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(0);//创建一行 IRow row2 = sheet1.CreateRow(1);//再创建二行 //sheet1.CreateRow(0).CreateCell(0).SetCellValue("B");//直接在第一行第一列创建并设值 row1.CreateCell(0).SetCellValue("姓名");//创建一列并在第一列添加内容 row1.CreateCell(1).SetCellValue("参加工作时间"); row1.CreateCell(2).SetCellValue("当前日期"); row1.CreateCell(3).SetCellValue("工作年限"); ICell cel1 = row2.CreateCell(0);//在第二行创建一列 ICell cel2 = row2.CreateCell(1); ICell cel3 = row2.CreateCell(2); ICell cel4 = row2.CreateCell(3); cel1.SetCellValue("Tom");//在第二行的第一列添加内容 cel2.SetCellValue(new DateTime(2004, 7, 1));//添加日期 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: es.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(0);//读取当前表数据 //LastRowNum 是当前表的总行数(注意) for (int i = 0; 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 = { 10, 10, 10, 20 }; //测试数据 int rowCount = 3, columnCount = 4; object[,] data = { {"列0", "列1", "列2", "列3"}, {"", 400, 5.2, 6.01}, {"", DateTime.Today, true, "2014-07-02"} }; for (int i = 0; i < columnWidth.Length; i++) { //设置列宽度,256*字符数,因为单位是1/256个字符 sheet.SetColumnWidth(i, 256 * columnWidth[i]); } IRow row; ICell cell; for (int i = 0; i < rowCount; i++) { row = sheet.CreateRow(i);//创建第i行 for (int j = 0; j < columnCount; j++) { cell = row.CreateCell(j); cell.CellStyle = j % 2 == 0 ? style1 : style2; //根据数据类型设置不同类型的cell //SetCellValue(cell, data[i, j]); } } //合并单元格,如果要合并的单元格中都有数据,只会保留左上角的 //CellRangeAddress(0, 2, 0, 0),合并0-2行,0-0列的单元格 CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0); 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 = 0; i < wk.NumberOfSheets; i++) { //获取每个"工作表" using (Sheet sheet = wk.GetSheetAt(i)) { Console.WriteLine("================={0}========================", sheet.SheetName); //遍历每一行 //sheet.GetRow( //sheet.LastRowNum;获取最后一行的索引 for (int j = 0; j <= sheet.LastRowNum; j++) { //获取每一行 Row row = sheet.GetRow(j); //获取当前行中的所有的单元格 // row.GetCell( //row.LastCellNum //循环遍历当前行中的每个单元格。 for (int k = 0; 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 = 0; i < 5; i++) { Row row = sheet.CreateRow(i); //向当前行中创建单元格 for (int j = 0; j < 5; 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(0); for (int c = 0; c < reader.FieldCount; c++) { rowHeader.CreateCell(c).SetCellValue(reader.GetName(c)); } #endregion int rowIndex = 1; while (reader.Read()) { //创建行,创建单元格 Row row = sheet.CreateRow(rowIndex); //循环读取表中的每一列值 for (int i = 0; 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(0)) { string sql = "insert into TblUsers values(@uid,@pwd,@realName)"; //循环读取工作表中的每个行 for (int i = 1; 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 = 1; c < row.LastCellNum; c++) { pms[c - 1].Value = row.GetCell(c).ToString(); //Console.WriteLine(row.GetCell(c).ToString()); } //执行Sql SqlHelper.ExecuteNonQuery(sql, pms); } } } MessageBox.Show("导入完毕!"); } //2.将读取到的数据导入到表中 #endregion } } }