Excel下载、Excel上传，邮件发送Excel并带Char图表

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>&nbsp;
                    <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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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();
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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..');
                    }
                });
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

后端接口：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;
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

后端接口：Excel上传

        /// 

        /// 上传模板
        /// 
        /// 
        [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;
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

生成Excel带图表不做本地保存

        /// 

        /// Excel图表不做本保存
        /// 
        /// 
        [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");
            }
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60