using System.Collections;
|
using System.Data;
|
using System.Text;
|
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];
|
foreach (DataRow rrr in tb1.Rows)
|
{
|
IRow row7 = sheet.CreateRow(_idx);
|
row7.HeightInPoints = rowHeight;
|
for (int i = 0; i < ary.Length; i++)
|
{
|
ICell cell7 = row7.CreateCell(i);
|
cell7.CellStyle = style;
|
cell7.SetCellValue(rrr[ary[i]].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, 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**************
|
|
// 5. 第8到10行中的第一列应被合并
|
//sheet.AddMergedRegion(new CellRangeAddress(7, 9, 0, 0));
|
|
//// 6. 第17，18行中的第一列合并
|
//sheet.AddMergedRegion(new CellRangeAddress(16, 17, 0, 0));
|
|
//// 7. 第17行中的2，3，4，5列合并
|
//sheet.AddMergedRegion(new CellRangeAddress(16, 16, 1, 4));
|
|
//// 8. 第18行中的，2，3列合并，4，5，6列合并
|
//sheet.AddMergedRegion(new CellRangeAddress(17, 17, 1, 2));
|
//sheet.AddMergedRegion(new CellRangeAddress(17, 17, 3, 5));
|
|
//// 9. 第19行中的，列2，3，4合并
|
//sheet.AddMergedRegion(new CellRangeAddress(18, 18, 1, 3));
|
|
//// 10. 第20行中的列全部合并
|
//sheet.AddMergedRegion(new CellRangeAddress(19, 19, 0, 5));
|
|
//保存
|
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, "0/1");
|
setCellVal(sheet, 7, 2, "0/1");
|
setCellVal(sheet, 7, 3, "0/1");
|
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 = tb1.Columns.Count;
|
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);
|
// 创建行
|
for (int rowIndex = 0; rowIndex <= 1; 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, 7));
|
setCellVal(sheet, 0, 0, row0["title"].ToString());
|
//循环数据库
|
int _idx = 1;
|
IRow row6 = sheet.CreateRow(_idx);
|
for (int colIndex = 0; colIndex < maxCols; colIndex++)
|
{
|
ICell cell6 = row6.CreateCell(colIndex);
|
cell6.CellStyle = style;
|
cell6.SetCellValue(tb1.Columns[colIndex].Caption.ToString().Trim());
|
}
|
_idx++;
|
foreach (DataRow rrr in tb1.Rows)
|
{
|
IRow row7 = sheet.CreateRow(_idx);
|
row7.HeightInPoints = rowHeight;
|
for (int colIndex = 0; colIndex < tb1.Columns.Count; colIndex++)
|
{
|
ICell cell7 = row7.CreateCell(colIndex);
|
cell7.CellStyle = style;
|
cell7.SetCellValue(rrr[tb1.Columns[colIndex]].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, 0, 2));
|
setCellVal(sheet, _idx, 0, "巡检人：");
|
for (int colIndex = 2; colIndex < tb1.Columns.Count; colIndex++)
|
{
|
setCellVal(sheet, _idx, colIndex, "余吉林");
|
}
|
//保存
|
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;
|
}
|
}
|
}
|
