1.导出
private void btn_print_Click(object sender, EventArgs e)
{
DataTable dtNew = new DataTable(); dtNew.Columns.Add(new DataColumn("commodity_name", typeof(object)));
dtNew.Columns.Add(new DataColumn("specifications", typeof(object)));
dtNew.Columns.Add(new DataColumn("unit", typeof(object)));
dtNew.Columns.Add(new DataColumn("number", typeof(object)));
dtNew.Columns.Add(new DataColumn("price", typeof(object)));
dtNew.Columns.Add(new DataColumn("money", typeof(object)));
dtNew.Columns.Add(new DataColumn("settlement", typeof(object))); for (int i = ; i < dgv.Rows.Count; i++)
{
DataRow dr = dtNew.NewRow();
dr["commodity_name"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["specifications"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["unit"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["number"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["price"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["money"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["settlement"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? ""); dtNew.Rows.Add(dr);
}
dgv2.DataSource = dtNew; string xlsxName = "其他产品采购单_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //保存的Excel文件名
TableToExcelForXLSX(dtNew, xlsxName); //传入带出数据及文件名
}
private void TableToExcelForXLSX(System.Data.DataTable dt, string output)
{
FileStream file = new FileStream(output, FileMode.OpenOrCreate, FileAccess.ReadWrite);
try
{
HSSFWorkbook workbook = new HSSFWorkbook(); //XSSFWorkbook :用于表示高及低层次excel文件格式的类，实现workbook接口。 ISheet sheet = workbook.CreateSheet("sheet"); //ISheet:是一个工作表的高级表示，Row:行；Cell:单元格
IRow row = null;
//第一行
row = sheet.CreateRow();//在工作表中添加一行
ICell cell = row.CreateCell();//在行中添加一列
cell.SetCellValue("商品采购入库单");//设置列的内容
setCellStyle(workbook, cell);
mergeCell(sheet, , , , ); //合并单元格 ICellStyle style = workbook.CreateCellStyle();
//第二行
row = sheet.CreateRow();
row.CreateCell();
sheet.GetRow().GetCell().SetCellValue("供货单位："); row.CreateCell();
sheet.GetRow().GetCell().SetCellValue(txt_unitname.Text); row.CreateCell();
sheet.GetRow().GetCell().SetCellValue((Convert.ToDateTime(dp_datetime.Text)).ToString("yyyy年MM月dd日"));
//第三行 row = sheet.CreateRow(); //循环添加表头
for (int i = ; i < dgv2.ColumnCount; i++)
{
row.CreateCell(i);
sheet.GetRow().GetCell(i).SetCellValue(dgv2.Columns[i].HeaderText);
//边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style.BottomBorderColor = HSSFColor.BLACK.index;
style.LeftBorderColor = HSSFColor.BLACK.index;
style.RightBorderColor = HSSFColor.BLACK.index;
style.TopBorderColor = HSSFColor.BLACK.index;
row.GetCell(i).CellStyle = style; //规定单元格加边框
} for (int i = ; i <= dgv2.Rows.Count; i++)
{
row = sheet.CreateRow(i + );
for (int j = ; j < dgv2.ColumnCount; j++)
{
row.CreateCell(j);
string str = dgv2.Rows[i - ].Cells[j].Value.ToString();
sheet.GetRow(i + ).GetCell(j).SetCellValue(str);
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style.BottomBorderColor = HSSFColor.BLACK.index;
style.LeftBorderColor = HSSFColor.BLACK.index;
style.RightBorderColor = HSSFColor.BLACK.index;
style.TopBorderColor = HSSFColor.BLACK.index;
row.GetCell(j).CellStyle = style;
}
}
row = sheet.CreateRow(dgv.Rows.Count + ); row.CreateCell();
sheet.GetRow(dgv.Rows.Count + ).GetCell().SetCellValue("财务审核："); row.CreateCell();
sheet.GetRow(dgv.Rows.Count + ).GetCell().SetCellValue(cb_auditing.Text); row.CreateCell();
sheet.GetRow(dgv.Rows.Count + ).GetCell().SetCellValue("实物负责人："); row.CreateCell();
sheet.GetRow(dgv.Rows.Count + ).GetCell().SetCellValue(dgv.Rows[].Cells[].Value.ToString()); row.CreateCell();
sheet.GetRow(dgv.Rows.Count + ).GetCell().SetCellValue("制单人："); row.CreateCell();
sheet.GetRow(dgv.Rows.Count + ).GetCell().SetCellValue(dgv.Rows[].Cells[].Value.ToString()); // 存储路径弹框选择
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = output;
saveDialog.ShowDialog();
output = saveDialog.FileName;
if (output.IndexOf(":") < ) return; //被点了取消
if (output != "")
{
try
{
file = File.OpenWrite(saveDialog.FileName);
workbook.Write(file);
//file.Close();
//MessageBox.Show("导出成功！");
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
} }
} finally
{
if (file != null)
file.Close();
GC.SuppressFinalize(this);
} }
2.合并单元格、设置字体
private void mergeCell(ISheet sheet, int firstRow, int lastRow, int firstCell, int lastCell)
{
sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCell, lastCell));//2.0使用 2.0以下为Region
} private void setCellStyle(HSSFWorkbook workbook, ICell cell)
{
HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
ffont.FontHeight = * ;
ffont.FontName = "宋体";
fCellStyle.SetFont(ffont); fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐
cell.CellStyle = fCellStyle;
}
附加（ 使用Microsoft.Office.Interop.Excel 导出Excel表格）
private void ExportExcels(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < ) return;被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
} Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[];//取得sheet1 DataTable dtNew = new DataTable(); dtNew.Columns.Add(new DataColumn("id", typeof(object)));
dtNew.Columns.Add(new DataColumn("datetime", typeof(object)));
dtNew.Columns.Add(new DataColumn("purchase_unit", typeof(object)));
dtNew.Columns.Add(new DataColumn("specifications", typeof(object)));
dtNew.Columns.Add(new DataColumn("unit", typeof(object)));
dtNew.Columns.Add(new DataColumn("number", typeof(object)));
dtNew.Columns.Add(new DataColumn("price", typeof(object)));
dtNew.Columns.Add(new DataColumn("money", typeof(object)));
dtNew.Columns.Add(new DataColumn("payment", typeof(object)));
dtNew.Columns.Add(new DataColumn("remain", typeof(object))); for (int i = ; i < dgv.Rows.Count; i++)
{
DataRow dr = dtNew.NewRow(); dr["id"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["datetime"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["purchase_unit"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["specifications"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["unit"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["number"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["price"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["money"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["payment"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? "");
dr["remain"] = Convert.ToString(dgv.Rows[i].Cells[].Value ?? ""); dtNew.Rows.Add(dr);
}
dgv2.DataSource = dtNew;
Excel.Range titleRange = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[, ]];//选取单元格
titleRange.Merge(true);//合并单元格
titleRange.Value2 = "其它商品销售明细汇总表";设置单元格内文本
titleRange.Font.Name = "宋体";//设置字体
titleRange.Font.Size = ;//字体大小
titleRange.Font.Bold = false;//加粗显示
titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中
titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//垂直居中
titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
titleRange.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细 Excel.Range title_unitname = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[, ]];//选取单元格
title_unitname.Merge(false);//合并单元格
title_unitname.Value2 = "商品名称：";设置单元格内文本
title_unitname.Font.Name = "宋体";//设置字体
title_unitname.Font.Size = ;//字体大小 Excel.Range title_unitname2 = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[, ]];//选取单元格
title_unitname2.Merge(false);//合并单元格
title_unitname2.Value2 = txt_commname.Text;设置单元格内文本
title_unitname2.Font.Name = "宋体";//设置字体
title_unitname2.Font.Size = ;//字体大小 Excel.Range title_datetime = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[, ]];//选取单元格
title_datetime.Merge(true);//合并单元格
title_datetime.Value2 = (Convert.ToDateTime(dp_datetime1.Text)).ToString("yyyy年MM月dd日");设置单元格内文本
title_datetime.Font.Name = "宋体";//设置字体
title_datetime.Font.Size = ;//字体大小 Excel.Range title_z = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[,]];//选取单元格
title_z.Merge(true);//合并单元格
title_z.Value2 = "至";设置单元格内文本
title_z.Font.Name = "宋体";//设置字体
title_z.Font.Size = ;//字体大小
title_z.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//水平居中 Excel.Range title_datetime2 = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[,]];//选取单元格
title_datetime2.Merge(true);//合并单元格
title_datetime2.Value2 = (Convert.ToDateTime(dp_datetime2.Text)).ToString("yyyy年MM月dd日");设置单元格内文本
title_datetime2.Font.Name = "宋体";//设置字体
title_datetime2.Font.Size = ;//字体大小 for (int i = ; i < dgv2.ColumnCount; i++)
{
worksheet.Cells[, i + ] = dgv.Columns[i].HeaderText;
Excel.Range title_dgv = worksheet.Range[worksheet.Cells[, i + ], worksheet.Cells[, i + ]];
title_dgv.Merge(false);//合并单元格
title_dgv.Value2 = dgv2.Columns[i].HeaderText;设置单元格内文本
title_dgv.Font.Name = "宋体";//设置字体
title_dgv.Font.Size = ;//字体大小
title_dgv.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
title_dgv.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细
}
写入数值
for (int r = ; r < dgv2.Rows.Count; r++)
{
for (int i = ; i < dgv2.ColumnCount; i++)
{
Excel.Range title_dgvT = worksheet.Range[worksheet.Cells[r + , i + ], worksheet.Cells[r + , i + ]];
worksheet.Cells[r + , i + ] = dgv.Rows[r].Cells[i].Value;
title_dgvT.Merge(false);//合并单元格
title_dgvT.Value2 = dgv2.Rows[r].Cells[i].Value;设置单元格内文本
title_dgvT.Font.Name = "宋体";//设置字体
title_dgvT.Font.Size = ;//字体大小
title_dgvT.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//设置边框
title_dgvT.Borders.Weight = Excel.XlBorderWeight.xlThin;//边框常规粗细
}
System.Windows.Forms.Application.DoEvents();
} Excel.Range title_auditing = worksheet.Range[worksheet.Cells[dgv.Rows.Count + , ], worksheet.Cells[dgv.Rows.Count + , ]];//选取单元格
title_auditing.Merge(false);//合并单元格
title_auditing.Value2 = "会计：";设置单元格内文本
title_auditing.Font.Name = "宋体";//设置字体
title_auditing.Font.Size = ;//字体大小 Excel.Range title_auditing2 = worksheet.Range[worksheet.Cells[dgv.Rows.Count + , ], worksheet.Cells[dgv.Rows.Count + , ]];//选取单元格
title_auditing2.Merge(false);//合并单元格
title_auditing2.Value2 = dgv.Rows[].Cells[].Value;设置单元格内文本
title_auditing2.Font.Name = "宋体";//设置字体
title_auditing2.Font.Size = ;//字体大小 worksheet.Cells[dgv.Rows.Count + , ] = "财务审核：" + cb_auditing.Text; Excel.Range title_docm = worksheet.Range[worksheet.Cells[dgv.Rows.Count + , ], worksheet.Cells[dgv.Rows.Count + , ]];//选取单元格
title_docm.Merge(false);//合并单元格
title_docm.Value2 = "制单人：";设置单元格内文本
title_docm.Font.Name = "宋体";//设置字体
title_docm.Font.Size = ;//字体大小 Excel.Range title_docm2 = worksheet.Range[worksheet.Cells[dgv.Rows.Count + , ], worksheet.Cells[dgv.Rows.Count + , ]];//选取单元格
title_docm2.Merge(false);//合并单元格
title_docm2.Value2 = dgv.Rows[].Cells[].Value;设置单元格内文本
title_docm2.Font.Name = "宋体";//设置字体
title_docm2.Font.Size = ;//字体大小
worksheet.Cells[dgv.Rows.Count + , ] = "制单人：" + dgv.Rows[].Cells[].Value; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}