添加引用
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Text;
导入
1 public DataSet ExcelToDataTable(string path) 2 { 3 DataSet dataSet1 = new DataSet(); 4 HSSFWorkbook hssfworkbook; 5 using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) 6 { 7 hssfworkbook = new HSSFWorkbook(file); //把xls文件中的数据写入hssfworkbook中 8 9 10 //for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数 11 //{ 12 ISheet sheet = hssfworkbook.GetSheetAt(0); //读取当前表数据 13 14 DataTable dt = new DataTable(); 15 16 for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数 17 { 18 IRow row = sheet.GetRow(j); //读取当前行数据 19 DataRow dr = dt.NewRow(); 20 if (row != null) 21 { 22 //sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限 23 for (int k = 0; k < row.LastCellNum; k++) //LastCellNum 是当前行的总列数 24 { 25 ICell cell = row.GetCell(k); //当前表格 26 if (cell != null) 27 { 28 if (j == 0) 29 { 30 dt.Columns.Add(cell.ToString()); 31 } 32 else 33 { 34 RegexHelper regexHelper = new RegexHelper(); 35 36 if (regexHelper.IsMatch(cell.ToString(), "[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}")) 37 { 38 dr[k] = cell.DateCellValue.ToString(); 39 } 40 else 41 { 42 dr[k] = cell.ToString(); 43 } 44 //日期格式判断输出 45 46 } 47 //sbr.Append(cell.ToString()); //获取表格中的数据并转换为字符串类型 48 49 } 50 }//for 51 }// if (row != null) 52 if (j != 0) 53 { 54 dt.Rows.Add(dr); 55 } 56 }//for j 57 58 dataSet1.Tables.Add(dt); 59 60 //} 61 62 }//using 63 return dataSet1; 64 }
导出
1 #region "数据导出" 2 /// <summary> 3 /// WinForm DataTable数据导出Excel到本地 4 /// </summary> 5 /// <param name="table"></param> 6 /// <param name="fileName"></param> 7 public static void SaveToFile(DataTable table, string fileName) 8 { 9 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) 10 { 11 MemoryStream ms = RenderToExcel(table); 12 13 byte[] data = ms.ToArray(); 14 15 fs.Write(data, 0, data.Length); 16 fs.Flush(); 17 18 data = null; 19 } 20 } 21 public static MemoryStream RenderToExcel(DataTable table) 22 { 23 MemoryStream ms = new MemoryStream(); 24 25 using (table) 26 { 27 using (IWorkbook workbook = new HSSFWorkbook()) 28 { 29 using (ISheet sheet = workbook.CreateSheet()) 30 { 31 IRow headerRow = sheet.CreateRow(0); 32 33 // 处理标题. 34 foreach (DataColumn column in table.Columns) 35 headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//如果标题没有设置,返回ColumnName的值 36 37 38 int rowIndex = 1; 39 40 foreach (DataRow row in table.Rows) 41 { 42 IRow dataRow = sheet.CreateRow(rowIndex); 43 44 foreach (DataColumn column in table.Columns) 45 { 46 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); 47 } 48 49 rowIndex++; 50 } 51 52 workbook.Write(ms); 53 ms.Flush(); 54 ms.Position = 0; 55 } 56 } 57 } 58 return ms; 59 } 60 61 /// <summary> 62 /// Web DataTable 数据导出到浏览器客户端 63 /// </summary> 64 /// <param name="table"></param> 65 /// <param name="context"></param> 66 /// <param name="fileName"></param> 67 static void RenderToBrowser(DataTable table, HttpContext context, string fileName) 68 { 69 MemoryStream ms = RenderToExcel(table); 70 if (context.Request.Browser.Browser == "IE") 71 fileName = HttpUtility.UrlEncode(fileName); 72 context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName); 73 context.Response.BinaryWrite(ms.ToArray()); 74 } 75 76 #endregion
调用 ExcelHelp类就是上面写的自定义类
1 ExcelHelp ExcelHelp = new ExcelHelp(); 2 //导入Excel到datagridview 3 dataGridView1.DataSource = ExcelHelp.ExcelToDataTable(txtfileurl.Text).Tables[0]; 4 //导出 5 saveFileDialog1.Filter = "Excel|*.Xls;"; 6 saveFileDialog1.FileName = "测试.Xls"; 7 if (saveFileDialog1.ShowDialog() == DialogResult.OK) 8 { 9 ExcelHelp.SaveToFile(rptDst.Tables["interaction"], saveFileDialog1.FileName); 10 }