From 27ec4a74d3ed9aa8ce1ee8e5d0955596c67cb996 Mon Sep 17 00:00:00 2001
From: wells.liu <wells.liu@broconcentric.com>
Date: 星期一, 13 七月 2020 18:36:39 +0800
Subject: [PATCH] Excel追加 +设置样式做好了
---
src/Bro.M071.Process/M071Process.cs | 5
src/ExcelTest/Form1.cs | 11 +-
src/Bro.M071.DBManager/ExcelExportHelper.cs | 165 +++++++++++++++++++++++++++++++++-------
3 files changed, 144 insertions(+), 37 deletions(-)
diff --git a/src/Bro.M071.DBManager/ExcelExportHelper.cs b/src/Bro.M071.DBManager/ExcelExportHelper.cs
index 17cadb7..ce6958e 100644
--- a/src/Bro.M071.DBManager/ExcelExportHelper.cs
+++ b/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,8 +86,9 @@
{
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)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
@@ -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;
- }
+ //灏唖heet鍐欏叆琛ㄤ腑
+ //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 锛圗xcel鐨勬寚瀹歴heet锛�
+ /// </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>
+ /// 灏唚orksheet杞垚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));
+ //鍓╀笅鐨勫啓鍏atatable
+ 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 "";
+ }
+ }
+
}
}
diff --git a/src/Bro.M071.Process/M071Process.cs b/src/Bro.M071.Process/M071Process.cs
index b0b9ecf..5090d47 100644
--- a/src/Bro.M071.Process/M071Process.cs
+++ b/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();
diff --git a/src/ExcelTest/Form1.cs b/src/ExcelTest/Form1.cs
index 2b45114..ddb97c6 100644
--- a/src/ExcelTest/Form1.cs
+++ b/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();
--
Gitblit v1.8.0