private void PrepareWorksheetReadyForData() { Excel.Workbooks workbooks = iExcelApp.Workbooks; workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Workbook workbook = workbooks.get_Item(workbooks.Count); Excel.Sheets sheets = workbook.Worksheets; Excel.Worksheet sheet = null; // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerHeapSize = new Comparers.HeapSize(sheet, iCol1FileName, iCol2FileName); iComparerHeapSize.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerCellCounts = new Comparers.CellCounts(sheet, iCol1FileName, iCol2FileName); iComparerCellCounts.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerLargestCells = new Comparers.LargestCells(sheet, iCol1FileName, iCol2FileName); iComparerLargestCells.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerFragmentation = new Comparers.Fragmentation(sheet, iCol1FileName, iCol2FileName); iComparerFragmentation.PrepareWorksheetReadyForData(); // sheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); iComparerSlackSpace = new Comparers.SlackSpace(sheet, iCol1FileName, iCol2FileName); iComparerSlackSpace.PrepareWorksheetReadyForData(); }
public static void ShowImportTemplete(string TempleteName) { try { string filename = clsLxms.GetParameterValue("EXCEL_TEMPLETE_FILE"); if (!File.Exists(filename)) { MessageBox.Show(clsTranslate.TranslateString("Can not find the templete file") + ": \r\n" + filename, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } string destfile = @Path.GetTempPath() + TempleteName + ".xls"; Util.retValue1 = destfile; File.Copy(filename, destfile, true); Excel.Application m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; Excel.Workbooks m_objBooks = m_objExcel.Workbooks; m_objBooks.Open(destfile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Workbook m_objBook = (Excel.Workbook)m_objBooks.get_Item(1); Excel.Sheets sm_objSheets = (Excel.Sheets)m_objBook.Worksheets; Excel.Worksheet m_objSheet = (Excel.Worksheet)sm_objSheets.get_Item(TempleteName); for (int i = sm_objSheets.Count; i >= 1; i--) { Excel.Worksheet m_Sheet = sm_objSheets.get_Item(i); if (m_Sheet.Name.ToLower().CompareTo(TempleteName.ToLower()) != 0) { m_Sheet.Delete(); } } m_objSheet.Name = "Sheet1"; //m_objSheet.Activate(); System.Windows.Forms.Application.DoEvents(); m_objExcel.DisplayAlerts = false; m_objExcel.AlertBeforeOverwriting = false; //保存工作簿 m_objExcel.Visible = true; return; } catch (System.Exception ex) { MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } }
private void PrepareWorksheetReadyForData( ) { Excel.Workbooks workbooks = iExcelApp.Workbooks; workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Workbook workbook = workbooks.get_Item(workbooks.Count); Excel.Sheets sheets = workbook.Worksheets; iSheetChunkSize = (Excel.Worksheet)sheets.get_Item(1); CreateSheet(iSheetChunkSize, "Chunk Size"); iSheetFree = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); CreateSheet(iSheetFree, "Free Size"); iSheetAlloc = (Excel.Worksheet)sheets.Add(Type.Missing, sheets.get_Item(sheets.Count), 1, Excel.XlSheetType.xlWorksheet); CreateSheet(iSheetAlloc, "Alloc Size"); }
private void PrepareWorsheetForSaving() { // Finalise comparison sheets iComparerHeapSize.FinaliseWorksheet(); iComparerCellCounts.FinaliseWorksheet(); iComparerLargestCells.FinaliseWorksheet(); iComparerFragmentation.FinaliseWorksheet(); iComparerSlackSpace.FinaliseWorksheet(); // Create summary sheet Excel.Workbooks workbooks = iExcelApp.Workbooks; Excel.Workbook workbook = workbooks.get_Item(workbooks.Count); Excel.Sheets sheets = workbook.Worksheets; Excel.Worksheet sheet = (Excel.Worksheet)sheets.get_Item(1); CreateSummary(sheet); sheet.Activate(); }
private void SaveResultBtn_Click(object sender, EventArgs e) { Excel.Application xlexcel = new Excel.Application(); xlexcel.Visible = true; xlexcel.UserControl = true; Excel.Workbooks xlWorkBook = xlexcel.Workbooks; System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US"); xlWorkBook.GetType().InvokeMember("Add", System.Reflection.BindingFlags.InvokeMethod, null, xlWorkBook, null, ci); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.get_Item(1).ActiveSheet; xlWorkSheet.Cells[1, 1].Value2 = ResultList.Columns[0].Text; xlWorkSheet.Cells[1, 2].Value2 = ResultList.Columns[1].Text; for (int row = 2; row < ResultList.Items.Count + 1; row++) // начиная с 2, так как индексация у екселя начинается с 1 + первая строка - header'ы { xlWorkSheet.Cells[row, 1].Value2 = ResultList.Items[row - 1].SubItems[0].Text; // имя параметра xlWorkSheet.Cells[row, 2].Value2 = ResultList.Items[row - 1].SubItems[1].Text; /* если потребуется разделять значения по клеткам экселя * var resultList = ResultList.Items[row].SubItems[1].Text.Split(','); * * for (int column = 2; column < resultList.Length+2; column++) * { * xlWorkSheet.Cells[row, column] = resultList[column-2]; // имя параметра * * } */ } ///////////////// releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlexcel); Clipboard.Clear(); }
private void Init(string pFileName) { f_FileName = pFileName; /// //== PREPARE EXCEL OBJECTS. xlApp = new Excel.Application(); xlBooks = (Excel.Workbooks)xlApp.Workbooks; if (pFileName != "") { xlBooks.Open( pFileName , xlMissing, xlMissing, xlMissing, xlMissing, xlMissing, xlMissing , xlMissing, xlMissing, xlMissing, xlMissing, xlMissing, xlMissing , xlMissing, xlMissing ); xlBook = xlBooks.get_Item(1); } else { xlBook = (Excel.Workbook)(xlBooks.Add(xlMissing)); } xlSheets = (Excel.Sheets)xlBook.Worksheets; ///--xlSheet = (Excel.Worksheet)(xlSheets.get_Item(1)); }
/// <summary> /// 模板导出Excel /// 可分页导出. /// </summary> /// <param name="sheetname">sheetname</param> /// <param name="startrow">startrow</param> /// <param name="rows">rows</param> /// <param name="cols">cols</param> /// <param name="copies">copies</param> /// <param name="returnfile">returnfile</param> public static void ExportByTemplate(IList <modExcelRangeData> list, string sheetname, int startrow, int rows, int cols, int copies) { try { if (list == null || list.Count == 0) { MessageBox.Show(clsTranslate.TranslateString("No data!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } string filename; #if DEBUG filename = @"C:\HongBiao\templates.xls"; #else filename = clsLxms.GetParameterValue("EXCEL_TEMPLETE_FILE"); #endif if (!File.Exists(filename)) { MessageBox.Show(clsTranslate.TranslateString("Can not find the templete file") + ": \r\n" + filename, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } string destfile = GetExportFilePath(0); if (File.Exists(destfile)) { File.Delete(destfile); } Util.retValue1 = destfile; File.Copy(filename, destfile); Excel.Application m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; Excel.Workbooks m_objBooks = m_objExcel.Workbooks; m_objBooks.Open(destfile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Workbook m_objBook = (Excel.Workbook)m_objBooks.get_Item(1); Excel.Sheets sm_objSheets = (Excel.Sheets)m_objBook.Worksheets; Excel.Worksheet m_objSheet = (Excel.Worksheet)sm_objSheets.get_Item(sheetname); for (int i = sm_objSheets.Count; i >= 1; i--) { Excel.Worksheet m_Sheet = sm_objSheets.get_Item(i); if (m_Sheet.Name.ToLower().CompareTo(sheetname.ToLower()) != 0) { m_Sheet.Delete(); } } //Excel.Range templaterange = m_objSheet.Range(m_objSheet.Cells[startrow, 1], m_objSheet.Cells[startrow + rows - 1, cols]); Excel.Range templaterange = m_objSheet.Range[m_objSheet.Cells[startrow, 1], m_objSheet.Cells[startrow + rows - 1, cols]]; for (int i = 1; i < copies; i++) { Excel.Range targetrange = (Excel.Range)m_objSheet.Range[m_objSheet.Cells[rows * i + 1, 1], m_objSheet.Cells[rows * (i + 1), cols]]; templaterange.Copy(targetrange); for (int k = 0; k < rows; k++) { m_objSheet.Rows[rows * i + 1 + k].RowHeight = m_objSheet.Rows[startrow + k].RowHeight; } } Excel.Range range; foreach (modExcelRangeData mod in list) { range = (Excel.Range)m_objSheet.get_Range(mod.CellBegin, mod.CellEnd); if (!mod.Is_Pic) { range.Value = mod.CellValue; } else { if (!string.IsNullOrEmpty(mod.CellValue) && File.Exists(mod.CellValue)) { System.Drawing.Image im = System.Drawing.Image.FromFile(mod.CellValue); double iLeft = range.Left + 1; double iTop = range.Top + 1; double iWidth = range.Width - 2; double iHeight = range.Height - 2; if (im.Width <= range.Width) { iWidth = im.Width; iLeft = range.Left + (range.Width - im.Width) / 2; } if (im.Height <= range.Height) { iHeight = im.Height; iTop = range.Top + (range.Height - im.Height) / 2; } im.Dispose(); m_objSheet.Activate(); range.Select(); m_objSheet.Shapes.AddPicture(mod.CellValue, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, (float)iLeft, (float)iTop, (float)iWidth, (float)iHeight); } } } m_objSheet.Activate(); System.Windows.Forms.Application.DoEvents(); m_objExcel.DisplayAlerts = false; m_objExcel.AlertBeforeOverwriting = false; //保存工作簿 m_objExcel.Visible = true; return; } catch (System.Exception ex) { MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } }
public static void ExportByTemplete(IList <modExcelRangeData> list, string sheetname) { try { string filename = clsLxms.GetParameterValue("EXCEL_TEMPLETE_FILE"); if (!File.Exists(filename)) { MessageBox.Show(clsTranslate.TranslateString("Can not find the templete file") + ": \r\n" + filename, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } string destfilename = GetExportFilePath(0); if (File.Exists(destfilename)) { File.Delete(destfilename); } Util.retValue1 = destfilename; File.Copy(filename, destfilename); Excel.Application m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; Excel.Workbooks m_objBooks = m_objExcel.Workbooks; m_objBooks.Open(destfilename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Workbook m_objBook = (Excel.Workbook)m_objBooks.get_Item(1); Excel.Sheets sm_objSheets = (Excel.Sheets)m_objBook.Worksheets; Excel.Worksheet m_objSheet = (Excel.Worksheet)sm_objSheets.get_Item(sheetname); for (int i = sm_objSheets.Count; i >= 1; i--) { Excel.Worksheet m_Sheet = sm_objSheets.get_Item(i); if (m_Sheet.Name.ToLower().CompareTo(sheetname.ToLower()) != 0) { m_Sheet.Delete(); } } Excel.Range range; foreach (modExcelRangeData mod in list) { range = (Excel.Range)m_objSheet.get_Range(mod.CellBegin, mod.CellEnd); if (!mod.Is_Pic) { range.Value = mod.CellValue; } else { if (!string.IsNullOrEmpty(mod.CellValue) && File.Exists(mod.CellValue)) { System.Drawing.Image im = System.Drawing.Image.FromFile(mod.CellValue); double iLeft = range.Left + 1; double iTop = range.Top + 1; double iWidth = range.Width - 2; double iHeight = range.Height - 2; if (im.Width <= range.Width) { iWidth = im.Width; iLeft = range.Left + (range.Width - im.Width) / 2; } if (im.Height <= range.Height) { iHeight = im.Height; iTop = range.Top + (range.Height - im.Height) / 2; } im.Dispose(); m_objSheet.Activate(); range.Select(); m_objSheet.Shapes.AddPicture(mod.CellValue, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, (float)iLeft, (float)iTop, (float)iWidth, (float)iHeight); } } } m_objSheet.Activate(); m_objExcel.Visible = true; } catch (System.Exception ex) { MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } }