CodematicDemo Common部分的源码 DataTOExcel.cs

DataTOExcel.cs 的源码

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Collections; using System.IO; using System.Diagnostics; using Excel; using COM.Excel; using System.Runtime.InteropServices; namespace Gauss.Common { public class DataToExcel { // Fields private DateTime afterTime; private DateTime beforeTime; private int titleColorindex; public DataToExcel() { this.titleColorindex = 15; } private void ClearFile(string FilePath) { string[] textArray1 = Directory.GetFiles(FilePath); if (textArray1.Length > 10) { for (int num1 = 0; num1 < 10; num1++) { try { File.Delete(textArray1[num1]); } catch { } } } } public void CreateExcel() { Application application1 = new ApplicationClass(); application1.Application.Workbooks.Add(true); application1.Cells[1, 1] = "/u7b2c1/u884c/u7b2c1/u5217"; application1.Cells[1, 2] = "/u7b2c1/u884c/u7b2c2/u5217"; application1.Cells[2, 1] = "/u7b2c2/u884c/u7b2c1/u5217"; application1.Cells[2, 2] = "/u7b2c2/u884c/u7b2c2/u5217"; application1.Cells[3, 1] = "/u7b2c3/u884c/u7b2c1/u5217"; application1.Cells[3, 2] = "/u7b2c3/u884c/u7b2c2/u5217"; application1.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); application1.Visible = true; } public string DataExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList) { cExcelFile file1 = new cExcelFile(); this.ClearFile(FilePath); string text1 = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls"; file1.CreateFile(FilePath + text1); file1.PrintGridLines = true; cExcelFile.MarginTypes types1 = cExcelFile.MarginTypes.xlsTopMargin; cExcelFile.MarginTypes types2 = cExcelFile.MarginTypes.xlsLeftMargin; cExcelFile.MarginTypes types3 = cExcelFile.MarginTypes.xlsRightMargin; cExcelFile.MarginTypes types4 = cExcelFile.MarginTypes.xlsBottomMargin; double num1 = 1.5; file1.SetMargin(ref types1, ref num1); file1.SetMargin(ref types2, ref num1); file1.SetMargin(ref types3, ref num1); file1.SetMargin(ref types4, ref num1); cExcelFile.FontFormatting formatting1 = cExcelFile.FontFormatting.xlsNoFormat; //string text2 = "/u5b8b/u4f53"; string text2 = "Arial"; short num2 = 9; file1.SetFont(ref text2, ref num2, ref formatting1); byte num3 = 1; byte num4 = 12; short num5 = 12; file1.SetColumnWidth(ref num3, ref num4, ref num5); string text3 = "/u9875/u7709"; string text4 = "/u9875/u811a"; file1.SetHeader(ref text3); file1.SetFooter(ref text4); cExcelFile.ValueTypes types5 = cExcelFile.ValueTypes.xlsText; cExcelFile.CellFont font1 = cExcelFile.CellFont.xlsFont0; cExcelFile.CellAlignment alignment1 = cExcelFile.CellAlignment.xlsCentreAlign; cExcelFile.CellHiddenLocked locked1 = cExcelFile.CellHiddenLocked.xlsNormal; int num6 = 1; int num7 = 1; int num8 = 0; foreach (DataColumn column1 in dt.Columns) { num8++; string text5 = column1.ColumnName.Trim(); object obj1 = text5; IDictionaryEnumerator enumerator1 = nameList.GetEnumerator(); while (enumerator1.MoveNext()) { if (enumerator1.Key.ToString().Trim() == text5) { obj1 = enumerator1.Value; } } file1.WriteValue(ref types5, ref font1, ref alignment1, ref locked1, ref num7, ref num8, ref obj1, ref num6); } foreach (DataRow row1 in dt.Rows) { num7++; num8 = 0; foreach (DataColumn column2 in dt.Columns) { num8++; if (column2.DataType == Type.GetType("System.DateTime")) { object obj2 = Convert.ToDateTime(row1[column2.ColumnName].ToString()).ToString("yyyy-MM-dd"); file1.WriteValue(ref types5, ref font1, ref alignment1, ref locked1, ref num7, ref num8, ref obj2, ref num6); } else { object obj3 = row1[column2.ColumnName].ToString(); file1.WriteValue(ref types5, ref font1, ref alignment1, ref locked1, ref num7, ref num8, ref obj3, ref num6); } } } file1.CloseFile(); return text1; } public void KillExcelProcess() { Process[] processArray1 = Process.GetProcessesByName("Excel"); foreach (Process process1 in processArray1) { DateTime time1 = process1.StartTime; if ((time1 > this.beforeTime) && (time1 < this.afterTime)) { process1.Kill(); } } } public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath) { this.beforeTime = DateTime.Now; int num1 = 4; int num2 = 1; Application application1 = new ApplicationClass(); _Workbook workbook1 = application1.Workbooks.Add(true); _Worksheet worksheet1 = (_Worksheet)workbook1.ActiveSheet; foreach (DataColumn column1 in dt.Columns) { num2++; application1.Cells[4, num2] = column1.ColumnName; worksheet1.get_Range(application1.Cells[4, num2], application1.Cells[4, num2]).Font.Bold = true; worksheet1.get_Range(application1.Cells[4, num2], application1.Cells[4, num2]).HorizontalAlignment = XlVAlign.xlVAlignCenter; worksheet1.get_Range(application1.Cells[4, num2], application1.Cells[4, num2]).Select(); worksheet1.get_Range(application1.Cells[4, num2], application1.Cells[4, num2]).Interior.ColorIndex = this.titleColorindex; } foreach (DataRow row1 in dt.Rows) { num1++; num2 = 1; foreach (DataColumn column2 in dt.Columns) { num2++; if (column2.DataType == Type.GetType("System.DateTime")) { application1.Cells[num1, num2] = Convert.ToDateTime(row1[column2.ColumnName].ToString()).ToString("yyyy-MM-dd"); worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).HorizontalAlignment = XlVAlign.xlVAlignCenter; } else { if (column2.DataType == Type.GetType("System.String")) { application1.Cells[num1, num2] = "'" + row1[column2.ColumnName].ToString(); worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).HorizontalAlignment = XlVAlign.xlVAlignCenter; continue; } application1.Cells[num1, num2] = row1[column2.ColumnName].ToString(); } } } int num3 = num1 + 1; int num4 = 2; application1.Cells[num3, 2] = "/u5408/u8ba1"; worksheet1.get_Range(application1.Cells[num3, 2], application1.Cells[num3, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; worksheet1.get_Range(application1.Cells[num3, num4], application1.Cells[num3, num2]).Select(); application1.Cells[2, 2] = strTitle; worksheet1.get_Range(application1.Cells[2, 2], application1.Cells[2, 2]).Font.Bold = true; worksheet1.get_Range(application1.Cells[2, 2], application1.Cells[2, 2]).Font.Size = 0x16; worksheet1.get_Range(application1.Cells[4, 2], application1.Cells[num3, num2]).Select(); worksheet1.get_Range(application1.Cells[4, 2], application1.Cells[num3, num2]).Columns.AutoFit(); worksheet1.get_Range(application1.Cells[2, 2], application1.Cells[2, num2]).Select(); worksheet1.get_Range(application1.Cells[2, 2], application1.Cells[2, num2]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; worksheet1.get_Range(application1.Cells[4, 2], application1.Cells[num3, num2]).Borders.LineStyle = 1; worksheet1.get_Range(application1.Cells[4, 2], application1.Cells[num3, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick; worksheet1.get_Range(application1.Cells[4, 2], application1.Cells[4, num2]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick; worksheet1.get_Range(application1.Cells[4, num2], application1.Cells[num3, num2]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick; worksheet1.get_Range(application1.Cells[num3, 2], application1.Cells[num3, num2]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick; this.afterTime = DateTime.Now; this.ClearFile(FilePath); string text1 = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls"; application1.ActiveWorkbook.SaveAs(FilePath + text1, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); workbook1.Close(null, null, null); application1.Workbooks.Close(); application1.Quit(); if (worksheet1 != null) { Marshal.ReleaseComObject(worksheet1); worksheet1 = null; } if (workbook1 != null) { Marshal.ReleaseComObject(workbook1); workbook1 = null; } if (application1 != null) { Marshal.ReleaseComObject(application1); application1 = null; } GC.Collect(); return text1; } public int TitleColorIndex { get { return this.titleColorindex; } set { this.titleColorindex = value; } } } }