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");
}
}
![[mysql]索引优化-2](https://img-blog.csdnimg.cn/c38ec557f8d241ab88c9d02a9c59b22e.png)