C# ExcelHelper 写入、删标签页、替换
using Microsoft.Office.Interop.Excel; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Reflection; using System.Runtime.InteropServices; using System.Text; namespace Platform { public class ExcelHelper { /// <summary> /// 将数据表写入Excel,不含表头，从指定的位置开始写 /// </summary> /// <param name="excelPath">excel文件路径</param> /// <param name="dts">数据表列表</param> /// <param name="startPosition">开始位置</param> public void ExportDts2ServalExcelSheets(string excelPath, List<System.Data.DataTable> dts, List<string> startPositions) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; //输出 for (int sheetIndex = 1; sheetIndex <= shs.Count; sheetIndex++) { _Worksheet _wsh = (Microsoft.Office.Interop.Excel.Worksheet)shs.get_Item(sheetIndex); int indexTab = dts.FindIndex(delegate (System.Data.DataTable d) { return d.TableName == _wsh.Name; }); if (indexTab < 0) continue; _wsh.Activate(); //输出内容 System.Data.DataTable dt = dts[indexTab]; if (dt.Rows.Count > 0) { Range r = _wsh.get_Range(startPositions[indexTab], Missing.Value); r = r.get_Resize(dt.Rows.Count, dt.Columns.Count); object[,] cellData = new object[dt.Rows.Count, dt.Columns.Count]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { for (int iCol = 0; iCol < dt.Columns.Count; iCol++) { cellData[iRow, iCol] = dt.Rows[iRow][iCol].ToString(); } } r.set_Value(Missing.Value, cellData); } _wbk.Save();//保存 } _wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null; wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 } /// <summary> /// 将Excel中每个Sheet页的A1内容，其中的oldValue替换成newValue /// </summary> /// <param name="excelPath"></param> /// <param name="oldValue"></param> /// <param name="newValue"></param> public void ModifyExcelSheetTableHead(string excelPath, string oldValue, string newValue) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; //输出 for (int i = 1; i <= shs.Count; i++) { _Worksheet _wsh = (Microsoft.Office.Interop.Excel.Worksheet)shs.get_Item(i); Range r = _wsh.get_Range("A1", Missing.Value); if (r.Text != "") { string wholeSentence = r.Value.ToString(); wholeSentence = r.Value.Replace(oldValue, newValue); r.set_Value(Missing.Value, wholeSentence); } _wbk.Save();//保存 } _wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null; wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 } /// <summary> /// 将Excel中每个Sheet页的页名称，如果不含该指定的名称，则删除 /// </summary> /// <param name="excelPath"></param> /// <param name="oldValue"></param> /// <param name="newValue"></param> public void RemoveExcelSheetWithoutCityName(string excelPath,string thisName,string exceptionSheetName) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; app.DisplayAlerts = false; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; for (int i = shs.Count; i >=1 ; i--) { if (!shs.get_Item(i).Name.Contains(thisName)&& shs.get_Item(i).Name!=exceptionSheetName)
{ shs.get_Item(i).Delete(); } } _wbk.Save(); _wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null; wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 } /// <summary> /// 将数据表写入Excel文件的不同新创建的Sheet页,含表头 /// </summary> public void ExportDts2ServalExcelSheets(string excelPath, List<System.Data.DataTable> dts) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; //输出 for (int i = 1; i <= shs.Count; i++) { _Worksheet _wsh = shs.get_Item(i); System.Data.DataTable dt = dts.Find(delegate (System.Data.DataTable d) { return d.TableName == _wsh.Name; }); if (dt == null) continue; _wsh.Activate(); ////输出表头 object[] data = new object[dt.Columns.Count]; Range r = _wsh.get_Range("A1", Missing.Value); Range newRange = r.get_Resize(1, dt.Columns.Count);//表头，所以仅有一行 newRange.set_Value(Type.Missing, GetColumnObjs(dt.Columns)); //输出内容 if (dt.Rows.Count > 0) { r = _wsh.get_Range("A2", Missing.Value); r = r.get_Resize(dt.Rows.Count, dt.Columns.Count); object[,] cellData = new object[dt.Rows.Count, dt.Columns.Count]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { for (int iCol = 0; iCol < dt.Columns.Count; iCol++) { cellData[iRow, iCol] = dt.Rows[iRow][iCol].ToString(); } } r.set_Value(Missing.Value, cellData); } _wbk.Save();//保存 } _wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null; wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 } private string Object2String(object obj) { try { string result = obj.ToString(); return result; } catch { return ""; } } private DateTime Object2Date(object obj) { if (obj is null) return DateTime.Parse("2050-01-01"); try { if (DateTime.TryParse(obj.ToString(), out DateTime dt)) return dt; else return DateTime.Parse("2050-01-01"); } catch { return DateTime.Parse("2050-01-01"); } } public float Object2Float(object obj) { if (obj is null) return 0; if (float.TryParse(obj.ToString(), out float result)) return result; else return 0; } private int Object2Int(object obj) { if (int.TryParse(obj.ToString(), out int result)) return result; else return 0; } private String GetExcelColumnName(int i) { List<string> list = new List<string>() { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM", "AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ", "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB","CC","CD","CE","CF","CG", "CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT","CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD", "DE","DF","DG","DH","DI","DJ","DK","DL","DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ","EA", "EB","EC","ED","EE","EF","EG","EH","EI","EJ","EK","EL","EM","EN","EO","EP","EQ","ER","ES","ET","EU","EV","EW","EX","EY","EZ" }; return list[i - 1]; } /// <summary> /// 得到一个列的所有列名 /// </summary> private object[] GetColumnObjs(System.Data.DataColumnCollection dc) { object[] array = new object[dc.Count]; for (int i = 0; i < array.Length; i++) { DataColumn dataColumn = dc[i]; array[i] = dataColumn.ColumnName; } return array; } /// <summary> /// 关闭Excel进程 /// </summary> public class KeyMyExcelProcess { [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); public static void Kill(Microsoft.Office.Interop.Excel.Application excel) { try { IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 int k = 0; GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 p.Kill(); //关闭进程k } catch (System.Exception ex) { throw ex; } } } } }
using Microsoft.Office.Interop.Excel;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Reflection;using System.Runtime.InteropServices;using System.Text;
namespace 信息统计平台配套工具{ public class ExcelHelper { /// <summary> /// 将数据表写入Excel,不含表头，从指定的位置开始写 /// </summary> /// <param name="excelPath">excel文件路径</param> /// <param name="dts">数据表列表</param> /// <param name="startPosition">开始位置</param> public void ExportDts2ServalExcelSheets(string excelPath, List<System.Data.DataTable> dts, List<string> startPositions) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; //输出
for (int sheetIndex = 1; sheetIndex <= shs.Count; sheetIndex++) { _Worksheet _wsh = (Microsoft.Office.Interop.Excel.Worksheet)shs.get_Item(sheetIndex); int indexTab = dts.FindIndex(delegate (System.Data.DataTable d) { return d.TableName == _wsh.Name; }); if (indexTab < 0) continue;
_wsh.Activate(); //输出内容 System.Data.DataTable dt = dts[indexTab]; if (dt.Rows.Count > 0) { Range r = _wsh.get_Range(startPositions[indexTab], Missing.Value); r = r.get_Resize(dt.Rows.Count, dt.Columns.Count); object[,] cellData = new object[dt.Rows.Count, dt.Columns.Count]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { for (int iCol = 0; iCol < dt.Columns.Count; iCol++) { cellData[iRow, iCol] = dt.Rows[iRow][iCol].ToString(); } } r.set_Value(Missing.Value, cellData); } _wbk.Save();//保存 }
_wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null;
wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 }
/// <summary> /// 将Excel中每个Sheet页的A1内容，其中的oldValue替换成newValue /// </summary> /// <param name="excelPath"></param> /// <param name="oldValue"></param> /// <param name="newValue"></param> public void ModifyExcelSheetTableHead(string excelPath, string oldValue, string newValue) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; //输出
for (int i = 1; i <= shs.Count; i++) { _Worksheet _wsh = (Microsoft.Office.Interop.Excel.Worksheet)shs.get_Item(i); Range r = _wsh.get_Range("A1", Missing.Value); if (r.Text != "") { string wholeSentence = r.Value.ToString(); wholeSentence = r.Value.Replace(oldValue, newValue); r.set_Value(Missing.Value, wholeSentence); } _wbk.Save();//保存 }
_wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null;
wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 }
/// <summary> /// 将Excel中每个Sheet页的页名称，如果不含该城市名，则删除 /// </summary> /// <param name="excelPath"></param> /// <param name="oldValue"></param> /// <param name="newValue"></param> public void RemoveExcelSheetWithoutCityName(string excelPath,string cityName,string exceptionSheetName) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; app.DisplayAlerts = false; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; for (int i = shs.Count; i >=1 ; i--) { if (!shs.get_Item(i).Name.Contains(cityName)&& shs.get_Item(i).Name!=exceptionSheetName) { shs.get_Item(i).Delete(); } } _wbk.Save(); _wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null;
wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 }
/// <summary> /// 将数据表写入Excel文件的不同新创建的Sheet页,含表头 /// </summary> public void ExportDts2ServalExcelSheets(string excelPath, List<System.Data.DataTable> dts) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets; //输出
for (int i = 1; i <= shs.Count; i++) { _Worksheet _wsh = shs.get_Item(i); System.Data.DataTable dt = dts.Find(delegate (System.Data.DataTable d) { return d.TableName == _wsh.Name; }); if (dt == null) continue;
_wsh.Activate(); ////输出表头 object[] data = new object[dt.Columns.Count]; Range r = _wsh.get_Range("A1", Missing.Value); Range newRange = r.get_Resize(1, dt.Columns.Count);//表头，所以仅有一行 newRange.set_Value(Type.Missing, GetColumnObjs(dt.Columns));
//输出内容 if (dt.Rows.Count > 0) { r = _wsh.get_Range("A2", Missing.Value); r = r.get_Resize(dt.Rows.Count, dt.Columns.Count); object[,] cellData = new object[dt.Rows.Count, dt.Columns.Count]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { for (int iCol = 0; iCol < dt.Columns.Count; iCol++) { cellData[iRow, iCol] = dt.Rows[iRow][iCol].ToString(); } } r.set_Value(Missing.Value, cellData); } _wbk.Save();//保存 }
_wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null;
wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 } //（2022年2季度） YYYY年MM月
/// <summary> /// 将数据表写入Excel文件的不同新创建的Sheet页 /// </summary> public void ExcelMark(string excelPath) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Workbooks wbks = app.Workbooks; object missing = System.Reflection.Missing.Value; _Workbook _wbk = wbks.Open(excelPath, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); app.Visible = true; Sheets shs = _wbk.Sheets;
//输出 for (int sheetIndex = 1; sheetIndex <= shs.Count; sheetIndex++) { _Worksheet _wsh = (Microsoft.Office.Interop.Excel.Worksheet)shs.get_Item(sheetIndex); _wsh.Activate(); switch (_wsh.Name) { //lightpink turquoise violet lightgreen gold deepskyblue orange yellow pulm tan orange
case "提交情况": for (int i = 1; i < _wsh.UsedRange.Rows.Count; i++) { for (int j = 1; j <= _wsh.UsedRange.Columns.Count; j++) { string info = Object2String(_wsh.Cells[j][i + 1].Text); if (info == "未提交") { _wsh.Range[GetExcelColumnName(j) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.HotPink); } } } _wbk.Save();//保存 break; case "附件1表1项目": for (int i = 1; i < _wsh.UsedRange.Rows.Count; i++) { //1.1、【项目名称】为空，或包含引号、分号、问号、斜杠等不合理字符 string xiangmumingcheng = Object2String(_wsh.Cells[9][i + 1].Text); if (xiangmumingcheng.Contains("\"") || xiangmumingcheng.Contains(";") || xiangmumingcheng.Contains("\\") || xiangmumingcheng.Contains("?") || xiangmumingcheng == "") { _wsh.Range[GetExcelColumnName(9) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.HotPink); } //1.2、【项目投资额】>一亿 * 【所含单位工程数】 或 【项目投资额】<【所含单位工程数】 float zongtouzie = Object2Float(_wsh.Cells[12][i + 1].Text); int danweigongchengshu = Object2Int(_wsh.Cells[14][i + 1].Text); if (zongtouzie > danweigongchengshu * 10000 || zongtouzie < danweigongchengshu) { _wsh.Range[GetExcelColumnName(12) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Turquoise); _wsh.Range[GetExcelColumnName(14) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Turquoise); } //1.3、【所含单位工程数】为0 if (danweigongchengshu == 0) { _wsh.Range[GetExcelColumnName(14) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gold); } } _wbk.Save();//保存 break; case "附件1表1标段": for (int i = 1; i < _wsh.UsedRange.Rows.Count; i++) { //2.1、【标段名称】为空，或包含引号、分号、问号、斜杠等不合理字符 string biaoduanmingcheng = Object2String(_wsh.Cells[14][i + 1].Text); if (biaoduanmingcheng.Contains("\"") || biaoduanmingcheng.Contains(";") || biaoduanmingcheng.Contains("\\") || biaoduanmingcheng.Contains("?") || biaoduanmingcheng == "") { _wsh.Range[GetExcelColumnName(14) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.HotPink); } //2.2、【标段合同价款】>一亿 * 【单位工程数】 或 <10万*单位工程数 float hetongjiakuan = Object2Float(_wsh.Cells[16][i + 1].Text); int danweigongchengshu = Object2Int(_wsh.Cells[29][i + 1].Text); if (hetongjiakuan > danweigongchengshu * 10000 || hetongjiakuan < danweigongchengshu * 10) { _wsh.Range[GetExcelColumnName(16) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Turquoise); _wsh.Range[GetExcelColumnName(29) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Turquoise); } //2.3、【所含单位工程数】为空 if (danweigongchengshu == 0) { _wsh.Range[GetExcelColumnName(29) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gold); }
//2.4、【标段开工日期】比【标段竣工日期】晚，或日期空缺，异常 DateTime kaigongriqi = Object2Date(_wsh.Cells[17][i + 1].Value); DateTime jungongriqi = Object2Date(_wsh.Cells[19][i + 1].Text); if (kaigongriqi > jungongriqi || kaigongriqi < DateTime.Parse("1990-01-01") || kaigongriqi > DateTime.Parse("2050-01-01")) { _wsh.Range[GetExcelColumnName(17) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); } if (kaigongriqi > jungongriqi || jungongriqi < DateTime.Parse("1990-01-01") || jungongriqi > DateTime.Parse("2050-01-01")) { _wsh.Range[GetExcelColumnName(19) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen); } } _wbk.Save();//保存 break; case "附件1表1单位工程": for (int i = 1; i < _wsh.UsedRange.Rows.Count; i++) { //3.1、【单位工程名称】为空，或包含引号、分号、问号、斜杠等不合理字符 string danweigongchengmingcheng = Object2String(_wsh.Cells[30][i + 1].Text); if (danweigongchengmingcheng.Contains("\"") || danweigongchengmingcheng.Contains(";") || danweigongchengmingcheng.Contains("\\") || danweigongchengmingcheng.Contains("?") || danweigongchengmingcheng == "") { _wsh.Range[GetExcelColumnName(30) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.HotPink); } //3.2、【单位工程开工日期】【单位工程竣工日期】空缺，异常，或单位工程的【开工】比【竣工】晚
DateTime kaigongriqi = Object2Date(_wsh.Cells[44][i + 1].Value); DateTime jungongriqi = Object2Date(_wsh.Cells[45][i + 1].Value); if (kaigongriqi > jungongriqi || kaigongriqi < DateTime.Parse("1990-01-01") || kaigongriqi > DateTime.Parse("2050-01-01")) { _wsh.Range[GetExcelColumnName(44) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Salmon); } if (kaigongriqi > jungongriqi || jungongriqi < DateTime.Parse("1990-01-01") || jungongriqi > DateTime.Parse("2050-01-01")) { _wsh.Range[GetExcelColumnName(45) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Salmon); }
//3.3、【单位工程开工日期】比【标段开工日期】早 DateTime biaoduankaigongriqi = Object2Date(_wsh.Cells[17][i + 1].Value); if (biaoduankaigongriqi > kaigongriqi) { _wsh.Range[GetExcelColumnName(17) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.PaleGreen); }
//3.4、【单位工程竣工日期】比【标段竣工日期】晚 DateTime biaoduanjungongriqi = Object2Date(_wsh.Cells[19][i + 1].Value); if (biaoduanjungongriqi < jungongriqi) { _wsh.Range[GetExcelColumnName(19) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LimeGreen); } //3.5、【建筑面积】大于30万平方米 或 （ 【建筑面积】>200 and 单位工程名称中包含“门”） float jianzhumianji = Object2Float(_wsh.Cells[32][i + 1].Text); if (jianzhumianji > 30 ||(danweigongchengmingcheng.Contains("门") && jianzhumianji>0.02)) { _wsh.Range[GetExcelColumnName(32) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Turquoise); } //3.6、单位工程的【形象进度】为“已竣工”时，【单位工程竣工日期】不应为空，或为默认的“2050-01-01” string xingxiangjindu = Object2String(_wsh.Cells[35][i + 1].Text); if (xingxiangjindu == "已竣工" && jungongriqi == DateTime.Parse("2050-01-01")) { _wsh.Range[GetExcelColumnName(35) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SandyBrown); _wsh.Range[GetExcelColumnName(45) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SandyBrown); }
//好像逻辑不合适 ////3.7、【形象进度】不是“已完工”或“已竣工”时，【是否建立信用档案】已填写为“是” //string shifousheliyongjiuxingbiaopai = Object2String(_wsh.Cells[42][i + 1].Text); //string shifoushelijianlixinyongdangan = Object2String(_wsh.Cells[43][i + 1].Text); //if (!xingxiangjindu.Contains("已完工") || !xingxiangjindu.Contains("已竣工")) //{ // if (shifoushelijianlixinyongdangan == "是") // { // _wsh.Range[GetExcelColumnName(35) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Violet); // _wsh.Range[GetExcelColumnName(43) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Violet); // } //} //太复杂还没有处理 ////3.8、标段内各单位工程的【形象进度】均为“已竣工”时，【标段竣工日期】不应为空，或为默认的“2050 - 01 - 01” //if (xingxiangjindu == "已竣工" && biaoduanjungongriqi == DateTime.Parse("2050-01-01")) //{ // _wsh.Range[GetExcelColumnName(35) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Violet); // _wsh.Range[GetExcelColumnName(19) + (i + 1).ToString()].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Violet); //} } _wbk.Save();//保存 break;
default: break; }
} _wbk.Save();//保存 _wbk.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk); //通知COM控件，释放变量 _wbk = null;
wbks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbks);//所有用过的变量都要释放 wbks = null; app.Quit(); KeyMyExcelProcess.Kill(app); System.Runtime.InteropServices.Marshal.ReleaseComObject(app);//一个也不能少 app = null; System.GC.Collect(); //回收资源清内存 }
private string Object2String(object obj) { try { string result = obj.ToString(); return result; } catch { return ""; } }
private DateTime Object2Date(object obj) { if (obj is null) return DateTime.Parse("2050-01-01");
try { if (DateTime.TryParse(obj.ToString(), out DateTime dt)) return dt; else return DateTime.Parse("2050-01-01"); } catch { return DateTime.Parse("2050-01-01"); } }
public float Object2Float(object obj) { if (obj is null) return 0;
if (float.TryParse(obj.ToString(), out float result)) return result; else return 0; } private int Object2Int(object obj) { if (int.TryParse(obj.ToString(), out int result)) return result; else return 0; }
private String GetExcelColumnName(int i) { List<string> list = new List<string>() { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM", "AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ", "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB","CC","CD","CE","CF","CG", "CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT","CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD", "DE","DF","DG","DH","DI","DJ","DK","DL","DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ","EA", "EB","EC","ED","EE","EF","EG","EH","EI","EJ","EK","EL","EM","EN","EO","EP","EQ","ER","ES","ET","EU","EV","EW","EX","EY","EZ" }; return list[i - 1]; } /// <summary> /// 得到一个列的所有列名 /// </summary> private object[] GetColumnObjs(System.Data.DataColumnCollection dc) { object[] array = new object[dc.Count]; for (int i = 0; i < array.Length; i++) { DataColumn dataColumn = dc[i]; array[i] = dataColumn.ColumnName; } return array; }
/// <summary> /// 关闭Excel进程 /// </summary> public class KeyMyExcelProcess { [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); public static void Kill(Microsoft.Office.Interop.Excel.Application excel) { try { IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 int k = 0; GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 p.Kill(); //关闭进程k } catch (System.Exception ex) { throw ex; } } } }}
