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();
}
public bool ExcelWorkbookPrintToPDF(string fromExcelPath, string toPath)
{
bool flag = false;
if (File.Exists(fromExcelPath))
{
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
object missing = Type.Missing;
try
{
if (fromExcelPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的源文件路径不能为空。");
}
if (toPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的目标文件路径不能为空。");
}
excel = new EXCEL.ApplicationClass();
workBooks = excel.Workbooks;
Type type = workBooks.GetType();
workBook = workBooks.Open(fromExcelPath, missing, true,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//先使用分页视图打开,EXCEl获取 HPageBreaks 需要在分页视图中
excel.ActiveWindow.View = EXCEL.XlWindowView.xlPageBreakPreview;
//int hpbCount = classExcelMthd.getSheetPageCount(workBook, 1);
//按照设置好的打印区域发布为pdf
workBook.PrintOutEx(missing, missing, missing, false, missing,
true, false, "ZZY", true);
//再还原为普通视图
excel.ActiveWindow.View = EXCEL.XlWindowView.xlNormalView;
flag = true;
}
catch (Exception exception)
{
classLims_NPOI.WriteLog(exception, "");
flag = false;
}
finally
{
if (workBook != null)
{
workBook.Close(false, missing, missing);
Marshal.ReleaseComObject(workBook);
//Marshal.FinalReleaseComObject(workBook);
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBook = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
//Marshal.FinalReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}
/// <summary>
/// 打印workbook,使用另存为功能转pdf,指定打印页码
/// </summary>
/// <param name="fromExcelPath">源excel路径</param>
/// <param name="toPath">输出路径</param>
/// <param name="pageFrom">打印起始页码</param>
/// <param name="pageTo">打印结束页码</param>
/// <returns>成功或失败</returns>
public bool SaveExcelWorkbookAsPDFWithPage(string fromExcelPath, string toPath, int pageFrom, int pageTo)
{
bool flag = false;
if (File.Exists(fromExcelPath))
{
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
object missing = Type.Missing;
try
{
if (fromExcelPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的源文件路径不能为空。");
}
if (toPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的目标文件路径不能为空。");
}
excel = new EXCEL.ApplicationClass();
workBooks = excel.Workbooks;
Type type = workBooks.GetType();
workBook = workBooks.Open(fromExcelPath, missing, true,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//先使用分页视图打开,EXCEl获取 HPageBreaks 需要在分页视图中
excel.ActiveWindow.View = EXCEL.XlWindowView.xlPageBreakPreview;
//int hpbCount = classExcelMthd.getSheetPageCount(workBook, 1);
//按照设置好的打印区域发布为pdf
workBook.ExportAsFixedFormat(
EXCEL.XlFixedFormatType.xlTypePDF,
toPath,
EXCEL.XlFixedFormatQuality.xlQualityStandard, //可设置为 xlQualityStandard 或 xlQualityMinimum。
true, //包含文档属性
false, //如果设置为 True，则忽略在发布时设置的任何打印区域。如果设置为 False，则使用在发布时设置的打印区域。
pageFrom, //发布的起始页码。如果省略此参数，则从起始位置开始发布。
pageTo, //发布的终止页码。如果省略此参数，则发布至最后一页。
false, //是否发布文件后在查看器中显示文件。
Type.Missing);
//再还原为普通视图
excel.ActiveWindow.View = EXCEL.XlWindowView.xlNormalView;
flag = true;
}
catch (Exception exception)
{
classLims_NPOI.WriteLog(exception, "");
flag = false;
}
finally
{
if (workBook != null)
{
workBook.Close(false, missing, missing);
Marshal.ReleaseComObject(workBook);
//Marshal.FinalReleaseComObject(workBook);
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
Marshal.ReleaseComObject(workBooks);
workBook = null;
}
if (excel != null)
{
excel.Quit();
Marshal.ReleaseComObject(excel);
//Marshal.FinalReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}