C # Excel operations,

C # Excel operations,

Reference page:

Http://www.yuanjiaocheng.net/CSharp/cshart-value-reference-type.html

Http://www.yuanjiaocheng.net/CSharp/Csharp-keys.html

Http://www.yuanjiaocheng.net/CSharp/csharp-interface.html

Http://www.yuanjiaocheng.net/CSharp/Csharp-operators.html

Http://www.yuanjiaocheng.net/CSharp/Csharp-if-else.html

Basic operations

Public class ExcelUtil {public void ReleaseCOM (object pObj) // release resources {try {System. runtime. interopServices. marshal. releaseComObject (pObj);} catch {throw new Exception ("An error occurred while releasing the resource! ");} Finally {pObj = null;} public ExcelUtil (string filePath) {this. filePath = filePath; app = new Microsoft. office. interop. excel. application (); // open an Excel Application if (app = null) {return;} wbs = app. workbooks; wb = wbs. add (filePath); shs = wb. sheets; int sheetNumber = shs. count; for (int I = 1; I <= sheetNumber; I ++) {_ Worksheet sh = (_ Worksheet) shs. get_Item (I); this. shList. add (sh) ;}/// <s Ummary> // save the file /// </summary> public ExcelUtil (string filePath, int addSheet) {this. filePath = filePath; app = new Microsoft. office. interop. excel. application (); // open an Excel Application if (app = null) {return;} wbs = app. workbooks; wb = wbs. add (filePath); shs = wb. sheets; int sheetNumber = shs. count; int addSheetCount = addSheet-sheetNumber; if (addSheetCount> 0) {for (int I = 0; I <addSheetCount; I ++) {var sheet = (_ Worksheet) shs. add (Missing. value, Missing. value, Missing. value, Missing. value) ;}} int currentSheetNumber = shs. count; for (int I = 1; I <= currentSheetNumber; I ++) {_ Worksheet sh = (_ Worksheet) shs. get_Item (I); this. shList. add (sh) ;}//< summary> // save the file /// </summary> public void save () {if (filePath! = Null) {FileInfo old = new FileInfo (filePath); if (old. exists) {File. delete (filePath);} wb. saveAs (filePath, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Microsoft. office. interop. excel. xlSaveAsAccessMode. xlExclusive, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing); foreach (_ Worksheet inst in shList) {ReleaseCOM (inst);} ReleaseCOM (shs); ReleaseCOM (Wb); ReleaseCOM (wbs); app. Quit (); ReleaseCOM (app) ;}/// <summary >/// save excel by killing the process. /// Solves the formula exception during normal storage. /// </Summary> public void saveByKillProcess () {if (filePath! = Null) {try {FileInfo old = new FileInfo (filePath); if (old. exists) {File. delete (filePath);} wb. saveAs (filePath, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing, Microsoft. office. interop. excel. xlSaveAsAccessMode. xlExclusive, Type. missing, Type. missing, Type. missing, Type. missing, Type. missing); foreach (_ Worksheet inst in shList) {ReleaseCOM (inst);} ReleaseCOM (shs); ReleaseCOM (wb); ReleaseCOM (wbs); KillExcel (app );} catch (System. exception e) {logger. debug (e. message );}}} /// <summary> /// merge Excel cells /// </summary> /// <param name = "ws"> sheet </param> // <param name = "str"> cell column number A in the upper left corner of the cell to be merged </param> // <param name = "I"> cell in the upper left corner of the cell to be merged row number 2 </param> /// <param name = "str3"> cell column number B in the lower-right corner of the cell to be merged </param> /// <param name = "I "> cell Row 2 in the lower-right corner of the cell to be merged </param> /// <param name =" j "> row number of the last row of the table </param> /// <returns> cell </returns> public static Range CombineExcel (_ Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown) {try {string str1 = GetColStrFromInt (colLeft) + rowUp; string str2 = GetColStrFromInt (colRight) + rowDown; Range range = ws. get_Range (str1, str2); range. merge (0); range. horizontalAlignment = XlHAlign. xlHAlignCenter; range. font. underline = false; range. wrapText = true; // range. entireColumn. autoFit (); range. borders. lineStyle = 1; return range;} catch (Exception ex) {logger. error (ex. message); return null ;}/// <summary> /// pass in the column number to get the corresponding column name, starting from 1, 1 indicates column A // </summary> /// <param name = "col"> column number </param> /// <returns> </returns> public static string GetColStrFromInt (int col) {col = col + 'a'-1; string colStr = ""; if (col> (int) 'Z') {colStr = Convert. toChar (col-90-1)/26 + 'A '). toString () + Convert. toChar (col-90-1) % 26 + 'A '). toString (); return colStr;} else return Convert. toChar (col ). toString ();} [DllImport ("User32.dll", CharSet = CharSet. auto)] public static extern int GetWindowThreadProcessId (IntPtr hwnd, out int ID); private void KillExcel (Microsoft. office. interop. excel. application excel) {IntPtr t = new IntPtr (excel. hwnd); // obtain the handle. The specific function is to obtain the int k = 0 for this memory entry; GetWindowThreadProcessId (t, out k); // obtain the unique identifier k System of the process. diagnostics. process p = System. diagnostics. process. getprocpolicyid (k); // get the reference to process k p. kill (); // close process k} private Microsoft. office. interop. excel. application app; private Workbooks wbs; private _ Workbook wb; private Sheets shs; private List <_ Worksheet> shList = new List <_ Worksheet> (); public List <_ Worksheet> ShList {get {return shList;} set {shList = value;} private string filePath; protected readonly static log4net. ILog logger = log4net. logManager. getLogger (System. reflection. methodBase. getCurrentMethod (). declaringType );}

Get a merged range to insert data.

Private Range GetCurrentRange (_ Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown, string result) {Range currentRange = null; currentRange = ExcelUtil. combineExcel (ws, colLeft, rowUp, colRight, rowDown); currentRange. value2 = result; insert result currentRange. borders. lineStyle = 0; // border line currentRange. font. colorIndex = 3; // Insert the color currentRange. font. bold = true; // Bold currentRange. columnWidth = 100; // control the column width currentRange. horizontalAlignment = XlHAlign. xlHAlignLeft; // The Position of the text, currentRange. borders [XlBordersIndex. xlEdgeBottom]. weight = XlBorderWeight. xlThick; // The bottom border is bold var range = ws. cells; // select the entire document range. interior. colorIndex = 2; // fill in the background color return currentRange ;}

Create hyperlink

/// <Summary> /// Inserts the catalogue. directory hyperlink /// </summary> /// <param name = "ws"> The ws. </param> /// <param name = "colLeft"> The col left. </param> /// <param name = "rowUp"> The row up. </param> // <param name = "colRight"> The col right. </param> /// <param name = "rowDown"> The row down. </param> // <param name = "result"> The result. </param> // <param name = "colWidth"> Width of the col. </param> /// <param name = "Sheet"> The sheet. </param> private void InsertCatalogue (_ Worksheet ws, int colLeft, int rowUp, int colRight, int rowDown, string result, string sheet) {var currentRange = this. getCurrentRange (ws, colLeft, rowUp, colRight, rowDown, result); currentRange. font. colorIndex = 5; currentRange. font. bold = true; string hyperlink = "#" + sheet + "! "+ ExcelUtil. getColStrFromInt (colLeft) + rowUp; ws. hyperlinks. add (currentRange, hyperlink, System. reflection. missing. value, System. reflection. missing. value, System. reflection. missing. value); currentRange. font. underline = false; currentRange. horizontalAlignment = XlHAlign. xlHAlignLeft ;}

Draw a Statistical Chart of data

Private void Chart (_ Worksheet ws, int startRow, Catalogue catalogue) {Range chartRange; ChartObjects xlCharts = (ChartObjects) ws. chartObjects (Type. missing); ChartObject myChart = (ChartObject) xlCharts. add (250, 20,600,300); Chart chartPage = myChart. chart; string title = "title name"; this. insertResult (ws, 6, 25, 6, 25, title); string lowRange = ws. get_Range ("K31", Type. missing ). value2.ToString (); stri Ng hightRange = ws. get_Range ("L31", Type. missing ). value2.ToString (); double temp = 0; bool isAllLine = false; string chartRangeSelect = "I31: I" + startRow + ", K31: K" + startRow + ", L31: L "+ startRow; if (double. tryParse (lowRange, out temp) & double. tryParse (hightRange, out temp) {isAllLine = true;} else {if (! Double. tryParse (lowRange, out temp) {chartRangeSelect = "I31: I" + startRow + ", L31: L" + startRow;} else {chartRangeSelect = "I31: I "+ startRow +", K31: K "+ startRow ;}} chartRange = ws. get_Range (chartRangeSelect, Type. missing); chartPage. chartWizard (chartRange, XlChartType. xlConeCol, Type. missing, XlRowCol. xlColumns, Type. missing, Type. missing, true, title, Type. missing, Type. missing, Type. missing); chartPage. setSourceData (chartRange, XlRowCol. xlColumns); Series oSeries1 = (Series) chartPage. seriesCollection (2); oSeries1.ChartType = XlChartType. xlLine; oSeries1.Border. colorIndex = 3; Series oSeries = (Series) chartPage. seriesCollection (1); oSeries. chartType = XlChartType. xlLine; oSeries. border. colorIndex = 5; if (isAllLine) {Series oSeries2 = (Series) chartPage. seriesCollection (3); oSeries2.ChartType = XlChartType. xlLine; oSeries2.Border. colorIndex = 3;} chartPage. legend. delete (); Axis xAxis = (Axis) chartPage. axes (XlAxisType. xlCategory, XlAxisGroup. xlPrimary); xAxis. delete (); var range = ws. cells; range. wrapText = false ;}