自己一点点学习，一点点写下来的。可以使用，我这个使用NPOI写入。需要提前做好引入。
先放上效果图：
public void ExcelTest()
{
//导出：将数据库中的数据，存储到一个excel中
//1、查询数据库数据
//2、 生成excel
//2_1、生成workbook
//2_2、生成sheet
//2_3、遍历集合，生成行
//2_4、根据对象生成单元格
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表
var sheet = workbook.CreateSheet("信息表");
//设置单元格的宽度
/*
宽度：SetColumnWidth方法里的第二个参数要乘以256，因为这个参数的单位是1 / 256个字符宽度，所以要乘以256才是一整个字符宽度。
高度：
.Height 属性后面的值的单位是：1 / 20个点，所以要想得到一个点的话，需要乘以20。
HeightInPoints后面的单位是点，可以不用乘。*/
sheet.SetColumnWidth(0, 3*250);
sheet.SetColumnWidth(1, 11*250);
sheet.SetColumnWidth(2, 10*250);
sheet.SetColumnWidth(3, 10*250);
sheet.SetColumnWidth(4, 20*250);
sheet.SetColumnWidth(5, 10*250);//
sheet.SetColumnWidth(6, 10*250);
sheet.SetColumnWidth(7, 10*250);
sheet.SetColumnWidth(8, 10*250);
sheet.SetColumnWidth(9, 15*250);
sheet.SetColumnWidth(10, 15*250);
//sheet.SetColumnWidth(11, 8*250);
//sheet.SetColumnWidth(12, 7*250);
//sheet.SetColumnWidth(13, 7*250);
//sheet.SetColumnWidth(14, 11*250);/**/
#region 第一行样式
HSSFCellStyle cs_cell0 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建列头样式
cs_cell0.WrapText = true; //自动换行
cs_cell0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_cell0_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_cell0_Font.FontHeightInPoints = 11; //字体大小
cs_cell0.SetFont(cs_cell0_Font); //将字体绑定到样式
#endregion
//第一行 先row，后cell，再cell.SetCellValue，再设置样式，这是固定的
var row = sheet.CreateRow(0);//位置定位第一行
var cell0 = row.CreateCell(1);//位置定位第二列
cell0.SetCellValue("备注：花样机现在使用中的...");
cell0.CellStyle = cs_cell0;
#region 第二行样式
HSSFCellStyle cs_Title = (HSSFCellStyle)workbook.CreateCellStyle(); //创建列头样式
cs_Title.WrapText = true; //自动换行
cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_Title_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_Title_Font.FontName = "宋体";//设置字体
cs_Title_Font.IsBold = true; //字体加粗
cs_Title_Font.FontHeightInPoints = 20; //字体大小
cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
#endregion
//第二行 合并单元格+设置样式
CellRangeAddress region = new CellRangeAddress(1, 1, 1, 14);
sheet.AddMergedRegion(region);
row = sheet.CreateRow(1);cell0 = row.CreateCell(1);//第2列
row.HeightInPoints = 36;
cell0.SetCellValue("自动化每台机生产计划");
cell0.CellStyle = cs_Title;
#region 第四行加粗单元格样式
HSSFCellStyle cs_cell3 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建列头样式
cs_cell3.WrapText = true; //自动换行
HSSFFont cs_cell3_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_cell3.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
cs_cell3_Font.FontName = "宋体";//设置字体
cs_cell3_Font.IsBold = true; //字体加粗
cs_cell3_Font.FontHeightInPoints = 12; //字体大小
cs_cell3.SetFont(cs_cell3_Font); //将字体绑定到样式
#endregion
#region 第五行数据单元格样式
HSSFCellStyle cs_row5 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式
cs_row5.WrapText = true; //自动换行
cs_row5.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//设置单元格背景色
//cs_row5.FillBackgroundColor = 1;//这是背景色，区别前景色ForegroundColor
cs_row5.FillForegroundColor = 47;
cs_row5.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_row5.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_row5_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_row5_Font.FontName = "宋体";
cs_row5_Font.FontHeightInPoints = 9; //字体大小 12
cs_row5.SetFont(cs_row5_Font); //将字体绑定到样式
//加边框
cs_row5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框线
cs_row5.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框线
cs_row5.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右边框线
cs_row5.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上边框线
#endregion
#region 第六七八行数据单元格样式
HSSFCellStyle cs_row6 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式
cs_row6.WrapText = true; //自动换行
cs_row6.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //水平居中
cs_row6.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_row6_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_row6_Font.FontHeightInPoints = 11; //字体大小
cs_row6_Font.FontName = "宋体";
cs_row6.SetFont(cs_row6_Font); //将字体绑定到样式
//加边框
cs_row6.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框线
cs_row6.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框线
cs_row6.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右边框线
cs_row6.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上边框线
#endregion
//第四行 注意；同一行上的cell不能使用同一个，但row可以使用同一个
row = sheet.CreateRow(3);
cell0 = row.CreateCell(1);
cell0.SetCellValue("机器编号");
cell0.CellStyle = cs_cell3;
region = new CellRangeAddress(3, 3, 2, 4);
sheet.AddMergedRegion(region);
var cell1 = row.CreateCell(2);
row.HeightInPoints = 30;
cell1.SetCellValue("花样机1");
cell1.CellStyle = cs_cell3;
var cell2 = row.CreateCell(5);
cell2.SetCellValue("日班姓名");
cell2.CellStyle = cs_cell3;
#region 第四行不加粗样式
HSSFCellStyle cs_cell4 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建列头样式
cs_cell4.WrapText = false; //自动换行
cs_cell4.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //水平居中
cs_cell4.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_cell4_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_cell4_Font.FontName = "宋体";
cs_cell4_Font.FontHeightInPoints = 9; //字体大小
cs_cell4.SetFont(cs_cell4_Font); //将字体绑定到样式
#endregion
region = new CellRangeAddress(3, 3, 7, 9);
sheet.AddMergedRegion(region);
var cell3 = row.CreateCell(7);
row.HeightInPoints = 30;
cell3.SetCellValue("计划产出/单价：");
cell3.CellStyle = cs_cell4;
var cell4 = row.CreateCell(12);
cell4.SetCellValue("日期：");
cell4.CellStyle = cs_cell4;
//第五行
row = sheet.CreateRow(4);
row.HeightInPoints = 41;
//创建单元格
cell0 = row.CreateCell(1);cell0.SetCellValue("本厂编号");cell0.CellStyle = cs_row5;
cell1 = row.CreateCell(2);cell1.SetCellValue("组别"); cell1.CellStyle = cs_row5;
cell2 = row.CreateCell(3);cell2.SetCellValue("部件"); cell2.CellStyle = cs_row5;
cell3 = row.CreateCell(4);cell3.SetCellValue("预计完成数量"); cell3.CellStyle = cs_row5;
cell4 = row.CreateCell(5);cell4.SetCellValue("GTM票号"); cell4.CellStyle = cs_row5;
var cell5 = row.CreateCell(6);cell5.SetCellValue("GTM-A时间(Min)"); cell5.CellStyle = cs_row5;
var cell6 = row.CreateCell(7);cell6.SetCellValue("流水编号"); cell6.CellStyle = cs_row5;
var cell7 = row.CreateCell(8); cell7.SetCellValue("实际完成数量"); cell7.CellStyle = cs_row5;
var cell8 = row.CreateCell(9); cell8.SetCellValue("上一站"); cell8.CellStyle = cs_row5;
var cell9 = row.CreateCell(10); cell9.SetCellValue("下一站"); cell9.CellStyle = cs_row5;
//var cell10 = row.CreateCell(11); cell10.SetCellValue("完成件数"); cell10.CellStyle = cs_row5;
//var cell11 = row.CreateCell(12); cell11.SetCellValue("上一工作站"); cell11.CellStyle = cs_row5;
//var cell12 = row.CreateCell(13); cell12.SetCellValue("下一工作站"); cell12.CellStyle = cs_row5;
//var cell13 = row.CreateCell(14); cell13.SetCellValue("实际完成流水号(实际完成打勾✓)"); cell13.CellStyle = cs_row5;
string col0,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13;
//MessageBox.Show(dataGridView1.Rows.Count.ToString(), "提示");
//放gridview里的数据部分
for (int i = 0; i < dataGridView1.Rows.Count-1+5; i++)//完成后的最后一行为dataGridView1.Rows.Count-1+5+5
{
//Console.WriteLine("33");dataGridView1.Columns.Count
//获得gridview上的数据
if (i >= dataGridView1.Rows.Count - 1) {
col0 = "";
col1 = "";
col2 = "";
col3 = "";
col4 = "";
col5 = "";
col6 = "";
col7 = "";
col8 = "";
col9 = "";
// col10 = "";
// col11 = "";
// col12 = "";
// col13 = "";
}
else {
col0 = dataGridView1.Rows[i].Cells[0].Value.ToString();
col1 = dataGridView1.Rows[i].Cells[1].Value.ToString();
col2 = dataGridView1.Rows[i].Cells[2].Value.ToString();
col3 = dataGridView1.Rows[i].Cells[3].Value.ToString();
col4 = dataGridView1.Rows[i].Cells[4].Value.ToString();
col5 = dataGridView1.Rows[i].Cells[5].Value.ToString();
col6 = dataGridView1.Rows[i].Cells[6].Value.ToString();
col7 = dataGridView1.Rows[i].Cells[7].Value.ToString();
col8 = dataGridView1.Rows[i].Cells[8].Value.ToString();
col9 = dataGridView1.Rows[i].Cells[9].Value.ToString();
// col10 = dataGridView1.Rows[i].Cells[10].Value.ToString();
// col11 = dataGridView1.Rows[i].Cells[11].Value.ToString();
// col12 = dataGridView1.Rows[i].Cells[12].Value.ToString();
// col13 = dataGridView1.Rows[i].Cells[13].Value.ToString();
}
row = sheet.CreateRow(5+i);
row.HeightInPoints = 45;
//创建单元格
cell0 = row.CreateCell(1);cell0.SetCellValue(col0);cell0.CellStyle = cs_row6;
cell1 = row.CreateCell(2);cell1.SetCellValue(col1);cell1.CellStyle = cs_row6;
cell2 = row.CreateCell(3);cell2.SetCellValue(col2);cell2.CellStyle = cs_row6;
cell3 = row.CreateCell(4);cell3.SetCellValue(col3);cell3.CellStyle = cs_row6;
cell4 = row.CreateCell(5);cell4.SetCellValue(col4);cell4.CellStyle = cs_row6;
cell5 = row.CreateCell(6);cell5.SetCellValue(col5);cell5.CellStyle = cs_row6;
cell6 = row.CreateCell(7); cell6.SetCellValue(col6);cell6.CellStyle = cs_row6;
cell7 = row.CreateCell(8); cell7.SetCellValue(col7);cell7.CellStyle = cs_row6;
cell8 = row.CreateCell(9); cell8.SetCellValue(col8);cell8.CellStyle = cs_row6;
cell9 = row.CreateCell(10); cell9.SetCellValue(col9);cell9.CellStyle = cs_row6;
// cell10 = row.CreateCell(11); cell10.SetCellValue(col10);cell10.CellStyle = cs_row6;
// cell11 = row.CreateCell(12); cell11.SetCellValue(col11);cell11.CellStyle = cs_row6;
// cell12 = row.CreateCell(13); cell12.SetCellValue(col12);cell12.CellStyle = cs_row6;
// cell13 = row.CreateCell(14); cell13.SetCellValue(col13);cell13.CellStyle = cs_row6;
}
//表尾
#region 表尾第一行行数据单元格样式
HSSFCellStyle cs_row7 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式
cs_row7.WrapText = false; //自动换行
cs_row7.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //水平向左对齐
cs_row7.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_row7_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_row7_Font.FontHeightInPoints = 12; //字体大小
cs_row7_Font.FontName = "宋体";
cs_row7.SetFont(cs_row7_Font); //将字体绑定到样式
#endregion
int end_row = dataGridView1.Rows.Count - 1 + 5 + 5;
row = sheet.CreateRow(end_row);
row.HeightInPoints = 21;
cell0 = row.CreateCell(1);
cell0.SetCellValue("维修记录：");
cell0.CellStyle = cs_cell4;
#region 表尾第二行行数据单元格样式
HSSFCellStyle cs_row8 = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式
cs_row8.WrapText = true; //自动换行
cs_row8.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平向左对齐
cs_row8.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
HSSFFont cs_row8_Font = (HSSFFont)workbook.CreateFont(); //创建字体
cs_row8_Font.FontHeightInPoints = 12; //字体大小
cs_row8_Font.FontName = "宋体";
cs_row8.SetFont(cs_row8_Font); //将字体绑定到样式
//加边框
cs_row8.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框线
cs_row8.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框线
cs_row8.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //右边框线
cs_row8.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上边框线/**/
#endregion
row = sheet.CreateRow(end_row+1);
row.HeightInPoints = 26;
region = new CellRangeAddress(end_row+1, end_row+1, 1, 2);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell0 = row.CreateCell(1);
cell0.SetCellValue("停机时间");
cell0.CellStyle=cs_row8;
region = new CellRangeAddress(end_row + 1, end_row + 1, 3, 4);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell1 = row.CreateCell(3);
cell1.SetCellValue("停机问题点");
cell1.CellStyle = cs_row8;
region = new CellRangeAddress(end_row + 1, end_row + 1, 5, 7);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell2 = row.CreateCell(5);
cell2.SetCellValue("预计修复时间");
cell2.CellStyle = cs_row8;
region = new CellRangeAddress(end_row + 1, end_row + 1, 8, 10);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell3 = row.CreateCell(8);
cell3.SetCellValue("实际修复时间");
cell3.CellStyle = cs_row8;
region = new CellRangeAddress(end_row + 1, end_row + 1, 11, 14);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell4 = row.CreateCell(11);
cell4.SetCellValue("机修确认签名");
cell4.CellStyle = cs_row8;/**/
for (int i = end_row + 2; i < end_row + 2+3; i++)//完成后的最后一行为dataGridView1.Rows.Count-1+5+5
{
col0 = "";
col1 = "";
col2 = "";
col3 = "";
col4 = "";
row = sheet.CreateRow(i);
row.HeightInPoints = 34;
//创建单元格
region = new CellRangeAddress(i, i, 1, 2);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell0 = row.CreateCell(1); cell0.SetCellValue(col0); cell0.CellStyle = cs_row8;
region = new CellRangeAddress(i, i, 3, 4);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell1 = row.CreateCell(2); cell1.SetCellValue(col1); cell1.CellStyle = cs_row8;
region = new CellRangeAddress(i, i, 5, 7);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell2 = row.CreateCell(3); cell2.SetCellValue(col2); cell2.CellStyle = cs_row8;
region = new CellRangeAddress(i, i, 8, 10);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell3 = row.CreateCell(4); cell3.SetCellValue(col3); cell3.CellStyle = cs_row8;
region = new CellRangeAddress(i, i, 11, 14);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
sheet.AddMergedRegion(region);
cell4 = row.CreateCell(5); cell4.SetCellValue(col4); cell4.CellStyle = cs_row8;
}
end_row += 5;
row = sheet.CreateRow(end_row);
row.HeightInPoints = 21;
cell0 = row.CreateCell(1);
cell0.SetCellValue("工时(小时)");
cell0.CellStyle = cs_cell4;
row = sheet.CreateRow(end_row+1);
cell0 = row.CreateCell(1);
cell0.SetCellValue("作业时间(小时)");
cell0.CellStyle = cs_cell4;
cell1 = row.CreateCell(5);
cell1.SetCellValue("测试时间(小时)");
cell1.CellStyle = cs_cell4;
cell2 = row.CreateCell(11);
cell2.SetCellValue("闲置时间");
cell2.CellStyle = cs_cell4;
row = sheet.CreateRow(end_row+3);
row.HeightInPoints = 21;
cell0 = row.CreateCell(1);
cell0.SetCellValue("备注：");
cell0.CellStyle = cs_cell4;
region = new CellRangeAddress(end_row + 3, end_row + 3, 2, 13);
sheet.AddMergedRegion(region);
cell1 = row.CreateCell(2);
cell1.SetCellValue("1.机器状态处简单记为1/2/3/4,即1-代表“生产”，2-代表“测试”，3-代表“维修”，4-代表“闲置”");
cell1.CellStyle = cs_cell4;
row = sheet.CreateRow(end_row+4);
row.HeightInPoints = 21;
region = new CellRangeAddress(end_row + 4, end_row + 4, 2, 13);
sheet.AddMergedRegion(region);
cell0 = row.CreateCell(2);
cell0.SetCellValue("2.机器状态均为一律一半小时（30分钟）为基本单位统计，不足15分钟的忽略或记入上一操作，超过15分钟的以半");
cell0.CellStyle = cs_cell4;
row = sheet.CreateRow(end_row + 5);
row.HeightInPoints = 21;
region = new CellRangeAddress(end_row + 5, end_row + 5, 2, 13);
sheet.AddMergedRegion(region);
cell0 = row.CreateCell(2);
cell0.SetCellValue("3.机器状态如是“维修”，则在异常备注处记录出现什么问题，维修员处写维修员名字");
cell0.CellStyle = cs_cell4;
row = sheet.CreateRow(end_row + 6);
row.HeightInPoints = 21;
region = new CellRangeAddress(end_row + 6, end_row + 6, 2, 13);
sheet.AddMergedRegion(region);
cell0 = row.CreateCell(2);
cell0.SetCellValue("4.工时空格填写当天的上班工时(以小时为单位)，注意不是机器生产时间");
cell0.CellStyle = cs_cell4;
row = sheet.CreateRow(end_row + 7);
row.HeightInPoints = 21;
region = new CellRangeAddress(end_row + 7, end_row + 7, 2, 13);
sheet.AddMergedRegion(region);
cell0 = row.CreateCell(2);
cell0.SetCellValue("5.下面“作业/测试/维修/闲置时间”中填写每天设备测试、维修、闲置的总时间，以小时为单位");
cell0.CellStyle = cs_cell4;
///
string filePath = "";
SaveFileDialog s = new SaveFileDialog();
s.Title = "保存Excel文件";
s.Filter = "Excel文件(*.xls)|*.xls";
s.FilterIndex = 1;
if (s.ShowDialog() == DialogResult.OK)
filePath = s.FileName;
else
return;
///"C:\Users\10506\Desktop\信息表.xls"
FileStream file = new FileStream(@filePath, FileMode.CreateNew, FileAccess.Write);
workbook.Write(file);//book写到file
file.Dispose();
MessageBox.Show("创建完成", "提示");
}
扫一扫
532
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
