Excel上传
前段:ExportToExcel、UploadExcel
<!-- 模态框 -文件下载上传 -->
<div class="modal">
<div class="modal-content family">
<span class="close" onclick="ModalClose('modal')">×</span>
<p id="Item" class="modal-title">设置产出目标值</p>
<br>
<div class="form-flex">
<label for="projcode">projcode:</label>
<select id="projcode" name="projcode" placeholder="select projcode"></select>
<label for="product">product:</label>
<select id="product" name="product" placeholder="select projcode"></select>
<label for="process">process:</label>
<select id="process" name="process" placeholder="select process"></select>
<label>
<span><label><button type="submit" style="background-color: #49c5ce;" onclick="ExportToExcel()"><span><img src="/images/ProcessCapacity/Excel.ico" alt="Excel" class="icon"></span>生成Excel模板</button></label></span>
<span id="export"></span>
</label>
<span>
<label>导入模板:</label>
<span><input type="file" id="Template" name="Template" style="width:calc(100% - 0.78rem)" placeholder="Enter your Process"></span>
</span>
<label for="message">message:</label>
<input type="text" id="message" name="message" placeholder="Enter your message">
<button type="submit" style="font-weight: bold; height:0.5rem;" onclick="UploadExcel()">Submit</button>
</div>
</div>
</div>
Excel下载
/*目标值导出*/
var ExportToExcel = function () {
var projcode = $('#projcode').val();
var product = $('#product').val();
var pam = {
type: ExportType,
proj_code: $('#projcode').val(),
product_code: $('#product').val() == null ? "ALL" : $('#product').val(),
process_code: $('#process').val() == null ? "ALL" : $('#process').val()
};
//var url = "http://localhost:44304/Dashboard/ExportToExcel?" + $.param(pam);
var url = fisApiUrl + "/Dashboard/ExportToExcel?" + $.param(pam);
if (projcode && product) {
var xhr = new XMLHttpRequest();
xhr.open('GET',url,true);
xhr.responseType = 'blob';
xhr.onload = function () {
if (xhr.status === 200) {
var url = window.URL.createObjectURL(xhr.response);
var a = document.createElement('a');
a.href = url;
a.download = ExportType.toLocaleUpperCase()+'.xlsx';
a.click();
window.URL.revokeObjectURL(url);
}
};
xhr.send();
}
}
Excel上传
/*目标值上传*/
var UploadExcel = function () {
var fileInput = document.getElementById("Template");
var file = fileInput.files[0];
var formData = new FormData();
formData.append("file", file);
//formData.append("type", uploadType);
formData.append("type", ExportType);
var projcode = $('#projcode').val();
var product = $('#product').val();
if (projcode && product) {
$.ajax({
type: "POST",
//url: "http://localhost:44304/Dashboard/UploadExcel",
url: fisApiUrl + "/Dashboard/UploadExcel",
enctype: 'multipart/form-data',
data: formData,
async: false,
processData: false,// 禁止将数据转换为查询字符串
contentType: false,// 禁止设置请求头的Content-Type
crossDomain: true,
success: function (data) {
if (data.Success) {
alert("successfully");
//$('#product').empty();
//$('#process').empty();
$("#Template").val('');
$("#message").val('');
$(".modal").fadeOut();
}
},
error: function () {
console.log('Error No file uploaded..');
}
});
}
}
后端接口:Excel下载
[HttpGet]
public HttpResponseMessage ExportToExcel(string type,string proj_code,string product_code,string process_code)
{
if (string.IsNullOrWhiteSpace(proj_code))
{
return new HttpResponseMessage(HttpStatusCode.InternalServerError); ;
}
DataTable dataTable = new DataTable();
switch (type.Trim().ToUpper())
{
case "PRO_OUTPUT_TARGET":
//工序产出目标值
dataTable = new DashboardDAO().ExportToExcel(proj_code, product_code.Replace("null", null), process_code.Replace("null", null));
break;
case "EMP_OUTPUT_TARGET":
//员工产出目标值
dataTable = new DashboardDAO().OperatorTargetVauleToExcel(proj_code, product_code.Replace("null", null), process_code.Replace("null", null));
break;
case "EQUIPMENT_DATA":
case "BINDING_EQUIPMENT":
//工序&设备绑定
dataTable = new DashboardDAO().BindingEquipmentToExcel(type.Trim().ToUpper(), proj_code, product_code.Replace("null", null), process_code.Replace("null", null));
break;
case "BINDING_TEST_ITEM":
//工序&机台测试项绑定
dataTable = new DashboardDAO().BindingTestItemToExcel(proj_code, product_code.Replace("null", null), process_code.Replace("null", null));
break;
}
// 创建一个工作簿
IWorkbook workbook = new XSSFWorkbook();
// 创建一个工作表
ISheet sheet = workbook.CreateSheet("Sheet1");
// 创建表头行
IRow headerRow = sheet.CreateRow(0);
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.FillForegroundColor = IndexedColors.Green.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
for (int i = 0; i < dataTable.Columns.Count; i++)
{
//headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
ICell cell = headerRow.CreateCell(i);
cell.CellStyle = headerStyle;
cell.SetCellValue(dataTable.Columns[i].ColumnName);
}
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
// 填充数据行
for (int i = 0; i < dataTable.Rows.Count; i++)
{
IRow dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dataTable.Columns.Count; j++)
{
//dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
if (string.IsNullOrEmpty(dataTable.Rows[i][j].ToString()))
{
ICell cell = dataRow.CreateCell(j);
cell.CellStyle = cellStyle;
cell.SetCellValue(dataTable.Rows[i][j].ToString());
}
else
{
ICell cell = dataRow.CreateCell(j);
cell.CellStyle = headerStyle;
cell.SetCellValue(dataTable.Rows[i][j].ToString());
}
}
}
// 将工作簿转换为字节数组
using (MemoryStream stream = new MemoryStream())
{
workbook.Write(stream);
byte[] excelBytes = stream.ToArray();
// 创建一个 HttpResponseMessage 对象,并将 Excel 字节数组作为内容返回
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new ByteArrayContent(excelBytes);
response.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
response.Content.Headers.ContentDisposition.FileName = $@"{type.Trim().ToUpper()}.xlsx";
response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
return response;
}
}
后端接口:Excel上传
/// <summary>
/// 上传模板
/// </summary>
/// <returns></returns>
[HttpPost]
public Result UploadExcel()
{
string type = string.Empty;
var httpRequest = HttpContext.Current.Request;
if (httpRequest.Files.Count == 0)
{
return new Result(false) { ReturnMessage = "No file uploaded." };
}
var file = httpRequest.Files[0];
if (file == null || file.ContentLength == 0)
{
return new Result(false) { ReturnMessage = "No file uploaded." };
}
/*保存文件*/
//var fileName = Path.GetFileName(file.FileName);
//var filePath = Path.Combine(HttpContext.Current.Server.MapPath("~/your-upload-directory"), fileName);
//file.SaveAs(filePath);
// 处理上传的 Excel 文件
DataTable dataTable = new DataTable();
using (var stream = file.InputStream)
{
IWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(0);
for (int i = 0; i < headerRow.LastCellNum; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
dataTable.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
continue;
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.String)
{
dataRow[j] = cell.StringCellValue;
}
else if (cell.CellType == CellType.Numeric)
{
dataRow[j] = cell.NumericCellValue.ToString();
}
}
}
dataTable.Rows.Add(dataRow);
}
}
type = httpRequest.Form["type"].Trim();
bool status = false;
switch (type.Trim().ToUpper())
{
case "PRO_OUTPUT_TARGET":
//工序产出目标值
status = new DashboardDAO().UploadExcel(dataTable);
break;
case "EMP_OUTPUT_TARGET":
//员工产出目标值
status = new DashboardDAO().UploadOperatorTargetVauleExcel(dataTable);
break;
case "EQUIPMENT_DATA":
case "BINDING_EQUIPMENT":
//工序&设备绑定
status = new DashboardDAO().UploadBindingEquipmentExcel(type.Trim().ToUpper(),dataTable);
break;
case "BINDING_TEST_ITEM":
//工序&机台测试项绑定
status = new DashboardDAO().UploadBindingTestItemExcel(dataTable);
break;
}
//返回响应
Result result = new Result(status);
result.ReturnMessage = status ? "Excel file uploaded successfully." : "Excel file uploaded error!";
return result;
}
生成Excel带图表不做本地保存
/// <summary>
/// Excel图表不做本保存
/// </summary>
/// <param name="table"></param>
[HttpPost]
public void CreateExcelCharts3(List<string> emails)
{
// 设置LicenseContext属性
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelPackage package = new ExcelPackage();
// 创建一个工作表
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Sheet1");
// 创建行和单元格,并填充数据
sheet.Cells["A1"].Value = "Name";
sheet.Cells["B1"].Value = "Age";
sheet.Cells["A2"].Value = "John";
sheet.Cells["B2"].Value = 25;
sheet.Cells["A3"].Value = "Jane";
sheet.Cells["B3"].Value = 30;
// 创建柱状图
var chart = sheet.Drawings.AddChart("Chart1", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
// 设置图表位置
chart.SetPosition(4, 0, 5, 0);
// 设置图表大小
chart.SetSize(400, 300);
// 添加图表数据
var series = chart.Series.Add(sheet.Cells["B2:B3"], sheet.Cells["A2:A3"]);
// 配置图表样式
chart.Title.Text = "当天工序产出";
chart.Title.Font.Bold = true;
// 将图表右移一个单元格
chart.SetPosition(4, 4, 5, 4);
// 保存Excel文件1
//package.SaveAs(new FileInfo("output.xlsx"));
// 保存Excel文件2
//string filePath = @"D:\项目开发Develop\your_file_name.xlsx";
//File.WriteAllBytes(filePath, package.GetAsByteArray());
// 将Excel文件保存到内存流
using (MemoryStream stream = new MemoryStream())
{
package.SaveAs(stream);
stream.Position = 0;
List<StreamAttachment> attachment = new List<StreamAttachment>()
{
new StreamAttachment{
stream = stream,
name = "当天工序产出.xlsx",
mediaTyp = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}
};
EmailHelper.SendToEmail(emails, "看板指标预警", "Hi All: \n 当天工序产出现异常,详细数据,请查看附件!谢谢!", true, attachment, "Dashboard");
}
}
案例二:
使用EPPlus 创建Excel带Chart图表
CREATE TABLE #T_tableName
(
[PROJ_CODE] VARCHAR(512),
[PRODUCT_CODE] VARCHAR(512),
[PROCESS_ID] VARCHAR(512),
[SN] VARCHAR(512),
[TESTER] VARCHAR(512),
[SUM_INPUT_QTY] INT,
[SUM_OUTPUT_QTY] INT,
[SUM_FALL_QTY] VARCHAR(512),
[YIELD_RATE] VARCHAR(512),
[DOWN_TIME] INT,
[RUN_TIME] INT,
[REST_TIME] VARCHAR(512),
[TOTAL_TIME] INT,
[TIME_RATE] DECIMAL(10,2),
[THRESHOLD_VALUE] INT
);
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAD', 'LDU1_ADN', 'COC_LOADER1', '10-0027-00118', 'LOAD_UNLOAD_02', '1528', '1527', '1', '99.93', '459', '29', '0', '502', '5.78', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAD', 'LDU2_ADN', 'LENS_AA_SIOB', '10-0039-00154', 'LENSAA_036', '186', '186', '0', '100', '81', '383', '39', '502', '82.72', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAD', 'ROSA_ADN', 'LENS_ATTACH', '10-0061-00098', 'LENS_AA_12', '98', '98', '0', '100', '502', '0', '0', '502', '0', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAD', 'TOSA_ADN', 'SIOB_BOND_PIC', '10-0061-00088', 'FLIP_TOSA_AA_24', '32', '0', '32', '0', '414', '88', '0', '502', '17.53', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAD', 'MODULE_ADN', 'PRE_BI_HTOL', '80-0057-00003', 'PREBI_HTOL_03', '42', '38', '4', '90.48', '378', '124', '0', '502', '24.7', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU1_ADV2', 'COC_LOADER1', '10-0027-00118', 'LOAD_UNLOAD_02', '1499', '1499', '0', '100', '502', '0', '0', '502', '0', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU1_ADV2', 'COC_LOADER1', '10-0027-00128', 'LOAD_UNLOAD_10', '383', '383', '0', '100', '489', '0', '0', '502', '0', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU2_ADV2', 'LENS_AA_SIOB', '10-0039-00154', 'LENSAA_036', '26', '26', '0', '100', '458', '45', '0', '503', '8.95', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU2_ADV2', 'U_BLOCK_BOND', '', 'U_BLOCK_BOND_007', '851', '846', '5', '99.41', '459', '16', '0', '503', '3.18', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU2_ADV2', 'U_BLOCK_BOND', '10-0061-00081', 'U_BLOCK_BOND_02', '471', '471', '0', '100', '475', '15', '0', '503', '2.98', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU2_ADV2', 'U_BLOCK_BOND', '10-0061-00082', 'U_BLOCK_BOND_03', '23', '23', '0', '100', '500', '0', '0', '503', '0', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU2_ADV2', 'U_BLOCK_BOND', '10-0061-00100', 'U_BLOCK_BOND_04', '867', '866', '1', '99.88', '446', '44', '0', '503', '8.75', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU2_ADV2', 'U_BLOCK_BOND', '10-0061-00114', 'U_BLOCK_BOND_05', '670', '668', '2', '99.7', '359', '144', '0', '503', '28.63', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'LDU2_ADV2', 'U_BLOCK_BOND', '10-0061-00115', 'U_BLOCK_BOND_06', '509', '509', '0', '100', '480', '3', '0', '503', '0.6', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'ROSA_ADV2', 'TIA_SLC_BOND', '', 'PIC_TIA_SLC_TEC_BOND_003', '160', '160', '0', '100', '345', '5', '0', '504', '0.99', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'ROSA_ADV2', 'TIA_SLC_BOND', '', 'TIA_SLC_BOND_003', '319', '319', '0', '100', '476', '0', '0', '504', '0', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'ROSA_ADV2', 'PD_BOND', '', 'PIC_TIA_SLC_TEC_BOND_003', '160', '160', '0', '100', '499', '5', '0', '504', '0.99', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'ROSA_ADV2', 'PIC_ASSEMBLY', '10-0061-00106', 'SPLICING_011', '200', '200', '0', '100', '500', '0', '0', '504', '0', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00088', 'FLIP_TOSA_AA_24', '99', '13', '86', '13.13', '183', '256', '64', '504', '58.18', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00095', 'FLIP_TOSA_AA_33', '118', '9', '109', '7.63', '87', '355', '60', '504', '79.95', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00104', 'FLIP_TOSA_AA_36', '91', '88', '3', '96.7', '127', '370', '0', '504', '73.41', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00107', 'FLIP_TOSA_AA_38', '92', '92', '0', '100', '126', '375', '0', '504', '74.4', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00124', 'FLIP_TOSA_AA_46', '3', '2', '1', '66.67', '496', '8', '0', '504', '1.59', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00131', 'FLIP_TOSA_AA_49', '86', '81', '5', '94.19', '77', '368', '55', '504', '81.96', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00129', 'FLIP_TOSA_AA_50', '36', '34', '2', '94.44', '311', '167', '0', '504', '33.13', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00132', 'FLIP_TOSA_AA_51', '39', '37', '2', '94.87', '339', '163', '0', '504', '32.34', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00134', 'FLIP_TOSA_AA_52', '6', '6', '0', '100', '480', '24', '0', '504', '4.76', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00140', 'FLIP_TOSA_AA_60', '58', '54', '4', '93.1', '253', '249', '0', '504', '49.4', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'TOSA_ADV2', 'SIOB_BOND_PIC', '10-0061-00141', 'FLIP_TOSA_AA_61', '70', '65', '5', '92.86', '234', '267', '0', '504', '52.98', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'MODULE_ADV2_NEW', 'PRE_BI_HTOL', '', 'PREBI_HTOL_001', '83', '63', '20', '75.9', '65', '371', '65', '503', '84.7', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'MODULE_ADV2_NEW', 'PRE_BI_HTOL', '', 'PREBI_HTOL_002', '100', '77', '23', '77', '64', '369', '66', '503', '84.44', '60');
INSERT INTO #T_tableName ([PROJ_CODE], [PRODUCT_CODE], [PROCESS_ID], [SN], [TESTER], [SUM_INPUT_QTY], [SUM_OUTPUT_QTY], [SUM_FALL_QTY], [YIELD_RATE], [DOWN_TIME], [RUN_TIME], [REST_TIME], [TOTAL_TIME], [TIME_RATE], [THRESHOLD_VALUE]) VALUES ('NCCSAP', 'MODULE_ADV2_NEW', 'PRE_BI_HTOL', '', 'PREBI_HTOL_005', '92', '79', '13', '85.87', '64', '371', '65', '503', '84.7', '60');
SELECT * FROM #T_tableName
C#:
/// <summary>
/// 生成Excel附件
/// </summary>
/// <returns></returns>
public bool BoardWarningDataToExcelMail(string type, string title, List<string> emailaddress, DataSet dataSet)
{
bool state = true;
try
{
//设置LicenseContext属性
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelPackage package = new ExcelPackage();
for (int i = 0; i < dataSet.Tables.Count; i++)
{
DataTable table = dataSet.Tables[i];
//创建一个工作表
ExcelWorksheet sheet = package.Workbook.Worksheets.Add($@"Sheet{i}");
//批量填充
sheet.Cells["A1"].LoadFromDataTable(table, true);
//自适应宽度
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
//套用表格格式
var range = sheet.Cells[sheet.Dimension.Address];
var tableFormat = sheet.Tables.Add(range, $@"Table{i}");
tableFormat.TableStyle = OfficeOpenXml.Table.TableStyles.Medium2;
//chart
switch (type.ToUpper())
{
case "EQUIPMENT_DOWNTIME":
#region 设备60分钟未产出
sheet.Name = i != 0 ? "数据明细" : "设备60分钟未产出";
if (i != 0) { break; }
int d_TesterIndex = table.Columns.IndexOf("TESTER") + 1;
int d_DownTimeIndex = table.Columns.IndexOf("DOWN_TIME") + 1;
int d_Run_TimeIndex = table.Columns.IndexOf("RUN_TIME") + 1;
int d_TotalTimeIndex = table.Columns.IndexOf("TOTAL_TIME") + 1;
int d_ThresholdValueIndex = table.Columns.IndexOf("THRESHOLD_VALUE") + 1;
//创建柱状图
var d_chart = sheet.Drawings.AddChart("Chart1", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
//设置图表大小
d_chart.SetSize(1600, 400);
//添加图表数据
var d_series1 = d_chart.Series.Add(
sheet.Cells[2, d_DownTimeIndex, table.Rows.Count + 1, d_DownTimeIndex],
sheet.Cells[2, d_TesterIndex, table.Rows.Count + 1, d_TesterIndex]
);
d_series1.Header = "DOWN_TIME";
var d_series2 = d_chart.Series.Add(
sheet.Cells[2, d_Run_TimeIndex, table.Rows.Count + 1, d_Run_TimeIndex],
sheet.Cells[2, d_TesterIndex, table.Rows.Count + 1, d_TesterIndex]
);
d_series2.Header = "RUN_TIME";
var d_series3 = d_chart.Series.Add(
sheet.Cells[2, d_TotalTimeIndex, table.Rows.Count + 1, d_TotalTimeIndex],
sheet.Cells[2, d_TesterIndex, table.Rows.Count + 1, d_TesterIndex]
);
d_series3.Header = "TOTAL_TIME";
var d_series4 = d_chart.Series.Add(
sheet.Cells[2, d_ThresholdValueIndex, table.Rows.Count + 1, d_ThresholdValueIndex],
sheet.Cells[2, d_TesterIndex, table.Rows.Count + 1, d_TesterIndex]
);
d_series4.Header = "THRESHOLD_VALUE";
//配置图表样式
d_chart.Title.Text = sheet.Name;
d_chart.Title.Font.Bold = true;
//设置图例位置为顶部
d_chart.Legend.Position = eLegendPosition.Top;
//图表位置
d_chart.SetPosition(table.Rows.Count + 2, 0, 0, 0);
/*
//Chart图表样式 冲突影响 表格样式
//Area3dChartStyle8、AreaChartStyle8、StackedArea3dChartStyle8
//d_chart.StyleManager.SetChartStyle(ePresetChartStyle.StackedArea3dChartStyle8);
*/
#endregion
break;
default:
break;
}
}
#region 本地保存
string filePath = $@"D:\项目开发Develop\{title}.xlsx";
//File.WriteAllBytes(filePath, package.GetAsByteArray());
using (FileStream fileStream = new FileStream(filePath, FileMode.Create))
{
package.SaveAs(fileStream);
}
#endregion
//将Excel文件保存到内存流
using (MemoryStream stream = new MemoryStream())
{
package.SaveAs(stream);
stream.Position = 0;
List<StreamAttachment> attachment = new List<StreamAttachment>()
{
new StreamAttachment{
stream = stream,
name = $@"{title}.xlsx",
mediaTyp = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}
};
DataTable table = dataSet.Tables[0];
StringBuilder th = new StringBuilder();
for (int c = 0; c < table.Columns.Count; c++)
{
th.Append(string.Format(Msg.EmailTableTh, table.Columns[c]));
}
StringBuilder tabletr = new StringBuilder();
for (int r = 0; r < table.Rows.Count; r++)
{
StringBuilder tabletd = new StringBuilder();
for (int c = 0; c < table.Columns.Count; c++)
{
tabletd.Append(string.Format(Msg.EmailTableTd, table.Rows[r][table.Columns[c]]));
}
tabletr.Append(string.Format(Msg.EmailTableTr, tabletd));
}
//HTML
StringBuilder body = new StringBuilder();
body.Append(string.Format(Msg.Style, Msg.EmailStyle3));
body.Append(string.Format(Msg.EmailTitle, string.Format(Msg.WarningTfoot, title) + string.Format(Msg.LinkMsg, Msg.WarningContentMsg)));
body.Append(string.Format(Msg.EmailTable, string.Format(Msg.thead, string.Format(Msg.thead, th)), string.Format(Msg.tbody, tabletr)));
//Excel 为附件
EmailHelper.SendToEmail(emailaddress, string.Format(Msg.WarningHead, title), body.ToString(), true, attachment, "Dashboard");
}
}
catch (Exception e)
{
state = false;
throw new Exception(e.Message);
}
return state;
}
效果