Excel追加 +设置样式做好了

wells.liu

2020-07-13 27ec4a74d3ed9aa8ce1ee8e5d0955596c67cb996

Excel追加 +设置样式做好了

 src/Bro.M071.DBManager/ExcelExportHelper.cs

@@ -4,6 +4,7 @@
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;

namespace Bro.M071.DBManager
@@ -85,7 +86,8 @@
                {
                    var dataTable = excelExportDto.WorksheetDataTable[worksheet];
                    ExcelWorksheet workSheet = package.Workbook.Worksheets.Add($"{worksheet}");
                    int startRowFrom = string.IsNullOrEmpty(worksheet) ? 1 : 3;  //开始的行
                    //int startRowFrom = string.IsNullOrEmpty(worksheet) ? 1 : 3;  //开始的行
                    int startRowFrom = 1;  //开始的行
                                                                                 //是否显示行编号
                    if (showSrNo)
                    {
@@ -100,19 +102,28 @@
                    }
                    //Add Content Into the Excel File
                    workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
                    // autofit width of cells with small content 
                    int columnIndex = 1;
                    foreach (DataColumn item in dataTable.Columns)

                    for (int columnIndex = 1; columnIndex <= dataTable.Columns.Count; columnIndex++)
                    {
                        // autofit width of cells with small content 
                        ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
                        int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
                        if (maxLength < 150)
                        {
                            workSheet.Column(columnIndex).AutoFit();
                        }
                        columnIndex++;
                        // 设置NG单元格样式
                        for (int rowIndex = 1; rowIndex <= dataTable.Rows.Count; rowIndex++)
                        {
                            if (columnCells[rowIndex, columnIndex].Text == "NG")
                            {
                                workSheet.Cells[rowIndex, columnIndex].Style.Font.Bold = true;//字体为粗体
                                workSheet.Cells[rowIndex, columnIndex].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                workSheet.Cells[rowIndex, columnIndex].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#E33E33"));// 设置填充样式
                    }
                    // format header - bold, yellow on black 
                        }
                    }
                    // format header - bold, yellow on black 设置表头样式
                    using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
                    {
                        r.Style.Font.Color.SetColor(System.Drawing.Color.White);
@@ -132,34 +143,128 @@
                        r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                        r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                    }
                    if (!string.IsNullOrEmpty(worksheet))
                    {
                        workSheet.Cells["A1"].Value = worksheet;
                        workSheet.Cells["A1"].Style.Font.Size = 20;
                        workSheet.InsertColumn(1, 1);
                        workSheet.InsertRow(1, 1);
                        workSheet.Column(1).Width = 5;
                    }
                    //将sheet写入表中
                    //if (!string.IsNullOrEmpty(worksheet))
                    //{
                    //    workSheet.Cells["A1"].Value = worksheet;
                    //    workSheet.Cells["A1"].Style.Font.Size = 20;
                    //    workSheet.InsertColumn(1, 1);
                    //    workSheet.InsertRow(1, 1);
                    //    workSheet.Column(1).Width = 5;
                    //}
                }
                result = package.GetAsByteArray();
            }
            return result;
        }

        ///// <summary>
        ///// 导出Excel
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="data"></param>
        ///// <param name="heading"></param>
        ///// <param name="isShowSlNo"></param>
        ///// <param name="columnsToTake"></param>
        ///// <returns></returns>
        //public static byte[] ExportExcel<T>(List<T> data, string heading = "", bool isShowSlNo = false, params string[] columnsToTake)
        //{
        //    ExcelExportSet excelExport = new ExcelExportSet();
        //    excelExport.
        //    return ExportExcel(ListToDataTable(data), heading, isShowSlNo, columnsToTake);
        //}
        /// <summary>
        /// 新建 或 追加 写入Excel
        /// </summary>
        /// <param name="excelExportDto"></param>
        /// <param name="fileName">要写入的文件名(全路径名)</param>
        /// <param name="showSrNo"></param>
        /// <returns></returns>
        public static byte[] CreateOrAppendExcel(ExcelExportSet excelExportDto, string fileName, bool showSrNo = false)
        {
            byte[] result = null;
            bool isExist = File.Exists(fileName);
            ExcelExportSet newExcelSet = new ExcelExportSet();

            if (isExist)
            {
                var oldWorksheetDataTable = WorksheetToTable(fileName, excelExportDto.Worksheets);
                newExcelSet.Worksheets = excelExportDto.Worksheets;
                newExcelSet.WorksheetColumns = excelExportDto.WorksheetColumns;

                foreach (var sheet in excelExportDto.Worksheets)
                {
                    var oldTable = oldWorksheetDataTable[sheet];
                    newExcelSet.WorksheetDataTable[sheet] = oldTable;
                    if (excelExportDto.WorksheetDataTable[sheet].Rows.Count > 0)
                    {
                        //合并两个 datatable
                        newExcelSet.WorksheetDataTable[sheet].Merge(excelExportDto.WorksheetDataTable[sheet]);
                    }
                }
            }
            else
            {
                newExcelSet = excelExportDto;
            }

            result = ExportExcel(newExcelSet, showSrNo);
            return result;
        }

        /// <summary>
        ///将指定的Excel的文件转换成DataTable （Excel的指定sheet）
        /// </summary>
        /// <param name="fullFielPath">文件的绝对路径</param>
        /// <returns></returns>
        public static Dictionary<string, DataTable> WorksheetToTable(string fullFielPath, List<string> worksheets)
        {
            try
            {
                var resultWorksheetDataTable = new Dictionary<string, DataTable>();
                FileInfo existingFile = new FileInfo(fullFielPath);
                ExcelPackage package = new ExcelPackage(existingFile);
                foreach (var sheet in worksheets)
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[sheet];//选定 指定页
                    var dataTable = WorksheetToTable(worksheet);
                    resultWorksheetDataTable[sheet] = dataTable;
                }
                return resultWorksheetDataTable;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;

            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
            for (int i = 1; i <= rows; i++)
            {
                if (i > 1)
                    dr = dt.Rows.Add();

                for (int j = 1; j <= cols; j++)
                {
                    //默认将第一行设置为datatable的标题
                    if (i == 1)
                        dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
                    //剩下的写入datatable
                    else
                        dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
                }
            }
            return dt;
        }
        private static string GetString(object obj)
        {
            try
            {
                return obj.ToString();
            }
            catch (Exception)
            {
                return "";
            }
        }

    }
}

 src/Bro.M071.Process/M071Process.cs

