using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
/*
如果想要设置单元格为只读或可写，可以参考这里，实际上只需要如下两个步骤：
cell.CellStyle.IsLocked = false;//设置该单元格为非锁定
sheet.ProtectSheet("password");//保护表单，password为解锁密码
cell.CellStyle.IsLocked 默认就是true，因此第2步一定要执行，才能实现锁定单元格，
对于不想锁定的单元格，就一定要设置cell.CellStyle.IsLocked = false
*/ namespace NPOI操作
{
public class ProcessXls
{
/// <summary>
/// 输出
/// </summary>
/// <param name="filePath"></param>
public static void WriteFromExcelFile(string filePath)
{
HSSFWorkbook workbook = new HSSFWorkbook();//创建一个表
ISheet sheet1 = workbook.CreateSheet("Sheet1");//创建一个sheet
IRow row1 = sheet1.CreateRow();//创建一行
IRow row2 = sheet1.CreateRow();//再创建二行
//sheet1.CreateRow(0).CreateCell(0).SetCellValue("B");//直接在第一行第一列创建并设值
row1.CreateCell().SetCellValue("姓名");//创建一列并在第一列添加内容
row1.CreateCell().SetCellValue("参加工作时间");
row1.CreateCell().SetCellValue("当前日期");
row1.CreateCell().SetCellValue("工作年限");
ICell cel1 = row2.CreateCell();//在第二行创建一列
ICell cel2 = row2.CreateCell();
ICell cel3 = row2.CreateCell();
ICell cel4 = row2.CreateCell();
cel1.SetCellValue("Tom");//在第二行的第一列添加内容
cel2.SetCellValue(new DateTime(, , ));//添加日期
cel3.CellFormula = "TODAY()";//添加当前日期
cel4.CellFormula = "CONCATENATE(DATEDIF(B2,TODAY(),\"y\"),\"年\",DATEDIF(B2,TODAY(),\"ym\"),\"个月\")";
//在poi中日期是以double类型表示的，所以要格式化
//设置日期格式
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
cel2.CellStyle = cellStyle;
cel3.CellStyle = cellStyle;
using (FileStream file = new FileStream(@"c:\tes.xls", FileMode.Create))
{
workbook.Write(file);//写入输出流中
}
} /// <summary>
/// 读取XLS指定列数据
/// </summary>
/// <param name="filePath">XLS路径</param>
/// <param name="cellNum">列位置，从0开始</param>
/// <returns></returns>
public static List<string> ReadFromExcelFile(string filePath,int cellNum)
{
List<string> list = new List<string>();
IWorkbook workbook = null;
string extension = Path.GetExtension(filePath);
using (FileStream fs = File.OpenRead(filePath))
{
if (extension.Equals(".xls"))
{
workbook = new HSSFWorkbook(fs);////把xls文件中的数据写入workbook中
}
if (extension.Equals(".xlsx"))
{
workbook = new XSSFWorkbook(fs);//把xlsx文件中的数据写入workbook中
} ISheet sheet = workbook.GetSheetAt();//读取当前表数据
//LastRowNum 是当前表的总行数（注意）
for (int i = ; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);//读取当前行数据
if (row != null && row.GetCell(cellNum) != null)
{
string value = Convert.ToString(GetCellValue(row.GetCell(cellNum)));
list.Add(value);
}
}
}
return list;
} #region 读取所有行
/*public static List<string> ReadFromExcelFile(string filePath, int cellNum)
{
List<string> list = new List<string>();
IWorkbook workbook = null;
string extension = Path.GetExtension(filePath);
using (FileStream fs = File.OpenRead(filePath))
{
if (extension.Equals(".xls"))
{
workbook = new HSSFWorkbook(fs);////把xls文件中的数据写入workbook中
}
else
{
workbook = new XSSFWorkbook(fs);//把xlsx文件中的数据写入workbook中
} ISheet sheet = workbook.GetSheetAt(0);//读取当前表数据
var row = sheet.GetRow(0).GetCell(0);//读取当前行数据
//int count = row.LastRowNum;
//ICell row1 = row.LastRowNum;
//LastRowNum 是当前表的总行数-1（注意）
//for (int i = 0; i < row.LastRowNum; i++)
//{
// row1 = row.GetCell(i);
// if (row != null)
// {
// //LastCellNum 是当前行的总列数
// for (int j = 0; j < row.LastCellNum; j++)
// {
// //读取该行的第j列数据
// string value = row.GetCell(j) == null ? "" : Convert.ToString(GetCellValue(row.GetCell(j)));
// list.Add(value.ToString());
// }
// }
//}
}
return list;
}*/ #endregion /// <summary>
/// 获取cell的数据，并设置为对应的数据类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static object GetCellValue(ICell cell)
{
object value = null;
try
{
if (cell.CellType != CellType.Blank)
{
switch (cell.CellType)
{
case CellType.Numeric:
// Date Type的数据CellType是Numeric
if (DateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue;
}
else
{
// Numeric type
value = cell.NumericCellValue;
}
break;
case CellType.Boolean:
// Boolean type
value = cell.BooleanCellValue;
break;
default:
// String type
value = cell.StringCellValue;
break;
}
}
}
catch (Exception)
{
value = "";
} return value;
} /// <summary>
/// 根据数据类型设置不同类型的cell
/// </summary>
/// <param name="cell"></param>
/// <param name="obj"></param>
public static void SetCellValue(ICell cell, object obj)
{
if (obj.GetType() == typeof(int))
{
cell.SetCellValue((int)obj);
}
else if (obj.GetType() == typeof(double))
{
cell.SetCellValue((double)obj);
}
else if (obj.GetType() == typeof(IRichTextString))
{
cell.SetCellValue((IRichTextString)obj);
}
else if (obj.GetType() == typeof(string))
{
cell.SetCellValue(obj.ToString());
}
else if (obj.GetType() == typeof(DateTime))
{
cell.SetCellValue((DateTime)obj);
}
else if (obj.GetType() == typeof(bool))
{
cell.SetCellValue((bool)obj);
}
else
{
cell.SetCellValue(obj.ToString());
}
} /// <summary>
/// 写入
/// </summary>
/// <param name="filePath"></param>
public static void WriteToExcel(string filePath)
{
//创建工作薄
IWorkbook wb;
string extension = System.IO.Path.GetExtension(filePath);
//根据指定的文件格式创建对应的类
//URL：http://www.bianceng.cn/Programming/csharp/201410/45750.htm
if (extension.Equals(".xls"))
{
wb = new HSSFWorkbook();
}
else
{
wb = new XSSFWorkbook();
} ICellStyle style1 = wb.CreateCellStyle();//样式
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
//设置边框
style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style1.WrapText = true;//自动换行 ICellStyle style2 = wb.CreateCellStyle();//样式
IFont font1 = wb.CreateFont();//字体
font1.FontName = "楷体";
font1.Color = HSSFColor.Red.Index;//字体颜色
font1.Boldweight = (short)FontBoldWeight.Normal;//字体加粗样式
style2.SetFont(font1);//样式里的字体设置具体的字体样式
//设置背景色
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.FillPattern = FillPattern.SolidForeground;
style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式 //创建一个表单
ISheet sheet = wb.CreateSheet("Sheet0");
//设置列宽
int[] columnWidth = { , , , }; //测试数据
int rowCount = , columnCount = ;
object[,] data =
{
{"列0", "列1", "列2", "列3"},
{"", , 5.2, 6.01},
{"", DateTime.Today, true, "2014-07-02"}
}; for (int i = ; i < columnWidth.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(i, * columnWidth[i]);
} IRow row;
ICell cell;
for (int i = ; i < rowCount; i++)
{
row = sheet.CreateRow(i);//创建第i行
for (int j = ; j < columnCount; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = j % == ? style1 : style2;
//根据数据类型设置不同类型的cell
//SetCellValue(cell, data[i, j]);
}
} //合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
CellRangeAddress region = new CellRangeAddress(, , , );
sheet.AddMergedRegion(region); try
{
FileStream fs = File.OpenWrite(filePath);
wb.Write(fs); //向打开的这个xls文件中写入表并保存。
fs.Close();
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using _01ado复习;
using System.Data.SqlClient; namespace _02Excel操作
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
#region 读取Excel
using (FileStream fsRead = File.OpenRead("ReadExcel.xls"))
{
//读取磁盘上的excel文件到一个"工作薄",Workbook
using (Workbook wk = new HSSFWorkbook(fsRead))
{
//遍历该工作薄中的所有“工作表”
//wk.NumberOfSheets 获取工作表的总个数。
for (int i = ; i < wk.NumberOfSheets; i++)
{
//获取每个"工作表"
using (Sheet sheet = wk.GetSheetAt(i))
{
Console.WriteLine("================={0}========================", sheet.SheetName); //遍历每一行
//sheet.GetRow(
//sheet.LastRowNum;获取最后一行的索引
for (int j = ; j <= sheet.LastRowNum; j++)
{
//获取每一行
Row row = sheet.GetRow(j); //获取当前行中的所有的单元格
// row.GetCell(
//row.LastCellNum
//循环遍历当前行中的每个单元格。
for (int k = ; k < row.LastCellNum; k++)
{
Cell cell = row.GetCell(k);
Console.Write(cell.ToString());
}
Console.WriteLine(); } }
}
}
} #endregion
} private void button2_Click(object sender, EventArgs e)
{
#region 写入Excel //1.创建Workbook对象
using (Workbook wk = new HSSFWorkbook())
{
//创建工作表
using (Sheet sheet = wk.CreateSheet("My Sheet1"))
{
//向工作表中创建行
for (int i = ; i < ; i++)
{
Row row = sheet.CreateRow(i);
//向当前行中创建单元格
for (int j = ; j < ; j++)
{
Cell cell = row.CreateCell(j);
cell.SetCellValue(j);
}
}
using (FileStream fsWrite = File.OpenWrite("my.xls"))
{
wk.Write(fsWrite);
}
} }
MessageBox.Show("ok"); #endregion
} private void button3_Click(object sender, EventArgs e)
{
bool b = false;
//读取数据库中的数据
using (SqlDataReader reader = SqlHelper.ExecuteReader("select * from TblUsers"))
{
if (reader.HasRows)
{
b = true;
//创建工作薄
using (Workbook wk = new HSSFWorkbook())
{
//创建工作表
using (Sheet sheet = wk.CreateSheet("TblUsers"))
{
#region 创建列信息 Row rowHeader = sheet.CreateRow();
for (int c = ; c < reader.FieldCount; c++)
{
rowHeader.CreateCell(c).SetCellValue(reader.GetName(c));
} #endregion int rowIndex = ;
while (reader.Read())
{
//创建行，创建单元格
Row row = sheet.CreateRow(rowIndex); //循环读取表中的每一列值
for (int i = ; i < reader.FieldCount; i++)
{
#region 导出数据的时候都变成字符串类型了
////创建一个单元格
//Cell cell = row.CreateCell(i);
//cell.SetCellValue(reader[i].ToString()); #endregion #region 按照对应的类型导出数据 //1.循环所有的列
//string s = reader.GetDataTypeName(i);
//Type type = reader.GetFieldType(i);
//switch (type.Name)
//{ // default:
// break;
//}
Cell cell = row.CreateCell(i);
string s = reader.GetDataTypeName(i);
switch (s)
{
case "int":
cell.SetCellValue(reader.GetInt32(i));
break;
case "varchar":
case "char":
case "nvarchar":
case "nchar":
cell.SetCellValue(reader.GetString(i));
break;
} #endregion } rowIndex++;
}
//写入Excel
using (FileStream fsWrite = File.OpenWrite("tblusers.xls"))
{
wk.Write(fsWrite);
}
}
}
MessageBox.Show("导出完成！");
} if (!b)
{
MessageBox.Show("没数据！");
}
}
} private void button4_Click(object sender, EventArgs e)
{
#region Excel数据导入到数据库中 //1.读取Excel文件
using (FileStream fsRead = File.OpenRead("tblusers.xls"))
{
//创建工作薄
using (Workbook wk = new HSSFWorkbook(fsRead))
{
//1.获取第一个工作表
using (Sheet sheet = wk.GetSheetAt())
{
string sql = "insert into TblUsers values(@uid,@pwd,@realName)"; //循环读取工作表中的每个行
for (int i = ; i <= sheet.LastRowNum; i++)
{
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@uid",SqlDbType.VarChar),
new SqlParameter("@pwd",SqlDbType.VarChar),
new SqlParameter("@realName",SqlDbType.VarChar)
};
Row row = sheet.GetRow(i);
//获取每行中的后三个单元格的数据
for (int c = ; c < row.LastCellNum; c++)
{
pms[c - ].Value = row.GetCell(c).ToString();
//Console.WriteLine(row.GetCell(c).ToString());
} //执行Sql
SqlHelper.ExecuteNonQuery(sql, pms);
}
}
}
MessageBox.Show("导入完毕！");
}
//2.将读取到的数据导入到表中 #endregion
}
}
}