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;
}
}