Excel导入与导出_Winform

导入：

        private void Btn_import_Click(object sender, RoutedEventArgs e){System.Windows.Forms.OpenFileDialog openImageDialog = new System.Windows.Forms.OpenFileDialog();openImageDialog.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx";openImageDialog.Multiselect = false;if (openImageDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK){_service.Import(openImageDialog.FileName); //这里已经获取到了本地文件的绝对路径}}

  internal void Import(string filePath){try{//TODO  执行把Excel中的数据转换成DBSet的方法DataSet dt = ExcelHelper.ImportFromExcel(filePath, 0);if (dt.Tables.Count <= 0) { MessageBox.Show("导入表格不能为空！"); return; }var ExportDatas = dt.Tables[0].AsEnumerable().ToList();//这里已经把Excel中的数据转换成了List<DataRow>的形式，下面就是写入数据库MessageBox.Show("导入成功！");}catch (Exception e){MessageBox.Show("发生了异常，建议导入之前请先关闭此文件，如果仍不能正常导入请联系管理员！");}}

        public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex){using (FileStream stream = System.IO.File.OpenRead(excelFilePath)){//根据filePath(绝对路径)获取到excel文件bool isCompatible = GetIsCompatible(excelFilePath);return ImportFromExcel(stream, headerRowIndex, isCompatible);}}public static bool GetIsCompatible(string filePath){return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);}public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible){DataSet ds = new DataSet();IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);for (int i = 0; i < workbook.NumberOfSheets; i++){//循环sheet页 转行成DataTable插入到DbSet中ISheet sheet = workbook.GetSheetAt(i);DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);if (table != null) ds.Tables.Add(table);else break;}excelFileStream.Close();workbook = null;return ds;}/// <summary>/// 创建工作薄(依据文件流)/// </summary>/// <param name="isCompatible"></param>/// <param name="stream"></param>/// <returns></returns>public static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream){if (isCompatible){return new HSSFWorkbook(stream);}else{return new XSSFWorkbook(stream);}}

 /// <summary>/// 从工作表中生成DataTable/// </summary>/// <param name="sheet"></param>/// <param name="headerRowIndex"></param>/// <returns></returns>private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex){DataTable table = new DataTable();//获取行数IRow headerRow = sheet.GetRow(headerRowIndex);if (headerRow == null) return null;//获取列数int cellCount = headerRow.LastCellNum;//这个循环是循环插入列for (int i = headerRow.FirstCellNum; i < cellCount; i++){if (string.IsNullOrEmpty(headerRow.GetCell(i).ToString())){// 如果遇到第一个空列，则不再继续向后读取cellCount = i + 1;break;}DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());if (table.Columns.Contains(column.ToString())){MessageBox.Show("导入数据列中存在相同的列名！");return null;}table.Columns.Add(column);}ICell cell = null;try{//这个循环是循环插入数据行for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++){IRow row = sheet.GetRow(i);if (row != null){cell = row.Cells[0];if (row.Cells[0].CellType==CellType.Blank){continue;}DataRow dataRow = table.NewRow();for (int j = row.FirstCellNum; j < cellCount; j++){if (row.GetCell(j) != null){dataRow[j] = row.GetCell(j).ToString();}}table.Rows.Add(dataRow);}}}catch (Exception){return null;}return table;}

导出：

  private void Btn_export_Click(object sender, RoutedEventArgs e){System.Windows.Forms.SaveFileDialog saveImageDialog = new System.Windows.Forms.SaveFileDialog();saveImageDialog.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx";if (saveImageDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK){_service.Export(saveImageDialog.FileName, GetFilter());}else{}}

  internal async void Export(string filePath, QC_TestProjectConfigFilter filter){HSSFWorkbook workbook = new HSSFWorkbook();ICellStyle StrStyle = ExcelHelper.Getcellstyle(workbook, true, false, 10, NPOI.SS.UserModel.HorizontalAlignment.Center, NPOI.SS.UserModel.VerticalAlignment.Center, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin, BorderStyle.Thin);ISheet sheet = workbook.CreateSheet("综合配置");IRow rowTitle = sheet.CreateRow(0);rowTitle.Height = 25 * 20;ICell cellTitle0 = rowTitle.CreateCell(0);ExcelHelper.writeToHeadCell(workbook, cellTitle0, "Id", sheet, 0);ICell cellTitle1 = rowTitle.CreateCell(1);ExcelHelper.writeToHeadCell(workbook, cellTitle1, "质检分类", sheet, 1);ICell cellTitle2 = rowTitle.CreateCell(2);ExcelHelper.writeToHeadCell(workbook, cellTitle2, "检验项目", sheet, 2);StringBuilder sql = new StringBuilder();sql.AppendLine("SELECT TPC.*,S.SamplePlan,TP.Name AS TestName FROM QC_TestProjectConfig TPC ");sql.AppendLine("LEFT JOIN QC_Sampling S ON TPC.SampleId=S.Id ");sql.AppendLine("LEFT JOIN QC_TestProject TP ON TPC.TestId=TP.Id ");sql.AppendLine("WHERE 1=1 ");sql.AppendLine("AND (TPC.StationName LIKE @StationName OR @StationName= NULL) ");sql.AppendLine("AND (TPC.InspectionType LIKE @InspectionType OR @InspectionType= NULL) ");sql.AppendLine("AND (S.Id like @SampleId OR @SampleId=null)  ");sql.AppendLine("AND (TP.Id LIKE @TestId OR @TestId=NULL) ");using (var db = new SqlConnection(SqlDB.ConnectionStringLocalTransaction)){var QC_TestProjectConfigs = await db.QueryAsync<QC_TestProjectConfig>(sql.ToString(), new { StationName = string.Format("%{0}%", filter.StationName), InspectionType = string.Format("%{0}%", filter.InspectionType), SampleId = string.Format("%{0}%", filter.SampleId), TestId = string.Format("%{0}%", filter.TestId) });int rowIndex = 1;foreach (QC_TestProjectConfig data in QC_TestProjectConfigs){int columnindex = 0;IRow rowData = sheet.CreateRow(rowIndex++);rowData.Height = 25 * 20;ExcelHelper.WriteCell(rowData, columnindex++, StrStyle, data.Id.ToString());ExcelHelper.WriteCell(rowData, columnindex++, StrStyle, data.StationName);ExcelHelper.WriteCell(rowData, columnindex++, StrStyle, data.TestName);           }}FileStream stream = new FileStream(filePath, FileMode.Create);workbook.Write(stream);stream.Close();stream.Dispose();MessageBox.Show("导出成功！");}

同样声明一些公共方法

        public enum stylexls{头,表头,加粗居中,副标题,url,时间,数字,钱,百分比,中文大写,科学计数法,默认}//写标题单元格public static void writeToHeadCell(HSSFWorkbook workbook, ICell cell, string value, ISheet sheet,int i){if (value == "Id") { sheet.SetColumnWidth(i, 25 * 400); }else { sheet.SetColumnWidth(i, 25 * 180); }cell.CellStyle = ExcelHelper.Getcellstyle(workbook, stylexls.加粗居中, true);cell.CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;cell.SetCellValue(value);}//写数据单元格public static void WriteCell(IRow row, int ColumnIndex, ICellStyle CellStyle, string Value){ICell icelltop = row.CreateCell(ColumnIndex);icelltop.CellStyle = CellStyle;icelltop.SetCellValue(Value);}#region 定义单元格常用到样式public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, bool IsBold, string fontName = "仿宋_GB2312"){ICellStyle cellStyle = wb.CreateCellStyle();//定义几种字体  //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的  IFont fontTitle = wb.CreateFont();fontTitle.FontHeightInPoints = 24;fontTitle.Boldweight = 1;fontTitle.FontName = fontName;IFont fontSmallTitle = wb.CreateFont();fontSmallTitle.Boldweight = (short)FontBoldWeight.Bold;fontSmallTitle.FontHeightInPoints = 10;fontSmallTitle.FontName = fontName;IFont font = wb.CreateFont();font.FontHeightInPoints = 10;font.FontName = fontName;//font.Underline = 1;下划线  IFont fontcolorblue = wb.CreateFont();fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;fontcolorblue.IsItalic = true;//下划线  fontcolorblue.FontName = fontName;//边框颜色  //cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Blue.Index;//cellStyle.TopBorderColor = HSSFColor.OliveGreen.Blue.Index;  //cellStyle.FillForegroundColor = HSSFColor.White.Index;//cellStyle.FillBackgroundColor = HSSFColor.Blue.Index;if (IsBold){//边框厚度cellStyle.BorderBottom = BorderStyle.Thin;cellStyle.BorderLeft = BorderStyle.Thin;cellStyle.BorderRight = BorderStyle.Thin;cellStyle.BorderTop = BorderStyle.Thin;}//水平对齐  cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//垂直对齐  cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//自动换行  cellStyle.WrapText = true;//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对  cellStyle.Indention = 0;//上面基本都是设共公的设置  //下面列出了常用的字段类型  switch (str){case stylexls.头:cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;cellStyle.SetFont(fontTitle);break;case stylexls.表头:cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;cellStyle.SetFont(font);break;case stylexls.加粗居中:cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;cellStyle.SetFont(fontSmallTitle);break;case stylexls.副标题:cellStyle.SetFont(fontSmallTitle);break;case stylexls.时间:IDataFormat datastyle = wb.CreateDataFormat();cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");cellStyle.SetFont(font);break;case stylexls.数字:cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");cellStyle.SetFont(font);break;case stylexls.钱:IDataFormat format = wb.CreateDataFormat();cellStyle.DataFormat = format.GetFormat("￥#,##0");cellStyle.SetFont(font);break;case stylexls.url:fontcolorblue.Underline = FontUnderlineType.Single;cellStyle.SetFont(fontcolorblue);break;case stylexls.百分比:cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");cellStyle.SetFont(font);break;case stylexls.中文大写:IDataFormat format1 = wb.CreateDataFormat();cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");cellStyle.SetFont(font);break;case stylexls.科学计数法:cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");cellStyle.SetFont(font);break;case stylexls.默认:cellStyle.SetFont(font);break;}return cellStyle;}public static ICellStyle Getcellstyle(IWorkbook wb, bool IsBold, bool IsStrong, int FontSize, NPOI.SS.UserModel.HorizontalAlignment HAlignment, NPOI.SS.UserModel.VerticalAlignment VAlignment, BorderStyle BottomStyle, BorderStyle LeftStyle, BorderStyle RightStyle, BorderStyle TopStyle, stylexls DataType, string fontName = "仿宋_GB2312"){ICellStyle cellStyle = wb.CreateCellStyle();IFont font = wb.CreateFont();font.FontHeightInPoints = (short)FontSize;font.FontName = fontName;if (IsStrong){font.Boldweight = (short)FontBoldWeight.Bold;}if (IsBold){//边框厚度cellStyle.BorderBottom = BottomStyle;cellStyle.BorderLeft = LeftStyle;cellStyle.BorderRight = RightStyle;cellStyle.BorderTop = TopStyle;}switch (DataType){case stylexls.时间:IDataFormat datastyle = wb.CreateDataFormat();cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");cellStyle.SetFont(font);break;case stylexls.数字:cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");cellStyle.SetFont(font);break;case stylexls.钱:IDataFormat format = wb.CreateDataFormat();cellStyle.DataFormat = format.GetFormat("￥#,##0");cellStyle.SetFont(font);break;case stylexls.百分比:cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");cellStyle.SetFont(font);break;case stylexls.中文大写:IDataFormat format1 = wb.CreateDataFormat();cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");cellStyle.SetFont(font);break;case stylexls.科学计数法:cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");cellStyle.SetFont(font);break;case stylexls.默认:cellStyle.SetFont(font);break;}//水平对齐  cellStyle.Alignment = HAlignment;//垂直对齐  cellStyle.VerticalAlignment = VAlignment;//自动换行  cellStyle.WrapText = true;//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对  cellStyle.Indention = 0;return cellStyle;}#endregionpublic static ICellStyle Getcellstyle(IWorkbook wb, bool IsBold, bool IsStrong, int FontSize, NPOI.SS.UserModel.HorizontalAlignment HAlignment, NPOI.SS.UserModel.VerticalAlignment VAlignment, BorderStyle BottomStyle, BorderStyle LeftStyle, BorderStyle RightStyle, BorderStyle TopStyle){return Getcellstyle(wb, IsBold, IsStrong, FontSize, HAlignment, VAlignment, BottomStyle, LeftStyle, RightStyle, TopStyle, stylexls.默认);}