//打印整个workbook
/// <summary>
/// 打印整个workbook,使用默认pdf打印机转换为pdf
/// </summary>
/// <param name="fromExcelPath">源excel路径</param>
/// <returns>成功或失败</returns>
public bool ConvertExcelWorkbookPDF(string fromExcelPath)
{
bool flag = true;
try
{
if (fromExcelPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的源文件路径不能为空。");
}
Microsoft.Office.Interop.Excel.ApplicationClass applicationClass = new Microsoft.Office.Interop.Excel.ApplicationClass();
applicationClass.GetType();
Workbooks workbooks = applicationClass.Workbooks;//.get_Workbooks();
Type type = workbooks.GetType();
object obj = fromExcelPath;
object[] objArray = new object[] { obj, true, true };
Microsoft.Office.Interop.Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)type.InvokeMember("Open", BindingFlags.InvokeMethod,
null, workbooks, objArray);
workbook.GetType();
object value = Missing.Value;
//目标路径仅在打印失败时写入,成功时都默认在打印机路径下
//故不使用目标路径,直接使用打印机默认路径
workbook.PrintOutEx(value, value, value, value, value, false, value, value, value);
//item.PrintOut(value, value, value, value, value, false, value, value);
if (workbook != null)
{
workbook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
//Marshal.FinalReleaseComObject(workBook);
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
if (applicationClass != null)
{
applicationClass.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(applicationClass);
applicationClass = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
return(flag);
}
catch (Exception exception)
{
classLims_NPOI.WriteLog(exception, "");
throw exception;
}
finally
{
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
Exc.Application appExc = new Exc.Application();
appExc.DisplayAlerts = false;
appExc.Visible = false;
appExc.SheetsInNewWorkbook = 1;
Exc.Workbook book = appExc.Workbooks.Add(1);
Exc.Worksheet sheet = (Exc.Worksheet)book.Worksheets[1];
sheet.Cells[1, 1] = "Справочник банков";
sheet.get_Range("A1", "C1").Merge();
sheet.get_Range("A1", "C1").BorderAround();
sheet.get_Range("A1", "A1").HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
sheet.get_Range("A1", "A1").Font.Bold = true;
sheet.get_Range("A1", "A1").Font.Size = 16;
sheet.Cells[3, 1] = "Выгрузил: " + Nwuram.Framework.Settings.User.UserSettings.User.FullUsername;
sheet.get_Range("A3", "C3").Merge();
sheet.Cells[4, 1] = "Дата выгрузки: " + DateTime.Now;
sheet.get_Range("A4", "C4").Merge();
sheet.Cells[6, 1] = "Наименование банка";
sheet.Cells[6, 2] = "Корреспондентский счет";
sheet.Cells[6, 3] = "БИК";
sheet.get_Range("A6", "A6").ColumnWidth = 25;
sheet.get_Range("B6", "B6").ColumnWidth = 23;
sheet.get_Range("C6", "C6").ColumnWidth = 9;
sheet.get_Range("A6", "A6").Font.Bold = true;
sheet.get_Range("B6", "B6").Font.Bold = true;
sheet.get_Range("C6", "C6").Font.Bold = true;
sheet.get_Range("A6", "C6").HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
sheet.get_Range("A6", "C6").Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
sheet.get_Range("A6", "C6").WrapText = true;
if (dgBanks.Rows.Count > 0)
{
for (int i = 0; i < dgBanks.Rows.Count; i++)
{
string A, G;
A = "A" + (i + 7);
G = "C" + (i + 7);
sheet.Cells[i + 7, 1] = dgBanks.Rows[i].Cells["cName"].Value;
sheet.Cells[i + 7, 2] = dgBanks.Rows[i].Cells["CorrespondentAccount"].Value;
sheet.Cells[i + 7, 3] = dgBanks.Rows[i].Cells["BIC"].Value;
sheet.get_Range(A, G).WrapText = true;
sheet.get_Range(A, G).VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop;
sheet.get_Range(A, G).Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
sheet.PageSetup.PrintArea = "A1:" + G;
sheet.get_Range("B6", G).NumberFormat = 0;
}
}
sheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
sheet.PageSetup.LeftMargin = 13.88;
sheet.PageSetup.RightMargin = 13.88;
sheet.PageSetup.TopMargin = 13.88;
sheet.PageSetup.BottomMargin = 13.88;
sheet.PageSetup.HeaderMargin = 0;
sheet.PageSetup.FooterMargin = 0;
appExc.Visible = true;
object[] args = new object[2];
args[0] = @_fileName;
args[1] = 39;
book.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, book, args);
}
/// <summary>
/// 按索引查找sheet,使用默认pdf打印机转换为pdf
/// </summary>
/// <param name="fromExcelPath">源excel路径</param>
/// <param name="worksheetIndex">sheet索引,注意从1开始</param>
/// <returns>成功或失败</returns>
public bool ConvertExcelWorkSheetPDF_index(string fromExcelPath, int worksheetIndex)
{
bool flag = true;
try
{
if (fromExcelPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的源文件路径不能为空。");
}
Microsoft.Office.Interop.Excel.ApplicationClass applicationClass = new Microsoft.Office.Interop.Excel.ApplicationClass();
applicationClass.GetType();
Workbooks workbooks = applicationClass.Workbooks;//.get_Workbooks();
Type type = workbooks.GetType();
object obj = fromExcelPath;
object[] objArray = new object[] { obj, true, true };
Microsoft.Office.Interop.Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)type.InvokeMember("Open", BindingFlags.InvokeMethod,
null, workbooks, objArray);
//workbooks.Open(this._sourcePath);
//Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Item[0];
workbook.GetType();
//object obj1 = "c:\\temp.ps";
//obj1 = toPdfPath;
object obj2 = "-4142";
//Microsoft.Office.Interop.Excel.Worksheet item = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Item[worksheetIndex];
Microsoft.Office.Interop.Excel.Worksheet item = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(worksheetIndex);
if (item == null)
{
flag = false;
throw new Exception("需要转换的WorkSheet名称不存在。");
}
//item.get_Cells().get_Interior().set_ColorIndex(obj2);
item.Cells.Interior.ColorIndex = obj2;//.get_Interior().set_ColorIndex(obj2);
object value = Missing.Value;
//item.PrintOut(value, value, value, value, value, true, value, obj1);
//Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;
//item.ExportAsFixedFormat(targetType, obj1, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,true,false, value, value, value, value);
//目标路径仅在打印失败时写入,成功时都默认在打印机路径下
//故不使用目标路径,直接使用打印机默认路径
item.PrintOut(value, value, value, value, value, false, value, value);
if (item != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(item);
//Marshal.FinalReleaseComObject(sheet);
item = null;
}
if (workbook != null)
{
workbook.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
//Marshal.FinalReleaseComObject(workBook);
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
if (applicationClass != null)
{
applicationClass.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(applicationClass);
applicationClass = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
return(flag);
}
catch (Exception exception)
{
classLims_NPOI.WriteLog(exception, "");
throw exception;
}
finally
{
}
}
