/// <summary> /// Fill the excel sheet with data along with the position specified /// </summary> /// <param name="columnrow"></param> /// <param name="data"></param> private void FillExcelWithData(string startColumn) { _excelRange = _excelSheet.get_Range(startColumn, _value); _excelRange = _excelRange.get_Resize(RowCount + 1, ColumnCount); _excelRange.set_Value(Missing.Value, ExcelData); _excelRange.EntireColumn.AutoFit(); }
/// <summary> /// Method to add an excel rows /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> /// <param name="values"></param> private void AddExcelRows(string startRange, int rowCount, int colCount, object values) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.set_Value(_optionalValue, values); }
/// <summary> /// fill the header columns for the range specified and make it bold if specified /// </summary> /// <param name="headers"></param> /// <param name="startColumn"></param> /// <param name="endColumn"></param> protected void FillHeaderColumn(object[] headers, string startColumn, string endColumn) { _excelRange = _excelSheet.get_Range(startColumn, endColumn); _excelRange.set_Value(_value, headers); if (BoldHeaders == true) { this.BoldRow(startColumn, endColumn); } _excelRange.EntireColumn.AutoFit(); }
private void ExportData(List <string[]> rows, Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet) { object[,] rowrows = new object[rows.Count, 6]; // string[,] rowrows = new string[rows.Count,6]; int i = 0; foreach (string[] sa in rows) { int j = 0; foreach (string s in sa) { if (j == 4) { rowrows[i, j] = float.Parse(s); } else { rowrows[i, j] = s; } j++; } i++; } int rowCount = rowrows.GetLength(0); int columnCount = rowrows.GetLength(1); Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[2, 1]; range = range.get_Resize(rowCount, 6); range.set_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault, rowrows); //int rowCount = 2; //foreach (string[] s in rows) //{ // xlWorkSheet.Cells[rowCount, 1] = s[0]; // xlWorkSheet.Cells[rowCount, 2] = s[1]; // xlWorkSheet.Cells[rowCount, 3] = s[2]; // xlWorkSheet.Cells[rowCount, 4] = s[3]; // xlWorkSheet.Cells[rowCount, 5] = s[4]; // xlWorkSheet.Cells[rowCount, 6] = s[5]; // rowCount++; //} }
private void AddDocumentNoInLastRow(string filename, string docno) { try { Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks; Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets; Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[1]; workSheet.Select(Type.Missing); Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange; int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row; string strRange = string.Format("B{0}", lastRow + 2); Microsoft.Office.Interop.Excel.Range docIsoRange = workSheet.get_Range(strRange, Type.Missing); //docIsoRange.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); docIsoRange.set_Value(System.Type.Missing, docno); //lastCell.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); //range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); System.Runtime.InteropServices.Marshal.ReleaseComObject(docIsoRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(range); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); book.Save(); book.Close(false, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(book); ObjExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel); } catch (Exception ex) { // } }
private void tsbtn_Build_Click(object sender, EventArgs e) { CloseProcess("EXCEL"); //关闭所有Excel进程 object missing = System.Reflection.Missing.Value; //定义object缺省值 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(tstxt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表 worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[tscbox_Sheet.Text]); //获取选择的工作表 Microsoft.Office.Interop.Excel.Range searchRange = worksheet.get_Range("A1", "E1"); //定义标题范围 object[] P_obj_Items = { "编程词典", "VC编程词典", "JAVA编程词典", "ASP.NET编程词典", "C#编程词典" }; searchRange.set_Value(missing, P_obj_Items); //绘制标题 searchRange.Font.Bold = true; //设置字体加粗 searchRange.Font.Name = "宋体"; //设置字体样式 searchRange.Font.Size = 10; //设置字体大小 searchRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置标题对齐方式 //获得要生成图表的数据 for (int i = 0; i < 13; i++) { worksheet.Cells[2 + i, 1] = i; worksheet.Cells[2 + i, 2] = i + 1; worksheet.Cells[2 + i, 3] = i + 2; worksheet.Cells[2 + i, 4] = i + 3; worksheet.Cells[2 + i, 5] = i + 4; } //实例化Excel绘图对象 Microsoft.Office.Interop.Excel.Chart chart = (Microsoft.Office.Interop.Excel.Chart)workbook.Charts.Add(missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Range chartRange = worksheet.get_Range("A1:A14", "B1:E14");//定义绘制图表范围 //在指定范围绘制图表 chart.ChartWizard(chartRange, Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, missing, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns, 1, 1, true, "编程词典销量分析", "月份", "销量", missing); excel.DisplayAlerts = false; //设置保存Excel时不显示对话框 workbook.Save(); //保存工作簿 workbook.Close(false, missing, missing); //关闭工作簿 CloseProcess("EXCEL"); //关闭所有Excel进程 WBrowser_Excel.Navigate(tstxt_Excel.Text); //在窗体中重新显示Excel文件内容 }
private void ExportExcelWithInsertTemplate(string filename, string ext) { string templateFilename = string.Format("{0}\\{1}\\{2}.{3}", Application.StartupPath, UiUtility.ExcelTemplatePath, this.TPL_FILE_NAME, ext); try { //declare for using Ms.Excel Object Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); ObjExcel.DisplayAlerts = false; //for template excel Microsoft.Office.Interop.Excel.Workbooks objTempbooks = ObjExcel.Workbooks; //for current excel Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks; //----------------------------------------------------------------------------------------------------------------------------- Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets; Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[this.SheetName]; workSheet.Select(Type.Missing); //Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange; Microsoft.Office.Interop.Excel.Range insertRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing); insertRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove); //insert last row //Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange; //int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row; //string strRange = string.Format("A{0}", lastRow + 1); //Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing); //insertLastRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow); //----------------------------------------------------------------------------------------------------------------------------- Microsoft.Office.Interop.Excel.Workbook tmpBook = objTempbooks.Open(templateFilename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Microsoft.Office.Interop.Excel.Sheets tmpSheets = tmpBook.Worksheets; Microsoft.Office.Interop.Excel.Worksheet tmpWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)tmpSheets[this.SheetName]; tmpWorkSheet.Select(Type.Missing); Microsoft.Office.Interop.Excel.Range copyRange = tmpWorkSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing); copyRange.EntireRow.Copy(Type.Missing); Microsoft.Office.Interop.Excel.Range pasteRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing); pasteRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); //----------------------------------------------------------------------------------------------------------------------------- //Microsoft.Office.Interop.Excel.Range copyLastRange = tmpWorkSheet.get_Range("A11", Type.Missing); //copyLastRange.EntireRow.Copy(Type.Missing); //Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange; //int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row; //string strRange = string.Format("A{0}", lastRow + 1); //Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing); //insertLastRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAllUsingSourceTheme, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); //------------------------------------------------------------------------------------------------------------------------------------- book.Application.CutCopyMode = Microsoft.Office.Interop.Excel.XlCutCopyMode.xlCopy; //book.CheckCompatibility = false; tmpBook.Close(false, Type.Missing, Type.Missing); if (this._ShippingOrder_Hdr != null) { //Set Shipping Order Information. //SO_NO Microsoft.Office.Interop.Excel.Range sonoRange = workSheet.get_Range("K2", Type.Missing); sonoRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.SO_NO); //SO_DATE Microsoft.Office.Interop.Excel.Range sodateRange = workSheet.get_Range("K3", Type.Missing); if (this._ShippingOrder_Hdr.SO_DATE.HasValue) { sodateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.SO_DATE.Value)); } else { sodateRange.set_Value(System.Type.Missing, string.Empty); } //Customer Microsoft.Office.Interop.Excel.Range custRange = workSheet.get_Range("B4", Type.Missing); custRange.set_Value(System.Type.Missing, string.Format("{0} - {1}", this._ShippingOrder_Hdr.PARTY_ID, this._ShippingOrder_Hdr.PARTY_NAME)); //PO_REF_NO Microsoft.Office.Interop.Excel.Range porefRange = workSheet.get_Range("B5", Type.Missing); porefRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REF_NO); //PO_REF_DATE Microsoft.Office.Interop.Excel.Range podateRange = workSheet.get_Range("G5", Type.Missing); if (this._ShippingOrder_Hdr.REF_DATE.HasValue) { podateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.REF_DATE.Value)); } else { podateRange.set_Value(System.Type.Missing, string.Empty); } //ETD Microsoft.Office.Interop.Excel.Range etdRange = workSheet.get_Range("K5", Type.Missing); if (this._ShippingOrder_Hdr.ETA.HasValue) { etdRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy HH:mm}", this._ShippingOrder_Hdr.ETA.Value)); } else { etdRange.set_Value(System.Type.Missing, string.Empty); } //REMARK Microsoft.Office.Interop.Excel.Range remarkRange = workSheet.get_Range("B6", Type.Missing); remarkRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REMARK); var focusRange = workSheet.get_Range("A1", "A1").Select(); book.Save(); book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(sonoRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(sodateRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(custRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(porefRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(podateRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(etdRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(remarkRange); } else { var focusRange = workSheet.get_Range("A1", "A1").Select(); book.Save(); book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange); } //------------------------------------------------------------------------------------- //System.Runtime.InteropServices.Marshal.ReleaseComObject(insertLastRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(copyRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(insertRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(book); ObjExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel); } catch (Exception ex) { // } }
/// <summary> /// Extension method to write list data to Microsoft.Office.Interop.Excel File. /// </summary> /// <typeparam name="T">Ganeric list</typeparam> /// <param name="list"></param> /// <param name="PathToSave">Path to save file.</param> public static void ToExcel <T>(this List <T> list, string PathToSave) { #region Declarations if (string.IsNullOrEmpty(PathToSave)) { throw new Exception("Invalid file path."); } else if (PathToSave.ToLower().Contains("") == false) { throw new Exception("Invalid file path."); } if (list == null) { throw new Exception("No data to export."); } Microsoft.Office.Interop.Excel.Application excelApp = null; Microsoft.Office.Interop.Excel.Workbooks books = null; Microsoft.Office.Interop.Excel._Workbook book = null; Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel._Worksheet sheet = null; Microsoft.Office.Interop.Excel.Range range = null; Microsoft.Office.Interop.Excel.Font font = null; // Optional argument variable object optionalValue = Missing.Value; string strHeaderStart = "A2"; string strDataStart = "A3"; #endregion #region Processing try { #region Init Microsoft.Office.Interop.Excel app. excelApp = new Microsoft.Office.Interop.Excel.Application(); books = (Microsoft.Office.Interop.Excel.Workbooks)excelApp.Workbooks; book = (Microsoft.Office.Interop.Excel._Workbook)(books.Add(optionalValue)); sheets = (Microsoft.Office.Interop.Excel.Sheets)book.Worksheets; sheet = (Microsoft.Office.Interop.Excel._Worksheet)(sheets.get_Item(1)); #endregion #region Creating Header Dictionary <string, string> objHeaders = new Dictionary <string, string>(); PropertyInfo[] headerInfo = typeof(T).GetProperties(); foreach (var property in headerInfo) { var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false) .Cast <DisplayNameAttribute>().FirstOrDefault(); objHeaders.Add(property.Name, attribute == null ? property.Name : attribute.DisplayName); } range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(1, objHeaders.Count); range.set_Value(optionalValue, objHeaders.Values.ToArray()); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); font = range.Font; font.Bold = true; range.Interior.Color = Color.LightGray.ToArgb(); #endregion #region Writing data to cell int count = list.Count; object[,] objData = new object[count, objHeaders.Count]; for (int j = 0; j < count; j++) { var item = list[j]; int i = 0; foreach (KeyValuePair <string, string> entry in objHeaders) { var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null); objData[j, i++] = (y == null) ? "" : y.ToString(); } } range = sheet.get_Range(strDataStart, optionalValue); range = range.get_Resize(count, objHeaders.Count); range.set_Value(optionalValue, objData); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(count + 1, objHeaders.Count); range.Columns.AutoFit(); #endregion #region Saving data and Opening Microsoft.Office.Interop.Excel file. if (string.IsNullOrEmpty(PathToSave) == false) { book.SaveCopyAs(PathToSave); } excelApp.Visible = true; #endregion #region Release objects try { if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } sheet = null; if (sheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); } sheets = null; if (book != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(book); } book = null; if (books != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(books); } books = null; if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } excelApp = null; } catch (Exception ex) { sheet = null; sheets = null; book = null; books = null; excelApp = null; } finally { GC.Collect(); } #endregion } catch (Exception ex) { throw ex; } #endregion }