NPOI操作excel
1、基本导出方法
private void ExportToExcel() { SaveFileDialog sdfExport = new SaveFileDialog(); sdfExport.Filter = "Excel文件|*.xls"; if (sdfExport.ShowDialog() != DialogResult.OK) { return; } string filename = sdfExport.FileName; HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Student"); IRow rowHeader = sheet.CreateRow(0);//表头行 rowHeader.CreateCell(0, CellType.String).SetCellValue("姓名"); rowHeader.CreateCell(1, CellType.String).SetCellValue("年龄"); rowHeader.CreateCell(2, CellType.String).SetCellValue("生日"); //把查询结果导出到Excel for (int i = 0; i < dataGridViewX1.Rows.Count; i++) { Student student = new Student(); student.StuName = dataGridViewX1.Rows[i].Cells["StuName"].Value.ToString(); student.StuAge = dataGridViewX1.Rows[i].Cells["StuAge"].Value.ToString(); student.StuBirthday = dataGridViewX1.Rows[i].Cells["StuBirthday"].Value.ToString(); IRow row = sheet.CreateRow(i + 1); row.CreateCell(0, CellType.String).SetCellValue(student.StuName); row.CreateCell(1, CellType.String).SetCellValue(student.StuAge); ICellStyle styledate = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); //格式具体有哪些请看单元格右键中的格式，有说明 styledate.DataFormat = format.GetFormat("yyyy/m/d"); ICell cellInDate = row.CreateCell(2, CellType.Numeric); cellInDate.CellStyle = styledate; cellInDate.SetCellValue(student.StuBirthday); } using (Stream stream = File.OpenWrite(filename)) { workbook.Write(stream); } }
2、ExcelUtility
using System; using System.Data; using System.IO; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; using System.Windows.Forms; using System.Drawing; using CommonHelper; namespace NPOIOprateExcel { public class ExcelUtility { /// <summary> /// 将excel导入到datatable /// </summary> /// <param name="filePath">excel路径</param> /// <param name="isColumnName">第一行是否是列名</param> /// <returns>返回datatable</returns> public static DataTable ExcelToDataTable(string filePath, bool isColumnName) { DataTable dataTable = null; FileStream fs = null; DataColumn column = null; DataRow dataRow = null; IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; int startRow = 0; try { using (fs = File.OpenRead(filePath)) { // 2007版本 if (filePath.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs); // 2003版本 else if (filePath.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(fs); if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet dataTable = new DataTable(); if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (rowCount > 0) { IRow firstRow = sheet.GetRow(0);//第一行 int cellCount = firstRow.LastCellNum;//列数 //构建datatable的列 if (isColumnName) { startRow = 1;//如果第一行是列名，则从第二行开始读取 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { cell = firstRow.GetCell(i); if (cell != null) { if (cell.StringCellValue != null) { column = new DataColumn(cell.StringCellValue); dataTable.Columns.Add(column); } } } } else { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { column = new DataColumn("column" + (i + 1)); dataTable.Columns.Add(column); } } //填充行 for (int i = startRow; i <= rowCount; ++i) { row = sheet.GetRow(i); if (row == null) continue; dataRow = dataTable.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { cell = row.GetCell(j); if (cell == null) { dataRow[j] = ""; } else { //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) switch (cell.CellType) { case CellType.Blank: dataRow[j] = ""; break; case CellType.Numeric: short format = cell.CellStyle.DataFormat; //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 if (format == 14 || format == 31 || format == 57 || format == 58) dataRow[j] = cell.DateCellValue; else dataRow[j] = cell.NumericCellValue; break; case CellType.String: dataRow[j] = cell.StringCellValue; break; } } } dataTable.Rows.Add(dataRow); } } } } } return dataTable; } catch (Exception) { if (fs != null) { fs.Close(); } return null; } } public static string getdatatype(DataTable Table,string colname) { string datatype=null; for (int j = 0; j < Table.Columns.Count; j++) { if (Table.Columns[j].Caption == colname) { datatype = Table.Columns[j].DataType.ToString(); break; } } return datatype; } public int[] Datagridtosql(DevComponents.DotNetBar.Controls.DataGridViewX datagrid, System.Windows.Forms.BindingSource ds) { int[] rows; rows = new int[2]; rows[0] = 0; rows[1] = 0; int insert_rows = 0; int update_rows = 0; int error_rows = 0; string[] cdatatype; int rowCount = datagrid.Rows.Count;//行数 int columnCount = datagrid.Columns.Count;//列数 //设置列头 int vcount = 0; string sql1 = ""; string insert_sql1 = ""; string update_sql1 = ""; string select_sql1 = ""; DataSet dt = (DataSet)ds.DataSource; DataTable Table = dt.Tables[ds.DataMember]; cdatatype = new string[columnCount]; for (int c = 0; c < columnCount; c++) { if (datagrid.Columns[c].Visible) { sql1 += datagrid.Columns[c].DataPropertyName + ","; } cdatatype[c] = getdatatype(Table, datagrid.Columns[c].DataPropertyName); } sql1 = sql1.Substring(0, sql1.Length - 1); sql1 += ")"; update_sql1 = "update " + ds.DataMember + " set "; select_sql1 = "select count(*) from " + ds.DataMember + " where StuName="; for (int i = 0; i < rowCount; i++) { string select_sql = select_sql1 + "'" + datagrid.Rows[i].Cells[1].Value.ToString() + "'"; int num = (int)AccessHelper.ExecuteScalar(select_sql, CommandType.Text, null); if (num > 0) { update_sql1 = "update " + ds.DataMember + " set "; for (int j = 0; j < columnCount; j++) { if (datagrid.Columns[j].Visible) { update_sql1 += "" + datagrid.Columns[j].DataPropertyName + "="; string datatype = cdatatype[j]; if (datatype == "System.String") { update_sql1 += "'" + datagrid.Rows[i].Cells[j].Value.ToString() + " ',"; } if (datatype == "System.Boolean") { update_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ","; } if ((datatype == "System.Int32") || (datatype == "System.Int16") || (datatype == "System.Double")) { update_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ","; } if (datatype == "System.DateTime") { if (!string.IsNullOrEmpty(datagrid.Rows[i].Cells[j].Value.ToString())) { update_sql1 += "#" + datagrid.Rows[i].Cells[j].Value.ToString() + "#,"; } else { update_sql1 += "Null,"; } } } } update_sql1 = update_sql1.Substring(0, update_sql1.Length - 1); update_sql1 += " where StuName=" + "'" + datagrid.Rows[i].Cells[1].Value.ToString() + "'"; ; try { AccessHelper.ExecuteQuery(update_sql1, CommandType.Text, null); update_rows++; } catch (Exception e) { } } else { insert_sql1 = "insert into " + ds.DataMember + "(" + sql1; insert_sql1 += " values("; for (int j = 0; j < columnCount; j++) { if (datagrid.Columns[j].Visible) { // MessageBox.Show(getdatatype(Table, datagrid.Columns[j].DataPropertyName) + datagrid.Columns[j].DataPropertyName); string datatype = cdatatype[j]; if (datatype == "System.String") { insert_sql1 += "'" + datagrid.Rows[i].Cells[j].Value.ToString() + " ',"; } if (datatype == "System.Boolean") { insert_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ","; } if ((datatype == "System.Int32") || (datatype == "System.Int16") || (datatype == "System.Double")) { insert_sql1 += "" + datagrid.Rows[i].Cells[j].Value.ToString() + ","; } if (datatype == "System.DateTime") { if (!string.IsNullOrEmpty(datagrid.Rows[i].Cells[j].Value.ToString())) { insert_sql1 += "#" + datagrid.Rows[i].Cells[j].Value.ToString() + "#,"; } else { insert_sql1 += "Null,"; } } } } insert_sql1 = insert_sql1.Substring(0, insert_sql1.Length - 1); insert_sql1 += ")"; try { AccessHelper.ExecuteQuery(insert_sql1, CommandType.Text, null); insert_rows++; } catch (Exception e) { } } } return rows; ; } public static int ExcelToDatagrid(DevComponents.DotNetBar.Controls.DataGridViewX datagrid, string filePath, bool isColumnName=true) { FileStream fs = null; IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; int startRow = 0; char[] delimiterChars = { '.', '\\' }; string[] Mystr = filePath.Split(delimiterChars); string sheetName = Mystr[Mystr.Length - 2]; ;// 没有扩展名的文件名 “Default” int[] colarray; try { using (fs = File.OpenRead(filePath)) { // 2007版本 if (filePath.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs); // 2003版本 else if (filePath.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(fs); if (workbook != null) { sheet = workbook.GetSheet(sheetName);//读取第一个sheet，当然也可以循环读取每个sheet //dataTable = new DataTable(); if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (rowCount > 0) { IRow firstRow = sheet.GetRow(0);//第一行 int cellCount = firstRow.LastCellNum;//列数 //构建datatable的列 colarray = new int[cellCount]; if (isColumnName) { startRow = 1;//如果第一行是列名，则从第二行开始读取 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { cell = firstRow.GetCell(i); if (cell != null) { int columnCount = datagrid.Columns.Count;//列数 for (int j = 0; j < columnCount; ++j) { if (cell.StringCellValue == datagrid.Columns[j].HeaderText) { colarray[j] = i; break; } } } } } else { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { colarray[i] = i; } } //填充行 for (int i = startRow; i <= rowCount; ++i) { row = sheet.GetRow(i); if (row == null) continue; datagrid.Rows.Add(); for (int j = row.FirstCellNum; j < cellCount; ++j) { cell = row.GetCell(colarray[j]); if (cell == null) { datagrid.Rows[i-1].Cells[j].Value = ""; } else { //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) switch (cell.CellType) { case CellType.Blank: datagrid.Rows[i-1].Cells[j].Value = ""; break; case CellType.Numeric: short format = cell.CellStyle.DataFormat; //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 if (format == 14 || format == 31 || format == 57 || format == 58) datagrid.Rows[i-1].Cells[j].Value = cell.DateCellValue; else datagrid.Rows[i-1].Cells[j].Value = cell.NumericCellValue; break; case CellType.String: datagrid.Rows[i-1].Cells[j].Value = cell.StringCellValue; break; case CellType.Boolean: datagrid.Rows[i - 1].Cells[j].Value = cell.BooleanCellValue; break; } } } } } } } } return 1; } catch (Exception e) { if (fs != null) { fs.Close(); } return -1; } } public static bool DataTableToExcel(DataTable dt,string fileName) { bool result = false; IWorkbook workbook = null; FileStream fs = null; IRow row = null; ISheet sheet = null; ICell cell = null; char[] delimiterChars = { '.', '\\' }; string[] Mystr = fileName.Split(delimiterChars); string sheetName = Mystr[Mystr.Length - 2]; try { if (dt != null && dt.Rows.Count > 0) { workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(sheetName);//创建一个名称为Sheet0的表 int rowCount = dt.Rows.Count;//行数 int columnCount = dt.Columns.Count;//列数 //设置列头 row = sheet.CreateRow(0);//excel第一行设为列头 for (int c = 0; c < columnCount; c++) { cell = row.CreateCell(c); cell.SetCellValue(dt.Columns[c].ColumnName); } //设置每行每列的单元格, for (int i = 0; i <rowCount; i++) { row = sheet.CreateRow(i+1); for (int j = 0; j < columnCount; j++) { cell = row.CreateCell(j);//excel第二行开始写入数据 cell.SetCellValue(dt.Rows[i][j].ToString()); } } using (fs = File.OpenWrite(fileName)) { workbook.Write(fs);//向打开的这个xls文件中写入数据 result = true; } } return result; } catch (Exception ex) { if (fs != null) { fs.Close(); } return false; } } public static bool DataGridViewXToExcel(DevComponents.DotNetBar.Controls.DataGridViewX dt,string filename) { bool result = false; IWorkbook workbook = null; FileStream fs = null; IRow row = null; ISheet sheet = null; ICell cell = null; char[] delimiterChars = { '.', '\\' }; string[] Mystr = filename.Split(delimiterChars); string sheetName = Mystr[Mystr.Length - 2];;// 没有扩展名的文件名 “Default” try { if (dt != null && dt.Rows.Count > 0) { workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(sheetName);//创建一个名称为Sheet0的表 int rowCount = dt.Rows.Count;//行数 int columnCount = dt.Columns.Count;//列数 //设置列头 row = sheet.CreateRow(0);//excel第一行设为列头 for (int j = 0; j < columnCount; j++) { if (dt.Columns[j].Visible) { cell = row.CreateCell(j); cell.SetCellValue(dt.Columns[j].HeaderText); } } //设置每行每列的单元格, for (int i = 0; i < rowCount; i++) { row = sheet.CreateRow(i + 1); for (int j = 0; j < columnCount; j++) { if (dt.Columns[j].Visible) { cell = row.CreateCell(j);//excel第二行开始写入数据 cell.SetCellValue(dt.Rows[i].Cells[j].Value.ToString()); } } } using (fs = File.OpenWrite(filename)) { workbook.Write(fs);//向打开的这个xls文件中写入数据 result = true; } } return result; } catch (Exception ex) { if (fs != null) { fs.Close(); } return false; } } } }
