1.导出
1 private void btn_print_Click(object sender, EventArgs e) 2 { 3 DataTable dtNew = new DataTable(); 4 5 dtNew.Columns.Add(new DataColumn("commodity_name", typeof(object))); 6 dtNew.Columns.Add(new DataColumn("specifications", typeof(object))); 7 dtNew.Columns.Add(new DataColumn("unit", typeof(object))); 8 dtNew.Columns.Add(new DataColumn("number", typeof(object))); 9 dtNew.Columns.Add(new DataColumn("price", typeof(object))); 10 dtNew.Columns.Add(new DataColumn("money", typeof(object))); 11 dtNew.Columns.Add(new DataColumn("settlement", typeof(object))); 12 13 for (int i = 0; i < dgv.Rows.Count; i++) 14 { 15 DataRow dr = dtNew.NewRow(); 16 dr["commodity_name"] = Convert.ToString(dgv.Rows[i].Cells[3].Value ?? ""); 17 dr["specifications"] = Convert.ToString(dgv.Rows[i].Cells[4].Value ?? ""); 18 dr["unit"] = Convert.ToString(dgv.Rows[i].Cells[5].Value ?? ""); 19 dr["number"] = Convert.ToString(dgv.Rows[i].Cells[8].Value ?? ""); 20 dr["price"] = Convert.ToString(dgv.Rows[i].Cells[6].Value ?? ""); 21 dr["money"] = Convert.ToString(dgv.Rows[i].Cells[7].Value ?? ""); 22 dr["settlement"] = Convert.ToString(dgv.Rows[i].Cells[9].Value ?? ""); 23 24 dtNew.Rows.Add(dr); 25 } 26 dgv2.DataSource = dtNew; 27 28 string xlsxName = "其他产品采购单_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //保存的Excel文件名 29 TableToExcelForXLSX(dtNew, xlsxName); //传入带出数据及文件名 30 }
1 private void TableToExcelForXLSX(System.Data.DataTable dt, string output) 2 { 3 FileStream file = new FileStream(output, FileMode.OpenOrCreate, FileAccess.ReadWrite); 4 try 5 { 6 HSSFWorkbook workbook = new HSSFWorkbook(); //XSSFWorkbook :用于表示高及低层次excel文件格式的类，实现workbook接口。 7 8 ISheet sheet = workbook.CreateSheet("sheet"); //ISheet:是一个工作表的高级表示，Row:行；Cell:单元格 9 IRow row = null;
//第一行 10 row = sheet.CreateRow(0);//在工作表中添加一行 11 ICell cell = row.CreateCell(0);//在行中添加一列 12 cell.SetCellValue("商品采购入库单");//设置列的内容 13 setCellStyle(workbook, cell); 14 mergeCell(sheet, 0, 0, 0, 6); //合并单元格 15 16 ICellStyle style = workbook.CreateCellStyle(); 17
//第二行 18 row = sheet.CreateRow(1); 19 row.CreateCell(0); 20 sheet.GetRow(1).GetCell(0).SetCellValue("供货单位："); 21 22 row.CreateCell(1); 23 sheet.GetRow(1).GetCell(1).SetCellValue(txt_unitname.Text); 24 25 row.CreateCell(5); 26 sheet.GetRow(1).GetCell(5).SetCellValue((Convert.ToDateTime(dp_datetime.Text)).ToString("yyyy年MM月dd日")); 27
//第三行 28 29 row = sheet.CreateRow(2); //循环添加表头 30 for (int i = 0; i < dgv2.ColumnCount; i++) 31 { 32 row.CreateCell(i); 33 sheet.GetRow(2).GetCell(i).SetCellValue(dgv2.Columns[i].HeaderText); 34 //边框 35 style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; 36 style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; 37 style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; 38 style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; 39 style.BottomBorderColor = HSSFColor.BLACK.index; 40 style.LeftBorderColor = HSSFColor.BLACK.index; 41 style.RightBorderColor = HSSFColor.BLACK.index; 42 style.TopBorderColor = HSSFColor.BLACK.index; 43 row.GetCell(i).CellStyle = style; //规定单元格加边框 44 } 45 46 47 for (int i = 1; i <= dgv2.Rows.Count; i++) 48 { 49 row = sheet.CreateRow(i + 2); 50 for (int j = 0; j < dgv2.ColumnCount; j++) 51 { 52 row.CreateCell(j); 53 string str = dgv2.Rows[i - 1].Cells[j].Value.ToString(); 54 sheet.GetRow(i + 2).GetCell(j).SetCellValue(str); 55 style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; 56 style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; 57 style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; 58 style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; 59 style.BottomBorderColor = HSSFColor.BLACK.index; 60 style.LeftBorderColor = HSSFColor.BLACK.index; 61 style.RightBorderColor = HSSFColor.BLACK.index; 62 style.TopBorderColor = HSSFColor.BLACK.index; 63 row.GetCell(j).CellStyle = style; 64 } 65 } 66 row = sheet.CreateRow(dgv.Rows.Count + 7); 67 68 row.CreateCell(0); 69 sheet.GetRow(dgv.Rows.Count + 7).GetCell(0).SetCellValue("财务审核："); 70 71 row.CreateCell(1); 72 sheet.GetRow(dgv.Rows.Count + 7).GetCell(1).SetCellValue(cb_auditing.Text); 73 74 row.CreateCell(3); 75 sheet.GetRow(dgv.Rows.Count + 7).GetCell(3).SetCellValue("实物负责人："); 76 77 row.CreateCell(4); 78 sheet.GetRow(dgv.Rows.Count + 7).GetCell(4).SetCellValue(dgv.Rows[0].Cells[12].Value.ToString()); 79 80 row.CreateCell(5); 81 sheet.GetRow(dgv.Rows.Count + 7).GetCell(5).SetCellValue("制单人："); 82 83 row.CreateCell(6); 84 sheet.GetRow(dgv.Rows.Count + 7).GetCell(6).SetCellValue(dgv.Rows[0].Cells[13].Value.ToString()); 85 86 // 存储路径弹框选择 87 SaveFileDialog saveDialog = new SaveFileDialog(); 88 saveDialog.DefaultExt = "xls"; 89 saveDialog.Filter = "Excel文件|*.xls"; 90 saveDialog.FileName = output; 91 saveDialog.ShowDialog(); 92 output = saveDialog.FileName; 93 if (output.IndexOf(":") < 0) return; //被点了取消 94 if (output != "") 95 { 96 try 97 { 98 file = File.OpenWrite(saveDialog.FileName); 99 workbook.Write(file); 100 //file.Close(); 101 //MessageBox.Show("导出成功！"); 102 } 103 catch (Exception ex) 104 { 105 MessageBox.Show("导出文件时出错,文件可能正被打开！ " + ex.Message); 106 } 107 108 } 109 } 110 111 finally 112 { 113 if (file != null) 114 file.Close(); 115 GC.SuppressFinalize(this); 116 } 117 118 }
2.合并单元格、设置字体
1 private void mergeCell(ISheet sheet, int firstRow, int lastRow, int firstCell, int lastCell)
2 {
3 sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCell, lastCell));//2.0使用 2.0以下为Region
4 }
5
6 private void setCellStyle(HSSFWorkbook workbook, ICell cell)
7 {
8 HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
9 HSSFFont ffont = (HSSFFont)workbook.CreateFont();
10 ffont.FontHeight = 20 * 20;
11 ffont.FontName = "宋体";
12 fCellStyle.SetFont(ffont);
13
14 fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
15 fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐
16 cell.CellStyle = fCellStyle;
17 }
附加（ 使用Microsoft.Office.Interop.Excel 导出Excel表格）
1 private void ExportExcels(string fileName, DataGridView myDGV) 2 { 3 string saveFileName = ""; 4 SaveFileDialog saveDialog = new SaveFileDialog(); 5 saveDialog.DefaultExt = "xls"; 6 saveDialog.Filter = "Excel文件|*.xls"; 7 saveDialog.FileName = fileName; 8 saveDialog.ShowDialog(); 9 saveFileName = saveDialog.FileName; 10 if (saveFileName.IndexOf(":") < 0) return;被点了取消 11 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); 12 if (xlApp == null) 13 { 14 MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel"); 15 return; 16 } 17 18 Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; 19 Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); 20 Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 21 22 DataTable dtNew = new DataTable(); 23 24 dtNew.Columns.Add(new DataColumn("id", typeof(object))); 25 dtNew.Columns.Add(new DataColumn("datetime", typeof(object))); 26 dtNew.Columns.Add(new DataColumn("purchase_unit", typeof(object))); 27 dtNew.Columns.Add(new DataColumn("specifications", typeof(object))); 28 dtNew.Columns.Add(new DataColumn("unit", typeof(object))); 29 dtNew.Columns.Add(new DataColumn("number", typeof(object))); 30 dtNew.Columns.Add(new DataColumn("price", typeof(object))); 31 dtNew.Columns.Add(new DataColumn("money", typeof(object))); 32 dtNew.Columns.Add(new DataColumn("payment", typeof(object))); 33 dtNew.Columns.Add(new DataColumn("remain", typeof(object))); 34 35 for (int i = 0; i < dgv.Rows.Count; i++) 36 { 37 DataRow dr = dtNew.NewRow(); 38 39 dr["id"] = Convert.ToString(dgv.Rows[i].Cells[0].Value ?? ""); 40 dr["datetime"] = Convert.ToString(dgv.Rows[i].Cells[1].Value ?? ""); 41 dr["purchase_unit"] = Convert.ToString(dgv.Rows[i].Cells[2].Value ?? ""); 42 dr["specifications"] = Convert.ToString(dgv.Rows[i].Cells[4].Value ?? ""); 43 dr["unit"] = Convert.ToString(dgv.Rows[i].Cells[5].Value ?? ""); 44 dr["number"] = Convert.ToString(dgv.Rows[i].Cells[6].Value ?? ""); 45 dr["price"] = Convert.ToString(dgv.Rows[i].Cells[10].Value ?? ""); 46 dr["money"] = Convert.ToString(dgv.Rows[i].Cells[8].Value ?? ""); 47 dr["payment"] = Convert.ToString(dgv.Rows[i].Cells[9].Value ?? ""); 48 dr["remain"] = Convert.ToString(dgv.Rows[i].Cells[12].Value ?? ""); 49 50 dtNew.Rows.Add(dr); 51 } 52 dgv2.DataSource = dtNew; 53 Excel.Range titleRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 10]];//选取单元格 54 titleRange.Merge(true);//合并单元格 55 titleRange.Value2 = "其它商品销售明细汇总表";设置单元格内文本 56 titleRange.Font.Name = "宋体";//设置字体 57 titleRange.Font.Size = 28;//字体大小 58 titleRange.Font.Bold = false;//加粗显示 59 titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中 60 titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中 61 titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框 62 titleRange.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细 63 64 65 Excel.Range title_unitname = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[2, 1]];//选取单元格 66 title_unitname.Merge(false);//合并单元格 67 title_unitname.Value2 = "商品名称：";设置单元格内文本 68 title_unitname.Font.Name = "宋体";//设置字体 69 title_unitname.Font.Size = 14;//字体大小 70 71 Excel.Range title_unitname2 = worksheet.Range[worksheet.Cells[2, 2], worksheet.Cells[2, 2]];//选取单元格 72 title_unitname2.Merge(false);//合并单元格 73 title_unitname2.Value2 = txt_commname.Text;设置单元格内文本 74 title_unitname2.Font.Name = "宋体";//设置字体 75 title_unitname2.Font.Size = 14;//字体大小 76 77 Excel.Range title_datetime = worksheet.Range[worksheet.Cells[2, 8], worksheet.Cells[2, 8]];//选取单元格 78 title_datetime.Merge(true);//合并单元格 79 title_datetime.Value2 = (Convert.ToDateTime(dp_datetime1.Text)).ToString("yyyy年MM月dd日");设置单元格内文本 80 title_datetime.Font.Name = "宋体";//设置字体 81 title_datetime.Font.Size = 14;//字体大小 82 83 Excel.Range title_z = worksheet.Range[worksheet.Cells[2, 9], worksheet.Cells[2,9]];//选取单元格 84 title_z.Merge(true);//合并单元格 85 title_z.Value2 = "至";设置单元格内文本 86 title_z.Font.Name = "宋体";//设置字体 87 title_z.Font.Size = 14;//字体大小 88 title_z.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中 89 90 Excel.Range title_datetime2 = worksheet.Range[worksheet.Cells[2, 10], worksheet.Cells[2,10]];//选取单元格 91 title_datetime2.Merge(true);//合并单元格 92 title_datetime2.Value2 = (Convert.ToDateTime(dp_datetime2.Text)).ToString("yyyy年MM月dd日");设置单元格内文本 93 title_datetime2.Font.Name = "宋体";//设置字体 94 title_datetime2.Font.Size = 14;//字体大小 95 96 for (int i = 0; i < dgv2.ColumnCount; i++) 97 { 98 worksheet.Cells[5, i + 2] = dgv.Columns[i].HeaderText; 99 Excel.Range title_dgv = worksheet.Range[worksheet.Cells[5, i + 1], worksheet.Cells[5, i + 1]]; 100 title_dgv.Merge(false);//合并单元格 101 title_dgv.Value2 = dgv2.Columns[i].HeaderText;设置单元格内文本 102 title_dgv.Font.Name = "宋体";//设置字体 103 title_dgv.Font.Size = 14;//字体大小 104 title_dgv.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框 105 title_dgv.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细 106 } 107 写入数值 108 for (int r = 0; r < dgv2.Rows.Count; r++) 109 { 110 for (int i = 0; i < dgv2.ColumnCount; i++) 111 { 112 Excel.Range title_dgvT = worksheet.Range[worksheet.Cells[r + 6, i + 1], worksheet.Cells[r + 6, i + 1]]; 113 worksheet.Cells[r + 6, i + 2] = dgv.Rows[r].Cells[i].Value; 114 title_dgvT.Merge(false);//合并单元格 115 title_dgvT.Value2 = dgv2.Rows[r].Cells[i].Value;设置单元格内文本 116 title_dgvT.Font.Name = "宋体";//设置字体 117 title_dgvT.Font.Size = 14;//字体大小 118 title_dgvT.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框 119 title_dgvT.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细 120 } 121 System.Windows.Forms.Application.DoEvents(); 122 } 123 124 125 Excel.Range title_auditing = worksheet.Range[worksheet.Cells[dgv.Rows.Count + 10, 1], worksheet.Cells[dgv.Rows.Count + 10, 1]];//选取单元格 126 title_auditing.Merge(false);//合并单元格 127 title_auditing.Value2 = "会计：";设置单元格内文本 128 title_auditing.Font.Name = "宋体";//设置字体 129 title_auditing.Font.Size = 14;//字体大小 130 131 Excel.Range title_auditing2 = worksheet.Range[worksheet.Cells[dgv.Rows.Count + 10, 2], worksheet.Cells[dgv.Rows.Count + 10, 2]];//选取单元格 132 title_auditing2.Merge(false);//合并单元格 133 title_auditing2.Value2 = dgv.Rows[0].Cells[10].Value;设置单元格内文本 134 title_auditing2.Font.Name = "宋体";//设置字体 135 title_auditing2.Font.Size = 14;//字体大小 136 137 worksheet.Cells[dgv.Rows.Count + 10, 1] = "财务审核：" + cb_auditing.Text; 138 139 140 141 Excel.Range title_docm = worksheet.Range[worksheet.Cells[dgv.Rows.Count + 10, 9], worksheet.Cells[dgv.Rows.Count + 10, 9]];//选取单元格 142 title_docm.Merge(false);//合并单元格 143 title_docm.Value2 = "制单人：";设置单元格内文本 144 title_docm.Font.Name = "宋体";//设置字体 145 title_docm.Font.Size = 14;//字体大小 146 147 148 Excel.Range title_docm2 = worksheet.Range[worksheet.Cells[dgv.Rows.Count + 10, 10], worksheet.Cells[dgv.Rows.Count + 10, 10]];//选取单元格 149 title_docm2.Merge(false);//合并单元格 150 title_docm2.Value2 = dgv.Rows[0].Cells[11].Value;设置单元格内文本 151 title_docm2.Font.Name = "宋体";//设置字体 152 title_docm2.Font.Size = 14;//字体大小 153 worksheet.Cells[dgv.Rows.Count + 10, 10] = "制单人：" + dgv.Rows[0].Cells[15].Value; 154 155 worksheet.Columns.EntireColumn.AutoFit();//列宽自适应 156 if (saveFileName != "") 157 { 158 try 159 { 160 workbook.Saved = true; 161 workbook.SaveCopyAs(saveFileName); 162 } 163 catch (Exception ex) 164 { 165 MessageBox.Show("导出文件时出错,文件可能正被打开！ " + ex.Message); 166 } 167 } 168 xlApp.Quit(); 169 GC.Collect();//强行销毁 170 MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 171 }