using System.Collections;
|
using System.Data;
|
using System.Text;
|
using System.Timers;
|
using Masuit.Tools;
|
using NPOI.HSSF.UserModel;
|
using NPOI.HSSF.Util;
|
using NPOI.SS.Formula.Functions;
|
using NPOI.SS.UserModel;
|
using NPOI.SS.Util;
|
using NPOI.XSSF.UserModel;
|
|
|
namespace Gs.Toolbox;
|
|
/// <summary>
|
/// xls操作类
|
/// </summary>
|
public class ExcelHelper
|
{
|
/// <summary>
|
/// DataTable导出到Excel的MemoryStream
|
/// </summary>
|
/// <param name="dtSource">源DataTable</param>
|
/// <param name="strHeaderText">表头文本</param>
|
/// <returns></returns>
|
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
|
{
|
IWorkbook workbook = new HSSFWorkbook();
|
var sheet = workbook.CreateSheet();
|
var dateStyle = workbook.CreateCellStyle();
|
var format = workbook.CreateDataFormat();
|
//dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
|
//取得列宽
|
var arrColWidth = new int[dtSource.Columns.Count];
|
foreach (DataColumn item in dtSource.Columns)
|
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936)
|
.GetBytes(item.ColumnName).Length;
|
for (var i = 0; i < dtSource.Rows.Count; i++)
|
for (var j = 0; j < dtSource.Columns.Count; j++)
|
{
|
var intTemp = Encoding.GetEncoding(936)
|
.GetBytes(dtSource.Rows[i][j].ToString()).Length;
|
if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
|
}
|
|
var rowIndex = 0;
|
foreach (DataRow row in dtSource.Rows)
|
{
|
#region 新建表，填充表头，填充列头，样式
|
|
if (rowIndex == 65535 || rowIndex == 0)
|
{
|
if (rowIndex != 0) sheet = workbook.CreateSheet();
|
|
#region 列头及样式
|
|
{
|
var headerRow = sheet.CreateRow(0);
|
var headStyle = workbook.CreateCellStyle();
|
headStyle.Alignment = HorizontalAlignment.Center;
|
var font = workbook.CreateFont();
|
font.FontHeightInPoints = 10;
|
font.Boldweight = 700;
|
headStyle.SetFont(font);
|
foreach (DataColumn column in dtSource.Columns)
|
{
|
headerRow.CreateCell(column.Ordinal)
|
.SetCellValue(column.ColumnName);
|
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
|
|
//设置列宽
|
sheet.SetColumnWidth(column.Ordinal,
|
(arrColWidth[column.Ordinal] + 1) * 256);
|
}
|
}
|
|
#endregion
|
|
rowIndex = 1;
|
}
|
|
#endregion
|
|
#region 填充内容
|
|
foreach (DataColumn column in dtSource.Columns)
|
{
|
var dataRow = sheet.CreateRow(rowIndex);
|
var newCell = dataRow.CreateCell(column.Ordinal);
|
var drValue = row[column].ToString();
|
switch (column.DataType.ToString())
|
{
|
case "System.String": //字符串类型
|
newCell.SetCellValue(drValue);
|
break;
|
case "System.DateTime": //日期类型
|
DateTime dateV;
|
DateTime.TryParse(drValue, out dateV);
|
newCell.SetCellValue(dateV);
|
newCell.CellStyle = dateStyle; //格式化显示
|
break;
|
case "System.Boolean": //布尔型
|
var boolV = false;
|
bool.TryParse(drValue, out boolV);
|
newCell.SetCellValue(boolV);
|
break;
|
case "System.Int16": //整型
|
case "System.Int32":
|
case "System.Int64":
|
case "System.Byte":
|
var intV = 0;
|
int.TryParse(drValue, out intV);
|
newCell.SetCellValue(intV);
|
break;
|
case "System.Decimal": //浮点型
|
|
case "System.Double":
|
double doubV = 0;
|
double.TryParse(drValue, out doubV);
|
newCell.SetCellValue(doubV);
|
break;
|
case "System.DBNull": //空值处理
|
newCell.SetCellValue("");
|
break;
|
default:
|
newCell.SetCellValue("");
|
break;
|
}
|
}
|
|
#endregion
|
|
rowIndex++;
|
}
|
|
using (var ms = new MemoryStream())
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
return ms;
|
}
|
}
|
|
/// <summary>
|
/// DataTable导出到Excel文件
|
/// </summary>
|
/// <param name="dtSource">源DataTable</param>
|
/// <param name="strHeaderText">表头文本</param>
|
/// <param name="strFileName">保存位置</param>
|
public static void Export(DataTable dtSource, string strHeaderText,
|
string strFileName)
|
{
|
using (var ms = Export(dtSource, strHeaderText))
|
{
|
using (var fs = new FileStream(strFileName, FileMode.Create,
|
FileAccess.Write))
|
{
|
var data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
/// <summary>
|
/// </summary>
|
/// <param name="dtSource"></param>
|
/// <param name="aryHeader"></param>
|
/// <param name="strFileName"></param>
|
public static void ExportAryHead(DataTable dtSource
|
, ArrayList aryHeader
|
, string strFileName
|
, ArrayList aryFotter = null
|
, ArrayList aryFotter2 = null
|
, int fixW = 0)
|
{
|
var roct = aryHeader.Count;
|
IWorkbook workbook = new HSSFWorkbook();
|
var sheet = workbook.CreateSheet();
|
//取得列宽
|
var arrColWidth = new int[dtSource.Columns.Count];
|
foreach (DataColumn item in dtSource.Columns)
|
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936)
|
.GetBytes(item.ColumnName).Length;
|
if (fixW <= 0)
|
for (var i = 0; i < dtSource.Rows.Count; i++)
|
for (var j = 0; j < dtSource.Columns.Count; j++)
|
{
|
var intTemp = Encoding.GetEncoding(936)
|
.GetBytes(dtSource.Rows[i][j].ToString()).Length;
|
if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
|
}
|
|
var cusRow = sheet.CreateRow(0);
|
var _rr = 0;
|
foreach (string _hh in aryHeader)
|
{
|
cusRow = sheet.CreateRow(_rr);
|
cusRow.CreateCell(0).SetCellValue(_hh);
|
//cellRangAddress参数说明：起始行，结束行，起始列，结束列
|
sheet.AddMergedRegion(new CellRangeAddress(_rr, _rr, 0,
|
dtSource.Columns.Count - 1));
|
// sheet.AddMergedRegion(new Region(0, 0, _rr, dtSource.Columns.Count - 1));
|
_rr++;
|
}
|
|
//填充表头
|
var headerRow = sheet.CreateRow(roct);
|
var headStyle = workbook.CreateCellStyle();
|
headStyle.Alignment = HorizontalAlignment.Center;
|
headStyle.FillPattern = FillPattern.SolidForeground;
|
headStyle.FillForegroundColor = HSSFColor.LightBlue.Index;
|
var font = workbook.CreateFont();
|
font.Color = HSSFColor.White.Index;
|
font.FontHeightInPoints = 12;
|
font.Boldweight = 700;
|
headStyle.SetFont(font);
|
foreach (DataColumn column in dtSource.Columns)
|
{
|
headerRow.CreateCell(column.Ordinal)
|
.SetCellValue(column.ColumnName);
|
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
|
sheet.SetColumnWidth(column.Ordinal,
|
(arrColWidth[column.Ordinal] + 6) * 256);
|
}
|
|
//sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //首行筛选
|
// if (fixW <= 0)
|
//sheet.CreateFreezePane(dtSource.Columns.Count,
|
// 1 + aryHeader.Count); //首行冻结
|
//填充内容
|
var dataRow = sheet.CreateRow(roct + 1);
|
for (var i = 0; i < dtSource.Rows.Count; i++)
|
{
|
dataRow = sheet.CreateRow(i + roct + 1);
|
for (var j = 0; j < dtSource.Columns.Count; j++)
|
dataRow.CreateCell(j)
|
.SetCellValue(dtSource.Rows[i][j].ToString());
|
}
|
|
//填充footer
|
if (aryFotter != null && aryFotter.Count > 0)
|
{
|
var _rot = roct + dtSource.Rows.Count + 2;
|
var ftRow = sheet.CreateRow(_rot);
|
var _ftidx = 0;
|
var _ftw = dtSource.Columns.Count / 2;
|
foreach (string _hh in aryFotter)
|
{
|
var ftStyle = workbook.CreateCellStyle();
|
ftStyle.FillPattern = FillPattern.SolidForeground;
|
ftStyle.FillForegroundColor = HSSFColor.White.Index;
|
if (_ftidx == 0)
|
ftStyle.BorderTop = BorderStyle.Thin;
|
else
|
ftStyle.BorderTop = BorderStyle.None;
|
if (_ftidx == aryFotter.Count - 1)
|
ftStyle.BorderBottom = BorderStyle.Thin;
|
else
|
ftStyle.BorderBottom = BorderStyle.None;
|
ftRow = sheet.CreateRow(_rot);
|
ftRow.CreateCell(0).SetCellValue(_hh);
|
ftRow.GetCell(0).CellStyle = ftStyle;
|
//cellRangAddress参数说明：起始行，结束行，起始列，结束列
|
sheet.AddMergedRegion(
|
new CellRangeAddress(_rot, _rot, 0, _ftw - 1));
|
ftRow.CreateCell(_ftw)
|
.SetCellValue(aryFotter2[_ftidx].ToString());
|
ftRow.GetCell(_ftw).CellStyle = ftStyle;
|
sheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, _ftw,
|
dtSource.Columns.Count - 1));
|
for (var c = 0; c < dtSource.Columns.Count; c++)
|
{
|
var cell = HSSFCellUtil.GetCell(ftRow, c);
|
cell.CellStyle = ftStyle;
|
}
|
|
_rot++;
|
_ftidx++;
|
}
|
}
|
|
//保存
|
using (var ms = new MemoryStream())
|
{
|
using (var fs = new FileStream(strFileName, FileMode.Create,
|
FileAccess.Write))
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
var data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="workbook"></param>
|
/// <param name="fontSize">字体大小</param>
|
/// <param name="isBold">是否粗体</param>
|
/// <param name="isCenter">是否中间</param>
|
/// <param name="border">是否边框</param>
|
/// <returns></returns>
|
private static ICellStyle getStyle(HSSFWorkbook workbook, double fontSize = 11, bool isBold = false, bool isCenter = false, bool border = true)
|
{
|
IFont font = workbook.CreateFont();
|
font.FontName = "宋体";
|
font.FontHeightInPoints = fontSize;
|
if (isBold == true)
|
font.Boldweight = (short)FontBoldWeight.Bold;
|
// 创建单元格样式 - 带边框和指定字体
|
ICellStyle style = workbook.CreateCellStyle();
|
style.SetFont(font);
|
if (border == true)
|
{
|
style.BorderTop = BorderStyle.Thin;
|
style.BorderBottom = BorderStyle.Thin;
|
style.BorderLeft = BorderStyle.Thin;
|
style.BorderRight = BorderStyle.Thin;
|
}
|
if (isCenter == true)
|
style.Alignment = HorizontalAlignment.Center;
|
style.VerticalAlignment = VerticalAlignment.Center;
|
return style;
|
}
|
|
/// <summary>
|
/// 设置单元格的值
|
/// </summary>
|
/// <param name="sheet"></param>
|
/// <param name="rowIdx"></param>
|
/// <param name="colIdx"></param>
|
/// <param name="val"></param>
|
private static void setCellVal(ISheet sheet, int rowIdx, int colIdx, string val)
|
{
|
IRow row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
|
ICell cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);
|
cell.SetCellValue(val); // 可以设置字符串、数字等类型的数据
|
|
}
|
/// <summary>
|
/// 生成首检
|
/// </summary>
|
/// <param name="dtSource"></param>
|
public static void ExportShouJian(DataSet dtSource, string strFileName)
|
{
|
//行高
|
int rowHeight = 25;
|
//总7列
|
int maxCols = 7;
|
// 创建工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
ISheet sheet = workbook.CreateSheet("Sheet1");
|
|
// 创建单元格样式 - 带边框和指定字体
|
ICellStyle style = getStyle(workbook);
|
ICellStyle styleHeader = getStyle(workbook, 20, true, true);
|
ICellStyle style12 = getStyle(workbook, 11, false, true);
|
|
// 设置列宽（6列）
|
sheet.SetColumnWidth(0, 4000); // 第一列稍宽
|
sheet.SetColumnWidth(1, 3000);
|
sheet.SetColumnWidth(2, 3000);
|
sheet.SetColumnWidth(3, 3000);
|
sheet.SetColumnWidth(4, 3000);
|
sheet.SetColumnWidth(5, 3000);
|
sheet.SetColumnWidth(6, 3000);
|
// 创建20行
|
for (int rowIndex = 0; rowIndex <= 6; rowIndex++)
|
{
|
IRow row = sheet.CreateRow(rowIndex);
|
// 设置行高（所有行相同高度）
|
row.HeightInPoints = rowHeight;
|
// 创建6个单元格
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row.CreateCell(colIndex);
|
if (rowIndex == 0)
|
cell.CellStyle = styleHeader;
|
else
|
cell.CellStyle = style;
|
}
|
}
|
//这是表头
|
DataTable tb0 = dtSource.Tables[0];
|
DataRow row0 = tb0.Rows[0];
|
// 1. 第一行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
|
// 2. 第二行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 6));
|
// 第三行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 5, 6));
|
// 3. 第4行中的2至6列合并
|
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, 6));
|
// 4. 第5行中，2，3列合并，5，6列合并
|
sheet.AddMergedRegion(new CellRangeAddress(4, 4, 1, 2));
|
sheet.AddMergedRegion(new CellRangeAddress(4, 4, 4, 6));
|
//****合并好后开始赋值beg**************
|
setCellVal(sheet, 0, 0, row0["title"].ToString());
|
setCellVal(sheet, 1, 0, row0["title2"].ToString() + row0["title3"].ToString());
|
setCellVal(sheet, 2, 0, "工单号");
|
setCellVal(sheet, 2, 1, row0["daaNo"].ToString());
|
setCellVal(sheet, 2, 2, "产品名称");
|
setCellVal(sheet, 2, 3, row0["itemName"].ToString());
|
setCellVal(sheet, 2, 4, "规格型号");
|
setCellVal(sheet, 2, 5, row0["itemModel"].ToString());
|
setCellVal(sheet, 3, 0, "本批唯一码");
|
setCellVal(sheet, 3, 1, row0["wyb"].ToString());
|
setCellVal(sheet, 4, 0, "首件唯一码");
|
setCellVal(sheet, 4, 1, row0["sjwym"].ToString());
|
setCellVal(sheet, 4, 3, "首样条形码");
|
setCellVal(sheet, 4, 4, row0["txm"].ToString());
|
setCellVal(sheet, 5, 0, "生产单位");
|
setCellVal(sheet, 5, 1, "生产车间");
|
setCellVal(sheet, 5, 2, row0["sccj"].ToString());
|
setCellVal(sheet, 5, 3, "批量");
|
setCellVal(sheet, 5, 4, row0["pl"].ToString());
|
setCellVal(sheet, 5, 5, "生产日期");
|
setCellVal(sheet, 5, 6, row0["scrq"].ToString());
|
string[] ary = { "项目", "技术/品质要求", "测试数据", "检验结果", "A", "B", "C" };
|
for (int i = 0; i < ary.Length; i++)
|
{
|
setCellVal(sheet, 6, i, ary[i]);
|
}
|
//从第7行开始，就是循环数据库
|
int _idx = 7;
|
DataTable tb1 = dtSource.Tables[1];
|
string dd = "";
|
bool _blPscs = false;
|
bool _blGjyzx = false;
|
foreach (DataRow rrr in tb1.Rows)
|
{
|
//增加品水测试标题
|
if (_blPscs == false && rrr["项目"].ToString() == "品水测试")
|
{
|
IRow row700 = sheet.CreateRow(_idx);
|
for (int i = 0; i < ary.Length; i++)
|
{
|
ICell cell700 = row700.CreateCell(i);
|
cell700.CellStyle = style;
|
if (i == 0)
|
cell700.SetCellValue(rrr["项目"].ToString());
|
if (i == 1)
|
cell700.SetCellValue("温度");
|
if (i == 3)
|
cell700.SetCellValue("流量");
|
if (i == 5)
|
cell700.SetCellValue("是否有异味");
|
if (i == 6)
|
cell700.SetCellValue("检验结果");
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 4));
|
_idx++;
|
_blPscs = true;
|
}
|
//增加标题
|
if (_blGjyzx == false && rrr["项目"].ToString() == "关键部件一致性")
|
{
|
IRow row700 = sheet.CreateRow(_idx);
|
for (int i = 0; i < ary.Length; i++)
|
{
|
ICell cell700 = row700.CreateCell(i);
|
cell700.CellStyle = style;
|
if (i == 0)
|
cell700.SetCellValue(rrr["项目"].ToString());
|
if (i == 1)
|
cell700.SetCellValue("关键名称");
|
if (i == 2)
|
cell700.SetCellValue("生产日期");
|
if (i == 3)
|
cell700.SetCellValue("使用数量");
|
if (i == 4)
|
cell700.SetCellValue("认证信息");
|
if (i == 5)
|
cell700.SetCellValue("是否符合CCC一致性");
|
if (i == 6)
|
cell700.SetCellValue("操作");
|
}
|
_idx++;
|
_blGjyzx = true;
|
}
|
|
IRow row7 = sheet.CreateRow(_idx);
|
row7.HeightInPoints = rowHeight;
|
string _tmphb = rrr["项目合并行数"].ToString();
|
string _tmp = "";
|
if (dd == rrr["项目"].ToString())
|
_tmp = "";
|
else
|
_tmp = rrr["项目"].ToString();
|
dd = rrr["项目"].ToString();
|
for (int i = 0; i < ary.Length; i++)
|
{
|
ICell cell7 = row7.CreateCell(i);
|
cell7.CellStyle = style;
|
if (i == 0)
|
{
|
cell7.SetCellValue(_tmp);
|
if (!string.IsNullOrEmpty(_tmp) && int.Parse(_tmphb) > 1)
|
{
|
if (rrr["项目"].ToString() == "品水测试" || rrr["项目"].ToString() == "关键部件一致性" )
|
sheet.AddMergedRegion(new CellRangeAddress(_idx-1, _idx + int.Parse(_tmphb)-1, 0, 0));
|
else
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + int.Parse(_tmphb) - 1, 0, 0));
|
}
|
}
|
else
|
{
|
switch (rrr["项目"].ToString())
|
{
|
case "制热性能":
|
cell7.SetCellValue(rrr["制热性能table"].ToString());
|
row7.HeightInPoints = rowHeight * 5;
|
if (i == ary.Length - 1)
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 6));
|
break;
|
case "品水测试":
|
//度数
|
if (i == 1)
|
cell7.SetCellValue(rrr["技术/品质要求"].ToString());
|
if (i == 3)
|
cell7.SetCellValue(rrr["品水测试流量"].ToString());
|
if (i == 5)
|
cell7.SetCellValue(rrr["是否有异味"].ToString());
|
if (i == 6)
|
cell7.SetCellValue(rrr["检验结果"].ToString());
|
if (i == ary.Length - 1)
|
{
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 4));
|
}
|
break;
|
case "关键部件一致性":
|
if (i == 1)
|
cell7.SetCellValue(rrr["技术/品质要求"].ToString());
|
if (i == 2)
|
cell7.SetCellValue(rrr["生产日期"].ToString());
|
if (i == 3)
|
cell7.SetCellValue(rrr["使用数量"].ToString());
|
if (i == 4)
|
cell7.SetCellValue(rrr["认证信息"].ToString());
|
if (i == 5)
|
cell7.SetCellValue(rrr["是否符合CCC一致性"].ToString());
|
//if (i == 6)
|
// cell7.SetCellValue(rrr["检验结果"].ToString());
|
break;
|
default:
|
cell7.SetCellValue(rrr[ary[i]].ToString());
|
break;
|
}
|
}
|
}
|
_idx++;
|
}
|
IRow row8 = sheet.CreateRow(_idx);
|
row8.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row8.CreateCell(colIndex);
|
cell.CellStyle = style12;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
|
setCellVal(sheet, _idx, 0, "综合判定");
|
setCellVal(sheet, _idx, 1, "合格");
|
_idx++;
|
IRow row9 = sheet.CreateRow(_idx);
|
row9.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row9.CreateCell(colIndex);
|
cell.CellStyle = style;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 3));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 4, 6));
|
setCellVal(sheet, _idx, 1, "工艺 已确认：合格");
|
setCellVal(sheet, _idx, 4, "生产 已确认：合格");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx, 0, 0));
|
_idx++;
|
|
IRow row10 = sheet.CreateRow(_idx);
|
row10.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row10.CreateCell(colIndex);
|
cell.CellStyle = style12;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
|
setCellVal(sheet, _idx, 0, "审核意见");
|
setCellVal(sheet, _idx, 1, "合格");
|
_idx++;
|
|
IRow row11 = sheet.CreateRow(_idx);
|
row11.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row11.CreateCell(colIndex);
|
cell.CellStyle = style12;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 6));
|
setCellVal(sheet, _idx, 0, "首件填写,来料检验");
|
//****合并好后开始赋值end**************
|
//保存
|
using (var ms = new MemoryStream())
|
{
|
using (var fs = new FileStream(strFileName, FileMode.Create,
|
FileAccess.Write))
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
var data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 生成iqc
|
/// </summary>
|
/// <param name="dtSource"></param>
|
public static void ExportIqc(DataSet dtSource, string strFileName)
|
{
|
//行高
|
int rowHeight = 25;
|
//总列
|
int maxCols = 8;
|
// 创建工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
ISheet sheet = workbook.CreateSheet("Sheet1");
|
|
// 创建单元格样式 - 带边框和指定字体
|
ICellStyle style = getStyle(workbook);
|
ICellStyle styleHeader = getStyle(workbook, 20, true, true);
|
ICellStyle style12 = getStyle(workbook, 11, false, true);
|
|
// 设置列宽（6列）
|
sheet.SetColumnWidth(0, 3000); // 第一列稍宽
|
sheet.SetColumnWidth(1, 3000);
|
sheet.SetColumnWidth(2, 3000);
|
sheet.SetColumnWidth(3, 3000);
|
sheet.SetColumnWidth(4, 3000);
|
sheet.SetColumnWidth(5, 3000);
|
sheet.SetColumnWidth(6, 3000);
|
sheet.SetColumnWidth(7, 3000);
|
// 创建9行
|
for (int rowIndex = 0; rowIndex <= 8; rowIndex++)
|
{
|
IRow row = sheet.CreateRow(rowIndex);
|
// 设置行高（所有行相同高度）
|
row.HeightInPoints = rowHeight;
|
// 创建个单元格
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row.CreateCell(colIndex);
|
if (rowIndex == 1)
|
cell.CellStyle = styleHeader;
|
else
|
cell.CellStyle = style;
|
// 添加一些示例数据（可根据需要修改）
|
cell.SetCellValue($"行{rowIndex + 1}列{colIndex + 1}");
|
}
|
}
|
//这是表头
|
DataTable tb0 = dtSource.Tables[0];
|
DataRow row0 = tb0.Rows[0];
|
//第一行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, 7));
|
//第二行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));
|
//第三行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 1));
|
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 2, 5));
|
//第4
|
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, 1));
|
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 2, 5));
|
//第5行
|
sheet.AddMergedRegion(new CellRangeAddress(4, 4, 0, 1));
|
sheet.AddMergedRegion(new CellRangeAddress(4, 4, 2, 5));
|
//抽样依据
|
sheet.AddMergedRegion(new CellRangeAddress(5, 5, 0, 1));
|
sheet.AddMergedRegion(new CellRangeAddress(5, 5, 2, 7));
|
////6,7行抽样数合并
|
sheet.AddMergedRegion(new CellRangeAddress(6, 7, 4, 4));
|
sheet.AddMergedRegion(new CellRangeAddress(6, 7, 5, 5));
|
sheet.AddMergedRegion(new CellRangeAddress(6, 7, 6, 6));
|
sheet.AddMergedRegion(new CellRangeAddress(6, 7, 7, 7));
|
////第8行标题行
|
sheet.AddMergedRegion(new CellRangeAddress(8, 8, 1, 5));
|
//****合并好后开始赋值beg**************
|
setCellVal(sheet, 0, 0, row0["title"].ToString());
|
setCellVal(sheet, 0, 4, row0["title2"].ToString());
|
setCellVal(sheet, 1, 0, row0["title3"].ToString());
|
setCellVal(sheet, 2, 0, "物料名称");
|
setCellVal(sheet, 2, 2, row0["itemName"].ToString());
|
setCellVal(sheet, 2, 6, "物料编码");
|
setCellVal(sheet, 2, 7, row0["itemNo"].ToString());
|
setCellVal(sheet, 3, 0, "送货单位");
|
setCellVal(sheet, 3, 2, row0["shdw"].ToString());
|
setCellVal(sheet, 3, 6, "送检日期");
|
setCellVal(sheet, 3, 7, row0["sjrq"].ToString());
|
setCellVal(sheet, 4, 0, "型号规格");
|
setCellVal(sheet, 4, 2, row0["itemModel"].ToString());
|
setCellVal(sheet, 4, 6, "批量");
|
setCellVal(sheet, 4, 7, row0["pl"].ToString());
|
setCellVal(sheet, 5, 0, "抽样依据");
|
setCellVal(sheet, 5, 2, row0["cyyj"].ToString());
|
setCellVal(sheet, 6, 0, "AQL");
|
setCellVal(sheet, 6, 1, "CR");
|
setCellVal(sheet, 6, 2, "MA");
|
setCellVal(sheet, 6, 3, "MI");
|
setCellVal(sheet, 6, 4, "抽样数");
|
setCellVal(sheet, 6, 5, row0["cys"].ToString());
|
setCellVal(sheet, 6, 6, "不合格数");
|
setCellVal(sheet, 6, 7, row0["bhgs"].ToString());
|
setCellVal(sheet, 7, 0, "判定Ac/Re");
|
setCellVal(sheet, 7, 1, row0["cr"].ToString());
|
setCellVal(sheet, 7, 2, row0["ma"].ToString());
|
setCellVal(sheet, 7, 3, row0["mi"].ToString());
|
setCellVal(sheet, 8, 0, "项目");
|
setCellVal(sheet, 8, 1, "质量要求");
|
setCellVal(sheet, 8, 6, "检验记录");
|
setCellVal(sheet, 8, 7, "检验结果");
|
////从第9行开始，就是循环数据库
|
int _idx = 9;
|
DataTable tb1 = dtSource.Tables[1];
|
foreach (DataRow rrr in tb1.Rows)
|
{
|
IRow row7 = sheet.CreateRow(_idx);
|
row7.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell7 = row7.CreateCell(i);
|
cell7.CellStyle = style;
|
// cell7.SetCellValue(rrr[ary[i]].ToString());
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
|
setCellVal(sheet, _idx, 0, rrr["项目"].ToString());
|
setCellVal(sheet, _idx, 1, rrr["质量要求"].ToString());
|
setCellVal(sheet, _idx, 6, rrr["检验记录"].ToString());
|
setCellVal(sheet, _idx, 7, rrr["检验结果"].ToString());
|
_idx++;
|
}
|
//备注
|
IRow row8 = sheet.CreateRow(_idx);
|
row8.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row8.CreateCell(colIndex);
|
cell.CellStyle = style12;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 7));
|
setCellVal(sheet, _idx, 0, "备注：");
|
_idx++;
|
//综合判定
|
for (int i = 0; i < 2; i++)
|
{
|
IRow row9 = sheet.CreateRow(_idx);
|
row9.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row9.CreateCell(colIndex);
|
cell.CellStyle = style;
|
}
|
_idx++;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 1, 0, 0));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 2, 1, 7));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx - 1, 1, 7));
|
setCellVal(sheet, _idx - 2, 0, "综合判定：");
|
setCellVal(sheet, _idx - 2, 1, row0["zhpd"].ToString());
|
setCellVal(sheet, _idx - 1, 1, row0["zhpdqz"].ToString());
|
//审核意见：
|
for (int i = 0; i < 2; i++)
|
{
|
IRow row9 = sheet.CreateRow(_idx);
|
row9.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row9.CreateCell(colIndex);
|
cell.CellStyle = style;
|
}
|
_idx++;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 1, 0, 0));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 2, 1, 7));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx - 1, 1, 7));
|
setCellVal(sheet, _idx - 2, 0, "审核意见：");
|
setCellVal(sheet, _idx - 2, 1, row0["shyj"].ToString());
|
setCellVal(sheet, _idx - 1, 1, row0["shyjqz"].ToString());
|
string[] ary = { "采购部意见", "销售部意见", "技术部意见", "生产部意见", "品质部意见" };
|
for (int i = 0; i < ary.Length; i++)
|
{
|
IRow row9 = sheet.CreateRow(_idx);
|
row9.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row9.CreateCell(colIndex);
|
cell.CellStyle = style;
|
}
|
// sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 1));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 7));
|
_idx++;
|
}
|
setCellVal(sheet, _idx - ary.Length, 0, "不合格评审：");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx - ary.Length, _idx - 1, 0, 0));
|
for (int i = 0; i < ary.Length; i++)
|
{
|
setCellVal(sheet, _idx - i - 1, 1, ary[i]);
|
setCellVal(sheet, _idx - i - 1, 2, "□ 同意 □ 不同意 签名/日期：");
|
}
|
//****合并好后开始赋值end**************
|
//保存
|
using (var ms = new MemoryStream())
|
{
|
using (var fs = new FileStream(strFileName, FileMode.Create,
|
FileAccess.Write))
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
var data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
|
/// <summary>
|
/// 生成巡检
|
/// </summary>
|
/// <param name="dtSource"></param>
|
public static void ExportXunJian(DataSet dtSource, string strFileName)
|
{
|
DataTable tb0 = dtSource.Tables[0];
|
DataRow row0 = tb0.Rows[0];
|
DataTable tb1 = dtSource.Tables[1];
|
//行高
|
int rowHeight = 25;
|
//总列
|
int maxCols = 10;
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
ISheet sheet = workbook.CreateSheet("Sheet1");
|
// 创建单元格样式 - 带边框和指定字体
|
ICellStyle style = getStyle(workbook);
|
ICellStyle styleHeader = getStyle(workbook, 20, true, true);
|
ICellStyle style12 = getStyle(workbook, 11, false, true);
|
// 设置列宽（10列）
|
sheet.SetColumnWidth(0, 3000); // 第一列稍宽
|
sheet.SetColumnWidth(1, 3000);
|
sheet.SetColumnWidth(2, 3000);
|
sheet.SetColumnWidth(3, 3000);
|
sheet.SetColumnWidth(4, 3000);
|
sheet.SetColumnWidth(5, 3000);
|
sheet.SetColumnWidth(6, 3000);
|
sheet.SetColumnWidth(7, 3000);
|
sheet.SetColumnWidth(8, 3000);
|
sheet.SetColumnWidth(9, 3000);
|
// 创建头行
|
for (int rowIndex = 0; rowIndex <= 2; rowIndex++)
|
{
|
IRow row = sheet.CreateRow(rowIndex);
|
// 设置行高（所有行相同高度）
|
row.HeightInPoints = rowHeight;
|
// 创建个单元格
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row.CreateCell(colIndex);
|
if (rowIndex == 0)
|
cell.CellStyle = styleHeader;
|
else
|
cell.CellStyle = style;
|
}
|
}
|
//第一行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 9));
|
setCellVal(sheet, 0, 0, row0["title"].ToString());
|
//第二行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
|
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 6, 9));
|
setCellVal(sheet, 1, 0, "工单号");
|
setCellVal(sheet, 1, 2, row0["daaNo"].ToString());
|
setCellVal(sheet, 1, 3, "产品名称");
|
setCellVal(sheet, 1, 4, row0["itemName"].ToString());
|
setCellVal(sheet, 1, 5, "规格型号");
|
setCellVal(sheet, 1, 6, row0["itemModel"].ToString());
|
//第三行列合并
|
sheet.AddMergedRegion(new CellRangeAddress(2, 2,2, 4));
|
setCellVal(sheet,2, 0, "序号");
|
setCellVal(sheet, 2, 1, "检验项目");
|
setCellVal(sheet, 2, 2, "检验标准");
|
setCellVal(sheet, 2, 5, row0["tm1"].ToString());
|
setCellVal(sheet, 2, 6, row0["tm2"].ToString());
|
setCellVal(sheet, 2, 7, row0["tm3"].ToString());
|
setCellVal(sheet, 2, 8, row0["tm4"].ToString());
|
setCellVal(sheet, 2, 9, row0["tm5"].ToString());
|
//循环数据库
|
int _idx = 3;
|
foreach (DataRow rrr in tb1.Rows)
|
{
|
IRow row7 = sheet.CreateRow(_idx);
|
row7.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell7 = row7.CreateCell(colIndex);
|
cell7.CellStyle = style;
|
// cell7.SetCellValue("1");
|
switch (colIndex)
|
{
|
case 0:
|
cell7.SetCellValue(rrr["序号"].ToString());
|
break;
|
case 1:
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 4));
|
cell7.SetCellValue(rrr["检验项目"].ToString());
|
break;
|
case 2:
|
cell7.SetCellValue(rrr["检验标准"].ToString());
|
break;
|
case 5:
|
cell7.SetCellValue(rrr["tm1"].ToString());
|
break;
|
case 6:
|
cell7.SetCellValue(rrr["tm2"].ToString());
|
break;
|
case 7:
|
cell7.SetCellValue(rrr["tm3"].ToString());
|
break;
|
case 8:
|
cell7.SetCellValue(rrr["tm4"].ToString());
|
break;
|
case 9:
|
cell7.SetCellValue(rrr["tm5"].ToString());
|
break;
|
}
|
}
|
_idx++;
|
}
|
////备注
|
IRow row8 = sheet.CreateRow(_idx);
|
row8.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell = row8.CreateCell(colIndex);
|
cell.CellStyle = style12;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 4));
|
setCellVal(sheet, _idx, 0, "巡检人：");
|
setCellVal(sheet, _idx, 5, row0["xjr1"].ToString());
|
setCellVal(sheet, _idx, 6, row0["xjr2"].ToString());
|
setCellVal(sheet, _idx, 7, row0["xjr3"].ToString());
|
setCellVal(sheet, _idx, 8, row0["xjr4"].ToString());
|
setCellVal(sheet, _idx, 9, row0["xjr5"].ToString());
|
//保存
|
using (var ms = new MemoryStream())
|
{
|
using (var fs = new FileStream(strFileName, FileMode.Create,
|
FileAccess.Write))
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
var data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 生成成品检验报告（按实际模板结构）
|
/// </summary>
|
/// <param name="dtSource"></param>
|
/// <param name="strFileName"></param>
|
public static void ExportChengPin(DataSet dtSource, string strFileName)
|
{
|
//行高
|
int rowHeight = 25;
|
//总列数 - 根据模板分析，至少需要18列
|
int maxCols = 18;
|
// 创建工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
ISheet sheet = workbook.CreateSheet("Sheet1");
|
|
// 创建单元格样式
|
ICellStyle style = getStyle(workbook);
|
ICellStyle styleHeader = getStyle(workbook, 16, true, true);
|
ICellStyle styleTitle = getStyle(workbook, 14, true, true);
|
ICellStyle styleSmall = getStyle(workbook, 10, false, false);
|
|
// 设置列宽
|
sheet.SetColumnWidth(0, 3500); // A列：项目名称
|
sheet.SetColumnWidth(1, 5000); // B列：标准/规范
|
for (int i = 2; i < 18; i++) // C到R列：16个检测值列
|
{
|
sheet.SetColumnWidth(i, 2500);
|
}
|
|
//获取表头数据
|
DataTable tb0 = dtSource.Tables[0];
|
DataRow row0 = tb0.Rows[0];
|
int _idx = 0;
|
|
// 第1行：报告标题
|
IRow titleRow = sheet.CreateRow(_idx);
|
titleRow.HeightInPoints = 30;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = titleRow.CreateCell(i);
|
cell.CellStyle = styleHeader;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, maxCols - 1));
|
setCellVal(sheet, _idx, 0, "成品检验报告");
|
_idx++;
|
|
// 第2行：基本信息第一行
|
IRow info1Row = sheet.CreateRow(_idx);
|
info1Row.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = info1Row.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
setCellVal(sheet, _idx, 0, "生产车间：" + (row0["workShop"]?.ToString() ?? ""));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 5));
|
|
setCellVal(sheet, _idx, 12, "室温：" + (row0["temperature"]?.ToString() ?? ""));
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 12, 17));
|
_idx++;
|
|
// 第3行：产品信息
|
IRow info2Row = sheet.CreateRow(_idx);
|
info2Row.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = info2Row.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
setCellVal(sheet, _idx, 0, "产品名称");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 4));
|
setCellVal(sheet, _idx, 1, row0["itemName"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 5, "型号规格");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 6, 11));
|
setCellVal(sheet, _idx, 6, row0["itemModel"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 12, "电压");
|
setCellVal(sheet, _idx, 13, row0["voltage"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 14, "线号");
|
setCellVal(sheet, _idx, 15, row0["lineNo"]?.ToString() ?? "");
|
_idx++;
|
|
// 第4行：商标等信息
|
IRow info3Row = sheet.CreateRow(_idx);
|
info3Row.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = info3Row.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
setCellVal(sheet, _idx, 0, "商标");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 4));
|
setCellVal(sheet, _idx, 1, row0["brand"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 5, "生产日期");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 6, 11));
|
setCellVal(sheet, _idx, 6, row0["productionDate"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 12, "班次");
|
setCellVal(sheet, _idx, 13, row0["classes"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 14, "批量");
|
setCellVal(sheet, _idx, 15, row0["batch"]?.ToString() ?? "");
|
_idx++;
|
|
// 第5行：AC/Re标准
|
IRow acreRow = sheet.CreateRow(_idx);
|
acreRow.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = acreRow.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
setCellVal(sheet, _idx, 0, "Ac/Re(A类）");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
|
setCellVal(sheet, _idx, 1, row0["acRe_A"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 6, "Ac/Re(B类）");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 11));
|
setCellVal(sheet, _idx, 7, row0["acRe_B"]?.ToString() ?? "");
|
|
setCellVal(sheet, _idx, 12, "Ac/Re(C类）");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 17));
|
setCellVal(sheet, _idx, 13, row0["acRe_C"]?.ToString() ?? "");
|
_idx++;
|
|
// 第6行：抽样信息
|
IRow sampleRow = sheet.CreateRow(_idx);
|
sampleRow.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = sampleRow.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
setCellVal(sheet, _idx, 0, "抽样");
|
setCellVal(sheet, _idx, 1, "匀速");
|
setCellVal(sheet, _idx, 2, row0["SampleMethod"]?.ToString() == "匀速" ? "√" : "");
|
setCellVal(sheet, _idx, 3, "随机");
|
setCellVal(sheet, _idx, 4, row0["SampleMethod"]?.ToString() == "随机" ? "√" : "");
|
|
setCellVal(sheet, _idx, 5, "样本数");
|
setCellVal(sheet, _idx, 6, "匀速抽样数");
|
setCellVal(sheet, _idx, 7, row0["sampleSize1"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 8, "随机抽样数");
|
setCellVal(sheet, _idx, 9, row0["sampleSize2"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 10, "样本");
|
setCellVal(sheet, _idx, 11, row0["sampleSize3"]?.ToString() ?? "");
|
_idx++;
|
|
// 第7行：检验项目表头
|
IRow headerRow = sheet.CreateRow(_idx);
|
headerRow.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = headerRow.CreateCell(i);
|
cell.CellStyle = styleTitle;
|
}
|
setCellVal(sheet, _idx, 0, "项目");
|
setCellVal(sheet, _idx, 1, "标准");
|
setCellVal(sheet, _idx, 2, "1");
|
setCellVal(sheet, _idx, 3, "2");
|
// 根据需要继续添加更多列标题，这里简化为主要列
|
for (int i = 4; i < 18; i++)
|
{
|
setCellVal(sheet, _idx, i, (i-1).ToString());
|
}
|
_idx++;
|
|
// 检验项目数据
|
if (dtSource.Tables.Count > 1)
|
{
|
DataTable tb1 = dtSource.Tables[1];
|
foreach (DataRow rrr in tb1.Rows)
|
{
|
IRow dataRow = sheet.CreateRow(_idx);
|
dataRow.HeightInPoints = rowHeight;
|
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = dataRow.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
|
setCellVal(sheet, _idx, 0, rrr["RPB003"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 1, rrr["RPB004"]?.ToString() ?? "");
|
|
// 填充16个测试值
|
for (int i = 0; i < 16; i++)
|
{
|
string colName = $"RPB{i + 5:D3}"; // RPB005, RPB006, ..., RPB020
|
if (rrr.Table.Columns.Contains(colName))
|
{
|
setCellVal(sheet, _idx, i + 2, rrr[colName]?.ToString() ?? "");
|
}
|
else
|
{
|
setCellVal(sheet, _idx, i + 2, "");
|
}
|
}
|
|
_idx++;
|
}
|
}
|
|
// 不合格内容记录
|
IRow defectHeaderRow = sheet.CreateRow(_idx);
|
defectHeaderRow.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = defectHeaderRow.CreateCell(i);
|
cell.CellStyle = styleTitle;
|
}
|
setCellVal(sheet, _idx, 0, "序号");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 10)); // 不合格内容占多列
|
setCellVal(sheet, _idx, 1, "不合格内容");
|
setCellVal(sheet, _idx, 11, "A类");
|
setCellVal(sheet, _idx, 12, "B类");
|
setCellVal(sheet, _idx, 13, "C类");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 注意点占剩余列
|
setCellVal(sheet, _idx, 14, "注意点");
|
_idx++;
|
|
if (dtSource.Tables.Count > 2)
|
{
|
DataTable tb2 = dtSource.Tables[2];
|
foreach (DataRow rrr in tb2.Rows)
|
{
|
if (rrr["Description"]?.ToString() == "不良数量")
|
break;
|
IRow dataRow = sheet.CreateRow(_idx);
|
dataRow.HeightInPoints = rowHeight;
|
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = dataRow.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
|
setCellVal(sheet, _idx, 0, rrr["Seq"]?.ToString() ?? "");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 10)); // 不合格内容占多列
|
setCellVal(sheet, _idx, 1, rrr["Description"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 11, rrr["AClass"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 12, rrr["BClass"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 13, rrr["CClass"]?.ToString() ?? "");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 注意点占剩余列
|
setCellVal(sheet, _idx, 14, rrr["Point"]?.ToString() ?? "");
|
_idx++;
|
}
|
|
DataRow dr = tb2.Rows[tb2.Rows.Count - 1];
|
// 备注行
|
IRow remarkRow = sheet.CreateRow(_idx);
|
remarkRow.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = remarkRow.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
setCellVal(sheet, _idx, 0, "备注：");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 8));
|
setCellVal(sheet, _idx, 9, "不良数量：");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 9, 10));
|
setCellVal(sheet, _idx, 11, dr["AClass"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 12, dr["BClass"]?.ToString() ?? "");
|
setCellVal(sheet, _idx, 13, dr["CClass"]?.ToString() ?? "");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 注意点占剩余列
|
setCellVal(sheet, _idx, 14, dr["Point"]?.ToString() ?? "");
|
_idx++;
|
|
// 大的备注内容区域
|
IRow remarkContentRow = sheet.CreateRow(_idx);
|
remarkContentRow.HeightInPoints = 60;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = remarkContentRow.CreateCell(i);
|
cell.CellStyle = style;
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 17));
|
setCellVal(sheet, _idx, 0, dr["Remark"]?.ToString() ?? "");
|
_idx++;
|
}
|
|
// 检验判定区域（3行布局）
|
for (int judgeRowIndex = 0; judgeRowIndex < 3; judgeRowIndex++)
|
{
|
IRow judgmentRow = sheet.CreateRow(_idx);
|
judgmentRow.HeightInPoints = rowHeight;
|
for (int i = 0; i < maxCols; i++)
|
{
|
ICell cell = judgmentRow.CreateCell(i);
|
cell.CellStyle = styleTitle;
|
}
|
|
if (judgeRowIndex == 0)
|
{
|
// 第一行：检验判定
|
setCellVal(sheet, _idx, 0, "检验判定");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + 2, 0, 0)); // 检验判定跨3行
|
|
if (row0["CheckResult"]?.ToString() == "合格")
|
{
|
setCellVal(sheet, _idx, 1, "√ 合格 □ 不合格");
|
}
|
else if(row0["CheckResult"]?.ToString() == "不合格")
|
{
|
setCellVal(sheet, _idx, 1, "□ 合格 √ 不合格");
|
}
|
else
|
{
|
setCellVal(sheet, _idx, 1, "□ 合格 □ 不合格");
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + 1, 1, 5)); // 合格/不合格跨2行
|
|
setCellVal(sheet, _idx, 6, "审核");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + 2, 6, 6)); // 审核跨3行
|
|
if (row0["ProcessResults"]?.ToString() == "无")
|
{
|
setCellVal(sheet, _idx, 7, "√ 无 □ 返工");
|
}
|
else if (row0["ProcessResults"]?.ToString() == "返工")
|
{
|
setCellVal(sheet, _idx, 7, "□ 无 √ 返工");
|
}
|
else
|
{
|
setCellVal(sheet, _idx, 7, "□ 无 □ 返工");
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 11));
|
|
setCellVal(sheet, _idx, 12, "审批");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + 2, 12, 12)); // 审批跨3行
|
|
if (row0["ProcessResults"]?.ToString() == "无")
|
{
|
setCellVal(sheet, _idx, 13, "√ 无 □ 返工");
|
}
|
else if (row0["ProcessResults"]?.ToString() == "返工")
|
{
|
setCellVal(sheet, _idx, 13, "□ 无 √ 返工");
|
}
|
else
|
{
|
setCellVal(sheet, _idx, 13, "□ 无 □ 返工");
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 17));
|
}
|
else if (judgeRowIndex == 1)
|
{
|
if (row0["ProcessResults"]?.ToString() == "让步接收")
|
{
|
setCellVal(sheet, _idx, 7, "√ 让步接收 □ 特采");
|
}
|
else if (row0["ProcessResults"]?.ToString() == "特采")
|
{
|
setCellVal(sheet, _idx, 7, "□ 让步接收 √ 特采");
|
}
|
else
|
{
|
setCellVal(sheet, _idx, 7, "□ 让步接收 □ 特采");
|
}
|
// 第二行：让步接收选项
|
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 11));
|
|
if (row0["ProcessResults"]?.ToString() == "让步接收")
|
{
|
setCellVal(sheet, _idx, 13, "√ 让步接收 □ 特采");
|
}
|
else if (row0["ProcessResults"]?.ToString() == "特采")
|
{
|
setCellVal(sheet, _idx, 13, "□ 让步接收 √ 特采");
|
}
|
else
|
{
|
setCellVal(sheet, _idx, 13, "□ 让步接收 □ 特采");
|
}
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 17));
|
}
|
else
|
{
|
// 签名行
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
|
setCellVal(sheet, _idx, 1, $"签名/日期：");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 5));
|
setCellVal(sheet, _idx, 3, $"{row0["JY_USER"]?.ToString() ?? ""}/{row0["JY_DATE"]?.ToString() ?? ""}");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 8));
|
setCellVal(sheet, _idx, 7, $"签名/日期：");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 9, 11));
|
setCellVal(sheet, _idx, 9, $"{row0["CHECK_USER"]?.ToString() ?? ""}/{row0["CHECK_DATE"]?.ToString() ?? ""}");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 14));
|
setCellVal(sheet, _idx, 13, $"签名/日期：");
|
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 15, 17));
|
setCellVal(sheet, _idx, 15, $"{row0["SP_USER"]?.ToString() ?? ""}/{row0["SP_DATE"]?.ToString() ?? ""}");
|
}
|
_idx++;
|
}
|
//保存
|
using (var ms = new MemoryStream())
|
{
|
using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
var data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
}
|
|
/// <summary>
|
/// Excel导入成Datable
|
/// </summary>
|
/// <param name="file">导入路径(包含文件名与扩展名)</param>
|
/// <returns></returns>
|
public static DataTable ExcelToTable(string file)
|
{
|
var dt = new DataTable();
|
IWorkbook workbook;
|
var fileExt = Path.GetExtension(file).ToLower();
|
using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
|
{
|
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
|
if (fileExt == ".xlsx")
|
workbook = new XSSFWorkbook(fs);
|
else if (fileExt == ".xls")
|
workbook = new HSSFWorkbook(fs);
|
else
|
workbook = null;
|
if (workbook == null) return null;
|
// int numberOfSheets = SXSSFWorkbook.getNumberOfSheets();
|
var numberOfSheets = workbook.NumberOfSheets; //获取表的数量
|
|
var sheet = workbook.GetSheetAt(0);
|
//表头
|
var header = sheet.GetRow(sheet.FirstRowNum + 1);
|
var columns = new List<int>();
|
for (var i = 0; i < header.LastCellNum; i++)
|
{
|
var obj = GetValueType(header.GetCell(i));
|
if (obj == null || obj.ToString() == string.Empty)
|
dt.Columns.Add(new DataColumn("Columns" + i));
|
else
|
dt.Columns.Add(new DataColumn(obj.ToString().Trim()));
|
columns.Add(i);
|
}
|
|
for (var act = 0; act < numberOfSheets; act++)
|
{
|
var _sheet = workbook.GetSheetAt(act);
|
//数据
|
for (var i = _sheet.FirstRowNum + 1 + 1;
|
i <= _sheet.LastRowNum;
|
i++)
|
{
|
var dr = dt.NewRow();
|
var hasValue = false;
|
foreach (var j in columns)
|
{
|
dr[j] = GetValueType(_sheet.GetRow(i).GetCell(j));
|
if (dr[j] != null && dr[j].ToString() != string.Empty)
|
hasValue = true;
|
}
|
|
if (hasValue) dt.Rows.Add(dr);
|
}
|
}
|
}
|
|
return dt;
|
}
|
|
/// <summary>
|
/// 获取单元格类型
|
/// </summary>
|
/// <param name="cell"></param>
|
/// <returns></returns>
|
private static object GetValueType(ICell cell)
|
{
|
if (cell == null)
|
return null;
|
switch (cell.CellType)
|
{
|
case CellType.Blank: //BLANK:
|
return null;
|
case CellType.Boolean: //BOOLEAN:
|
return cell.BooleanCellValue;
|
case CellType.Numeric: //NUMERIC:
|
return cell.NumericCellValue;
|
case CellType.String: //STRING:
|
return cell.StringCellValue.Trim();
|
case CellType.Error: //ERROR:
|
return cell.ErrorCellValue;
|
case CellType.Formula: //FORMULA:
|
default:
|
return "=" + cell.CellFormula;
|
}
|
}
|
}
|
