设置值在单元格中居中
//worksheet4.Rows[0].Cells[0].CellFormat.Alignment = Infragistics.Documents.Excel.HorizontalCellAlignment.Center;
为单元格提供不同的边框样式和颜色，以使其与其他单元格分开：
//worksheet4.Rows[1].Cells[0].CellFormat.BottomBorderColor = System.Drawing.Color.Red;
//worksheet4.Rows[1].Cells[0].CellFormat.BottomBorderStyle =
// Infragistics.Documents.Excel.CellBorderLineStyle.DashDot;
//worksheet4.Rows[1].Cells[0].CellFormat.LeftBorderColor = System.Drawing.Color.Yellow;
//worksheet4.Rows[1].Cells[0].CellFormat.LeftBorderStyle =
// Infragistics.Documents.Excel.CellBorderLineStyle.Thick;
//worksheet4.Rows[1].Cells[0].CellFormat.RightBorderColor = System.Drawing.Color.Orange;
//worksheet4.Rows[1].Cells[0].CellFormat.RightBorderStyle =
// Infragistics.Documents.Excel.CellBorderLineStyle.Thin;
//worksheet4.Rows[1].Cells[0].CellFormat.TopBorderColor = System.Drawing.Color.Blue;
//worksheet4.Rows[1].Cells[0].CellFormat.TopBorderStyle =
// Infragistics.Documents.Excel.CellBorderLineStyle.Double;
将背景样式应用于突出的单元格
//worksheet4.Rows[2].Cells[0].CellFormat.FillPattern=Infragistics.Documents.Excel.FillPatternStyle.DiagonalCrosshatch;
//worksheet4.Rows[2].Cells[0].CellFormat.FillPatternBackgroundColor = System.Drawing.Color.SkyBlue;
设置单元格宽度/高度
//worksheet4.Columns[2].Width = 6;
//worksheet4.Columns[2].Width = 6;
创建一些列标题,赋值
worksheet4.Rows[1].Cells[1].Value = "早晨";
worksheet4.Rows[1].Cells[2].Value = "下午";
worksheet4.Rows[1].Cells[3].Value = "晚上";
创建一个合并区域，该区域将成为列标题的标题
//Infragistics.Documents.Excel.WorksheetMergedCellsRegion mergedRegion1=worksheet4.MergedCellsRegions.Add(2, 0, 2, 29);
设置合并区域的值
//mergedRegion1.Value = "封装外形";
为合并区域提供纯色背景色
//mergedRegion1.CellFormat.FillPattern = Infragistics.Documents.Excel.FillPatternStyle.Solid;
//mergedRegion1.CellFormat.FillPatternForegroundColor = System.Drawing.Color.SkyBlue;
设置合并区域中中间单元格的单元格对齐方式。
由于单元格及其合并的区域共享单元格格式，因此
最终设置合并区域的格式
//worksheet4.Rows[2].Cells[0].CellFormat.Alignment = Infragistics.Documents.Excel.HorizontalCellAlignment.Center;
//更改单元格的字体，使其值出现不同：
//worksheet.Rows [3] .Cells [0] .Value = 57;
//worksheet.Rows [3] .Cells [0] .CellFormat.Font.Bold =
//Infragistics.Documents.Excel.ExcelDefaultableBoolean.True;
//worksheet.Rows [3] .Cells [0] .CellFormat.Font.UnderlineStyle =
//Infragistics.Documents.Excel.FontUnderlineStyle.Double;
if (tData != null && tData.Rows.Count > 0)
{
//封装外形
string ProcessCode = string.Empty;
//当前行
int RowNumber = 0;
//第一个封装外形
string firstProcessCode = string.Empty;
int CurrentFinishedQuantity = 0; int AllTestQuantity = 0;
int HGQuantity = 0; string GoodRate1 = string.Empty; int DQuantity = 0; int YZBLQuantity = 0; int JCBLQuantity = 0; int DXQuantity = 0; int OSQuantity = 0;
int WrongQuantity = 0; string WrongRate1 = string.Empty; int WCQuantity = 0; int HKQuantity = 0;
for (int i = 0; i < tData.Rows.Count; i++)
{
if (RowNumber == 0 && ProcessCode == "")
{
ProcessCode = tData.Rows[0]["ProcessCode"].ToString();
firstProcessCode = ProcessCode;
//合并表头
Infragistics.Documents.Excel.WorksheetMergedCellsRegion mergedRegion1 = worksheet4.MergedCellsRegions.Add(0, 0, 0, 29);
mergedRegion1.Value = ProcessCode;
mergedRegion1.CellFormat.FillPattern = Infragistics.Documents.Excel.FillPatternStyle.Solid;
mergedRegion1.CellFormat.FillPatternForegroundColor = System.Drawing.Color.White;
worksheet4.Rows[0].Cells[0].CellFormat.Alignment = Infragistics.Documents.Excel.HorizontalCellAlignment.Center;//Center
worksheet4.Rows[0].Cells[0].CellFormat.Font.Height = 11 * 20;
worksheet4.Rows[0].Cells[0].CellFormat.Font.Name = "宋体";
//设置表头
SetExcelHeader(worksheet4, 1);
}
string _ProcessCode = tData.Rows[i]["ProcessCode"].ToString();
if (_ProcessCode != ProcessCode)
{
RowNumber += 4;
}
if (_ProcessCode != ProcessCode)
{
ProcessCode = _ProcessCode;
//合并表头
Infragistics.Documents.Excel.WorksheetMergedCellsRegion mergedRegion1 = worksheet4.MergedCellsRegions.Add(i + RowNumber, 0, i + RowNumber, 29);
mergedRegion1.Value = ProcessCode;
mergedRegion1.CellFormat.FillPattern = Infragistics.Documents.Excel.FillPatternStyle.Solid;
mergedRegion1.CellFormat.FillPatternForegroundColor = System.Drawing.Color.White;
worksheet4.Rows[i + RowNumber].Cells[0].CellFormat.Alignment = Infragistics.Documents.Excel.HorizontalCellAlignment.Center;//Center
worksheet4.Rows[i + RowNumber].Cells[0].CellFormat.Font.Height = 11 * 20;
worksheet4.Rows[i + RowNumber].Cells[0].CellFormat.Font.Name = "宋体";
//设置表头
SetExcelHeader(worksheet4, i + RowNumber + 1);
}
for (int j = 0; j < 30; j++)
{
worksheet4.Rows[i + RowNumber + 2].Cells[j].Value = tData.Rows[i][j + 2];
worksheet4.Rows[i + RowNumber + 2].Cells[j].CellFormat.Font.Height = 11 * 20;
worksheet4.Rows[i + RowNumber + 2].Cells[j].CellFormat.Font.Name = "宋体";
}
if (ProcessCode == _ProcessCode)
{
CurrentFinishedQuantity += NullHelper.GetInt32(tData.Rows[i]["CurrentFinishedQuantity"]);
AllTestQuantity += NullHelper.GetInt32(tData.Rows[i]["AllTestQuantity"]);
HGQuantity += NullHelper.GetInt32(tData.Rows[i]["HGQuantity"]);
DQuantity += NullHelper.GetInt32(tData.Rows[i]["DQuantity"]);
YZBLQuantity += NullHelper.GetInt32(tData.Rows[i]["YZBLQuantity"]);
JCBLQuantity += NullHelper.GetInt32(tData.Rows[i]["JCBLQuantity"]);
DXQuantity += NullHelper.GetInt32(tData.Rows[i]["DXQuantity"]);
OSQuantity += NullHelper.GetInt32(tData.Rows[i]["OSQuantity"]);
WrongQuantity += NullHelper.GetInt32(tData.Rows[i]["WrongQuantity"]);
WCQuantity += NullHelper.GetInt32(tData.Rows[i]["WCQuantity"]);
HKQuantity += NullHelper.GetInt32(tData.Rows[i]["HKQuantity"]);
try
{
GoodRate1 = ((HGQuantity + HKQuantity) / (CurrentFinishedQuantity - WCQuantity)).ToString("P");
WrongRate1 = (WrongQuantity / CurrentFinishedQuantity).ToString("P");//遗失率=遗失数/接收数量
}
catch
{
GoodRate1 = "0.00%";
WrongRate1 = "0.00%";
}
if (i == (tData.Rows.Count - 1))
{
SetRowTotal(worksheet4, (i + RowNumber + 3), ProcessCode, CurrentFinishedQuantity, AllTestQuantity, HGQuantity, GoodRate1, DQuantity, YZBLQuantity, JCBLQuantity, DXQuantity, OSQuantity,
WrongQuantity, WrongRate1, WCQuantity, HKQuantity);
CurrentFinishedQuantity = 0; AllTestQuantity = 0;
HGQuantity = 0; GoodRate1 = string.Empty; DQuantity = 0; YZBLQuantity = 0; JCBLQuantity = 0; DXQuantity = 0; OSQuantity = 0;
WrongQuantity = 0; WrongRate1 = string.Empty; WCQuantity = 0; HKQuantity = 0;
}
else
{
if (ProcessCode != tData.Rows[i + 1]["ProcessCode"].ToString())
{
SetRowTotal(worksheet4, (i + RowNumber + 3), ProcessCode, CurrentFinishedQuantity, AllTestQuantity, HGQuantity, GoodRate1, DQuantity, YZBLQuantity, JCBLQuantity, DXQuantity, OSQuantity,
WrongQuantity, WrongRate1, WCQuantity, HKQuantity);
CurrentFinishedQuantity = 0; AllTestQuantity = 0;
HGQuantity = 0; GoodRate1 = string.Empty; DQuantity = 0; YZBLQuantity = 0; JCBLQuantity = 0; DXQuantity = 0; OSQuantity = 0;
WrongQuantity = 0; WrongRate1 = string.Empty; WCQuantity = 0; HKQuantity = 0;
}
}
}
existingworkbook4.Save("D:\\测试业务分档统计表.xls");
/// <summary>
/// 设置合计行
/// </summary>
/// <param name="worksheet4">当前文档</param>
/// <param name="i">当前行</param>
/// <param name="ProcessCode">产品名称</param>
/// <param name="CurrentFinishedQuantity">接收数量</param>
/// <param name="AllTestQuantity">已测合计</param>
/// <param name="HGQuantity">合格品</param>
/// <param name="GoodRate1">合格率</param>
/// <param name="DQuantity">3D</param>
/// <param name="YZBLQuantity">印章不良</param>
/// <param name="JCBLQuantity">接触不良</param>
/// <param name="DXQuantity">电性</param>
/// <param name="OSQuantity">RG(O/S)</param>
/// <param name="WrongQuantity">遗失数</param>
/// <param name="WrongRate1">遗失率</param>
/// <param name="WCQuantity">未测</param>
/// <param name="HKQuantity">还库</param>
private void SetRowTotal(Worksheet worksheet4, int i, string ProcessCode, int CurrentFinishedQuantity, int AllTestQuantity,
int HGQuantity, string GoodRate1, int DQuantity, int YZBLQuantity, int JCBLQuantity, int DXQuantity, int OSQuantity,
int WrongQuantity, string WrongRate1, int WCQuantity, int HKQuantity)
{
worksheet4.Rows[i].Cells[4].Value = ProcessCode + "合计";
worksheet4.Rows[i].Cells[12].Value = CurrentFinishedQuantity.ToString(); //"接收数量"
worksheet4.Rows[i].Cells[13].Value = AllTestQuantity.ToString(); //"已测合计"
worksheet4.Rows[i].Cells[14].Value = HGQuantity.ToString(); //"合格"
worksheet4.Rows[i].Cells[15].Value = GoodRate1.ToString(); //"合格率"
worksheet4.Rows[i].Cells[16].Value = DQuantity.ToString(); //"3D"
worksheet4.Rows[i].Cells[17].Value = YZBLQuantity.ToString(); //"印章不良"
worksheet4.Rows[i].Cells[18].Value = JCBLQuantity.ToString(); //"接触不良"
worksheet4.Rows[i].Cells[19].Value = DXQuantity.ToString(); //"电性"
worksheet4.Rows[i].Cells[20].Value = OSQuantity.ToString(); //"RG(O/S)"
worksheet4.Rows[i].Cells[21].Value = WrongQuantity.ToString(); //"遗失数"
worksheet4.Rows[i].Cells[22].Value = WrongRate1.ToString(); //"遗失率"
worksheet4.Rows[i].Cells[23].Value = WCQuantity.ToString(); //"未测"
worksheet4.Rows[i].Cells[24].Value = HKQuantity.ToString(); //"还库"
for (int j = 0; j < 30; j++)
{
if (j == 4)
{
worksheet4.Rows[i].Cells[j].CellFormat.Font.Height = 10 * 20;
}
else
{
worksheet4.Rows[i].Cells[j].CellFormat.Font.Height = 11 * 20;
}
worksheet4.Rows[i].Cells[j].CellFormat.Font.Name = "宋体";
}
}
private void SetExcelHeader(Worksheet worksheet4, int i)
{
//背景颜色
for (int j = 0; j < 30; j++)
{
worksheet4.Rows[i].Cells[j].CellFormat.FillPatternForegroundColor = System.Drawing.Color.Gray;
worksheet4.Rows[i].Cells[j].CellFormat.Font.ColorInfo = System.Drawing.Color.White;
worksheet4.Rows[i].Cells[j].CellFormat.Font.Height = 12 * 20;
worksheet4.Rows[i].Cells[j].CellFormat.Font.Name = "Verdana";
if (j == 10 || j == 11)
{
worksheet4.Columns[j].Width = 5000;
}
else if (j == 4)
{
worksheet4.Columns[j].Width = 3200;
}
else
{
worksheet4.Columns[j].Width = 2900;
}
}
worksheet4.Rows[i].Cells[0].Value = "生产订单号";
worksheet4.Rows[i].Cells[1].Value = "批号";
worksheet4.Rows[i].Cells[2].Value = "产品编码";
worksheet4.Rows[i].Cells[3].Value = "产品型号";
worksheet4.Rows[i].Cells[4].Value = "芯片规格";
worksheet4.Rows[i].Cells[5].Value = "封装外形";
worksheet4.Rows[i].Cells[6].Value = "印章批号";
worksheet4.Rows[i].Cells[7].Value = "机台号";
worksheet4.Rows[i].Cells[8].Value = "操作人";
worksheet4.Rows[i].Cells[9].Value = "工序";
worksheet4.Rows[i].Cells[10].Value = "作业开始时间";
worksheet4.Rows[i].Cells[11].Value = "作业完成时间";
worksheet4.Rows[i].Cells[12].Value = "接收数量";
worksheet4.Rows[i].Cells[13].Value = "已测合计";
worksheet4.Rows[i].Cells[14].Value = "合格";
worksheet4.Rows[i].Cells[15].Value = "合格率";
worksheet4.Rows[i].Cells[16].Value = "3D";
worksheet4.Rows[i].Cells[17].Value = "印章不良";
worksheet4.Rows[i].Cells[18].Value = "接触不良";
worksheet4.Rows[i].Cells[19].Value = "电性";
worksheet4.Rows[i].Cells[20].Value = "RG(O/S)";
worksheet4.Rows[i].Cells[21].Value = "遗失数";
worksheet4.Rows[i].Cells[22].Value = "遗失率";
worksheet4.Rows[i].Cells[23].Value = "未测";
worksheet4.Rows[i].Cells[24].Value = "还库";
worksheet4.Rows[i].Cells[25].Value = "备注";
worksheet4.Rows[i].Cells[26].Value = "测试指令单号";
worksheet4.Rows[i].Cells[27].Value = "客户名称";
worksheet4.Rows[i].Cells[28].Value = "评估单号";
worksheet4.Rows[i].Cells[29].Value = "指令需求数";
}
https://www.infragistics.com/help/winforms/search?query=Excel&page=3&api=False
https://www.infragistics.com/help/winforms/excelengine-merge-cells
https://www.infragistics.com/help/winforms/excelengine-applying-styles-to-cells