@@ -561,14 +561,15 @@
                excelExportDto.WorksheetDataTable[excelExportDto.Worksheets[0]] = ExcelExportHelper.ListToDataTable(measurementUnitResultAndKeyUnitDataSet.KeyUnitDataList, keyUnitColumns);
                excelExportDto.WorksheetDataTable[excelExportDto.Worksheets[1]] = ExcelExportHelper.ListToDataTable(measurementUnitResultAndKeyUnitDataSet.MeasurementUnitResultList, measurementUnitResultColumns); ;

                byte[] filecontent = ExcelExportHelper.ExportExcel(excelExportDto, false);
                string dir = Path.Combine(Config.LogPath, DateTime.Now.ToString("yyyyMMdd"));
                if (!Directory.Exists(dir))
                {
                    Directory.CreateDirectory(dir);
                }
                var fileName = Path.Combine(dir, $"{measurementUnitResultAndKeyUnitDataSet.ProductionMeasurementRecord.ProductionBarcode}_{DateTime.Now.ToString("HHmmss")}.xlsx");

                FileStream fs = new FileStream(Path.Combine(dir, $"{measurementUnitResultAndKeyUnitDataSet.ProductionMeasurementRecord.ProductionBarcode}_{DateTime.Now.ToString("HHmmss")}.xlsx"), FileMode.Create, FileAccess.Write);
                byte[] filecontent = ExcelExportHelper.CreateOrAppendExcel(excelExportDto, fileName);
                FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
                fs.Write(filecontent, 0, filecontent.Length);
                fs.Flush();
                fs.Close();

 src/ExcelTest/Form1.cs

@@ -23,7 +23,7 @@
        private void button1_Click(object sender, EventArgs e)
        {
            SaveExcel();
            SaveTxt();
            //SaveTxt();
        }

        public async void SaveExcel()
@@ -50,7 +50,7 @@
                    measurementUnitResult.MeasurementName = "测试" + i;
                    measurementUnitResult.MeasurementType = "Slant";
                    measurementUnitResult.MeasurementValue = (i + 10).ToString();
                    measurementUnitResult.MeasurementResult = "OK";
                    measurementUnitResult.MeasurementResult = (i % 2) == 1? "OK":"NG";

                    measurementUnitResultAndKeyUnitDataSet.MeasurementUnitResultList.Add(measurementUnitResult);
                }
@@ -77,14 +77,15 @@
                excelExportDto.WorksheetDataTable[excelExportDto.Worksheets[0]] = ExcelExportHelper.ListToDataTable(measurementUnitResultAndKeyUnitDataSet.KeyUnitDataList, keyUnitColumns);
                excelExportDto.WorksheetDataTable[excelExportDto.Worksheets[1]] = ExcelExportHelper.ListToDataTable(measurementUnitResultAndKeyUnitDataSet.MeasurementUnitResultList, measurementUnitResultColumns); ;

                byte[] filecontent = ExcelExportHelper.ExportExcel(excelExportDto, false);
               
                string dir = Path.Combine(@"D:\PROJECTS\M071", DateTime.Now.ToString("yyyyMMdd"));
                if (!Directory.Exists(dir))
                {
                    Directory.CreateDirectory(dir);
                }

                FileStream fs = new FileStream(Path.Combine(dir, $"test_133535.xlsx"), FileMode.Append, FileAccess.Write);
                var fileName = Path.Combine(dir, $"test_133536.xlsx");
                byte[] filecontent = ExcelExportHelper.CreateOrAppendExcel(excelExportDto, fileName);
                FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
                fs.Write(filecontent, 0, filecontent.Length);
                fs.Flush();
                fs.Close();