public void mofileexcelvualuu()
{
if (duongdanfileexcel != null)
{
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(duongdanfileexcel);
app.Visible = true;
sh.PrintOutEx();
}
}
public void taovainfileexcel(DataTable dt, string tongma, string ngaybatdau, string ngayketthuc)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet worksheet = ExcelPkg.Workbook.Worksheets.Add("hts");
worksheet.Cells["A1:C1"].Merge = true;
worksheet.Cells["A2:C2"].Merge = true;
worksheet.Cells["A3:C3"].Merge = true;
worksheet.Cells["A1"].Value = "Danh mục VM _ Mã chưa trưng";
worksheet.Cells["A2"].Value = "Từ ngày: " + ngaybatdau + " - " + ngayketthuc;
worksheet.Cells["A3"].Value = "Tổng mã chưa trưng: " + tongma;
worksheet.Cells["A5"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light1);
worksheet.Column(1).Width = 10;
worksheet.Column(2).Width = 13;
worksheet.Column(3).Width = 10;
//worksheet.Cells[worksheet.Dimension.End.Row + 1, 1].Value = "Tổng sản phẩm:";
//worksheet.Cells[worksheet.Dimension.End.Row, 2].Value = tongsp;
var allCells = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
var cellFont = allCells.Style.Font;
cellFont.SetFromFont(new Font("Calibri", 10));
worksheet.PrinterSettings.LeftMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.RightMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.TopMargin = 0.2M / 2.54M;
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
if (File.Exists("hts.xlsx"))
{
File.Delete("hts.xlsx");
}
ExcelPkg.SaveAs(new FileInfo("hts.xlsx"));
ExcelPkg.Dispose();
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(Path.GetFullPath("hts.xlsx"));
//app.Visible = true;
sh.PrintOutEx();
app.Quit();
Marshal.FinalReleaseComObject(app);
Marshal.FinalReleaseComObject(book);
}
public static void taovainfileexcelchuyenhang(DataTable dt, string tongsp, string noinhan, string tencuahang)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet worksheet = ExcelPkg.Workbook.Worksheets.Add("hts");
worksheet.Cells["A1"].Value = tencuahang + " _ Điều chuyển";
worksheet.Cells["A2"].Value = "Đến : " + noinhan;
worksheet.Cells["A3"].Value = "Ngày tạo : " + DateTime.Now.ToString("dd-MM-yyyy");
worksheet.Cells["A4"].Value = "Tổng SP : " + tongsp + " sp";
worksheet.Cells["A6"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light1);
worksheet.Column(1).Width = 28;
worksheet.Column(2).Width = 4;
worksheet.Cells[worksheet.Dimension.End.Row + 1, 1].Value = "Tổng sản phẩm:";
worksheet.Cells[worksheet.Dimension.End.Row, 2].Value = tongsp;
var allCells = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
var cellFont = allCells.Style.Font;
cellFont.SetFromFont(new Font("Calibri", 10));
worksheet.PrinterSettings.LeftMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.RightMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.TopMargin = 0.2M / 2.54M;
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
if (File.Exists("hts.xlsx"))
{
File.Delete("hts.xlsx");
}
ExcelPkg.SaveAs(new FileInfo("hts.xlsx"));
ExcelPkg.Dispose();
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(Path.GetFullPath("hts.xlsx"));
//app.Visible = true;
sh.PrintOutEx();
book.Close();
app.Quit();
}
public static void taovainfileexcel(DataTable dt, string tongsp, int sobanin = 1)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet worksheet = ExcelPkg.Workbook.Worksheets.Add("hts");
worksheet.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light1);
worksheet.Column(1).Width = 28;
worksheet.Column(2).Width = 4;
worksheet.Cells[worksheet.Dimension.End.Row + 1, 1].Value = "Tổng sản phẩm:";
worksheet.Cells[worksheet.Dimension.End.Row, 2].Value = tongsp;
var allCells = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
var cellFont = allCells.Style.Font;
cellFont.SetFromFont(new Font("Calibri", 11));
worksheet.PrinterSettings.LeftMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.RightMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.TopMargin = 0.2M / 2.54M;
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
if (File.Exists("hts.xlsx"))
{
File.Delete("hts.xlsx");
}
ExcelPkg.SaveAs(new FileInfo("hts.xlsx"));
ExcelPkg.Dispose();
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(Path.GetFullPath("hts.xlsx"));
//app.Visible = true;
for (int i = 0; i < sobanin; i++)
{
sh.PrintOutEx();
}
book.Close();
app.Quit();
}
public static void taovainfileexceltheoPhieu(DataTable dt, string sophieu, string noidung, string soluong, string ngay)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet worksheet = ExcelPkg.Workbook.Worksheets.Add("hts");
worksheet.Cells["A1"].Value = "Số phiếu: " + sophieu;
worksheet.Cells["A2"].Value = "Nội dung: " + noidung;
worksheet.Cells["A3"].Value = "Tổng SL: " + soluong + "sp";
worksheet.Cells["A4"].Value = "Ngày/Phiếu: " + ngay;
worksheet.Cells["A6"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light1);
worksheet.Column(1).Width = 28;
worksheet.Column(2).Width = 4;
var allCells = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
var cellFont = allCells.Style.Font;
cellFont.SetFromFont(new Font("Calibri", 10));
worksheet.PrinterSettings.LeftMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.RightMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.TopMargin = 0.2M / 2.54M;
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
if (File.Exists("hts.xlsx"))
{
File.Delete("hts.xlsx");
}
ExcelPkg.SaveAs(new FileInfo("hts.xlsx"));
ExcelPkg.Dispose();
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(Path.GetFullPath("hts.xlsx"));
//app.Visible = true;
sh.PrintOutEx();
book.Close();
app.Quit();
}
public void taovainfileexcelchuyenhang(DataTable dt, string tongsp)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet worksheet = ExcelPkg.Workbook.Worksheets.Add("hts");
worksheet.Cells["A1"].Value = "27 Lê Văn Lương _ Điều chuyển";
worksheet.Cells["A3"].LoadFromDataTable(dt, true);
worksheet.Column(1).Width = 28;
worksheet.Column(2).Width = 4;
worksheet.Cells[worksheet.Dimension.End.Row + 1, 1].Value = "Tổng sản phẩm:";
worksheet.Cells[worksheet.Dimension.End.Row, 2].Value = tongsp;
var allCells = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
var cellFont = allCells.Style.Font;
cellFont.SetFromFont(new Font("Calibri", 10));
worksheet.PrinterSettings.LeftMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.RightMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.TopMargin = 0.2M / 2.54M;
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
if (File.Exists("hts.xlsx"))
{
File.Delete("hts.xlsx");
}
ExcelPkg.SaveAs(new FileInfo("hts.xlsx"));
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(Path.GetFullPath("hts.xlsx"));
//app.Visible = true;
sh.PrintOutEx();
app.Quit();
}
// Main conversion routine
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Object activeSheet = null;
Window activeWindow = null;
Windows wbWin = null;
Hashtable templatePageSetup = new Hashtable();
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
// Excel can be very slow to start up, so try to get the COM
// object a few times
int tries = 10;
app = new Microsoft.Office.Interop.Excel.Application();
while (tries > 0)
{
try
{
// Try to set a property on the object
app.ScreenUpdating = false;
}
catch (COMException)
{
// Decrement the number of tries and have a bit of a snooze
tries--;
Thread.Sleep(500);
continue;
}
// Looks ok, so bail out of the loop
break;
}
if (tries == 0)
{
ReleaseCOMObject(app);
return((int)ExitCode.ApplicationError);
}
app.Visible = true;
app.DisplayAlerts = false;
app.AskToUpdateLinks = false;
app.AlertBeforeOverwriting = false;
app.EnableLargeOperationAlert = false;
app.Interactive = false;
app.FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone;
var onlyActiveSheet = (Boolean)options["excel_active_sheet"];
Boolean includeProps = !(Boolean)options["excludeprops"];
Boolean skipRecalculation = (Boolean)options["excel_no_recalculate"];
Boolean showHeadings = (Boolean)options["excel_show_headings"];
Boolean showFormulas = (Boolean)options["excel_show_formulas"];
Boolean isHidden = (Boolean)options["hidden"];
Boolean screenQuality = (Boolean)options["screen"];
Boolean updateLinks = !(Boolean)options["excel_no_link_update"];
int maxRows = (int)options[@"excel_max_rows"];
int worksheetNum = (int)options["excel_worksheet"];
int sheetForConversionIdx = 0;
activeWindow = app.ActiveWindow;
Sheets allSheets = null;
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if (isHidden)
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
// If we have no write password and we're attempting to open for writing, we might be
// caught out by an unexpected write password
if (writePassword == "" && !nowrite)
{
oWritePass = (Object)"FAKEPASSWORD";
try
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
catch (System.Runtime.InteropServices.COMException)
{
// Attempt to open it in read-only mode
workbook = workbooks.Open(inputFile, updateLinks, true, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
}
else
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
// Add in a delay to let Excel sort itself out
AddCOMDelay(options);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Get any template options
SetPageOptionsFromTemplate(app, workbooks, options, ref templatePageSetup);
// Get the sheets
allSheets = workbook.Sheets;
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
// We will save as xlsb (binary format) since this doesn't raise some errors when processing
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xlsb");
fmt = XlFileFormat.xlExcel12;
// Set up the print quality
if (screenQuality)
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// If a worksheet has been specified, try and use just the one
if (worksheetNum > 0)
{
// Force us just to use the active sheet
onlyActiveSheet = true;
try
{
if (worksheetNum > allSheets.Count)
{
// Sheet count is too big
return((int)ExitCode.WorksheetNotFound);
}
if (allSheets[worksheetNum] is _Worksheet)
{
((_Worksheet)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Worksheet)allSheets[worksheetNum]).Index;
}
else if (allSheets[worksheetNum] is _Chart)
{
((_Chart)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Chart)allSheets[worksheetNum]).Index;
}
}
catch (Exception)
{
return((int)ExitCode.WorksheetNotFound);
}
}
if (showFormulas)
{
// Determine whether to show formulas
try
{
activeWindow.DisplayFormulas = true;
}
catch (Exception) { }
}
// Keep the windows hidden
if (isHidden)
{
wbWin = workbook.Windows;
if (null != wbWin)
{
if (wbWin.Count > 0)
{
wbWin[1].Visible = false;
}
}
if (null != activeWindow)
{
activeWindow.Visible = false;
}
}
// Keep track of the active sheet
if (workbook.ActiveSheet != null)
{
activeSheet = workbook.ActiveSheet;
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (maxRows > 0 || showFormulas || showHeadings)
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
// Loop through all the sheets (worksheets and charts)
for (int wsIdx = 1; wsIdx <= allSheets.Count; wsIdx++)
{
var ws = allSheets.Item[wsIdx];
// Skip anything that is not the active sheet
if (onlyActiveSheet)
{
// Have to be careful to treat _Worksheet and _Chart items differently
try
{
int itemIndex = 1;
if (activeSheet is _Worksheet)
{
itemIndex = ((Worksheet)activeSheet).Index;
}
else if (activeSheet is _Chart)
{
itemIndex = ((Microsoft.Office.Interop.Excel.Chart)activeSheet).Index;
}
if (wsIdx != itemIndex)
{
ReleaseCOMObject(ws);
continue;
}
}
catch (Exception)
{
if (ws != null)
{
ReleaseCOMObject(ws);
}
continue;
}
sheetForConversionIdx = wsIdx;
}
if (showHeadings && ws is _Worksheet)
{
PageSetup pageSetup = null;
try
{
pageSetup = ((Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
}
catch (Exception) { }
finally
{
ReleaseCOMObject(pageSetup);
}
}
// If showing formulas, make things auto-fit
if (showFormulas && ws is _Worksheet)
{
Range cols = null;
try
{
((_Worksheet)ws).Activate();
activeWindow.DisplayFormulas = true;
cols = ((Worksheet)ws).Columns;
cols.AutoFit();
}
catch (Exception) { }
finally
{
ReleaseCOMObject(cols);
}
}
// If there is a maximum row count, make sure we check each worksheet
if (maxRows > 0 && ws is _Worksheet)
{
// Check for a print area
var pageSetup = ((Worksheet)ws).PageSetup;
var printArea = pageSetup.PrintArea;
ReleaseCOMObject(pageSetup);
if (string.IsNullOrEmpty(printArea))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > maxRows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Worksheet)ws).Name;
}
ReleaseCOMObject(cellSearch);
}
ReleaseCOMObject(cells);
}
ReleaseCOMObject(rows);
}
ReleaseCOMObject(range);
if (row_count > maxRows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.ReleaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.ReleaseCOMObject(ws);
}
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
// Allow for re-calculation to be skipped
if (skipRecalculation)
{
app.Calculation = XlCalculation.xlCalculationManual;
app.CalculateBeforeSave = false;
}
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
if (onlyActiveSheet)
{
// Set up a delegate function for times we want to print
PrintDocument printFunc = delegate(string destination, string printer)
{
((Worksheet)activeSheet).PrintOutEx(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (sheetForConversionIdx > 0)
{
activeSheet = allSheets.Item[sheetForConversionIdx];
}
if (activeSheet is _Worksheet)
{
var wps = ((_Worksheet)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
((Worksheet)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
ReleaseCOMObject(wps);
}
else if (activeSheet is _Chart)
{
var wps = ((_Chart)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
((Microsoft.Office.Interop.Excel.Chart)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
ReleaseCOMObject(wps);
}
else
{
return((int)ExitCode.UnknownError);
}
AddCOMDelay(options);
}
else
{
PrintDocument printFunc = delegate(string destination, string printer)
{
workbook.PrintOutEx(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (HasTemplateOption(options))
{
// Set up the template page setup options on all the worksheets
// in the workbook
var worksheets = workbook.Worksheets;
for (int wsIdx = 1; wsIdx <= worksheets.Count; wsIdx++)
{
var ws = worksheets[wsIdx];
var wps = (ws is _Worksheet) ? ((_Worksheet)ws).PageSetup : ((_Chart)ws).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
ReleaseCOMObject(wps);
ReleaseCOMObject(ws);
}
ReleaseCOMObject(worksheets);
}
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
}
ReleaseCOMObject(allSheets);
ReleaseCOMObject(fmt);
ReleaseCOMObject(quality);
return((int)ExitCode.Success);
}
catch (COMException ce)
{
if ((uint)ce.ErrorCode == 0x800A03EC)
{
return((int)ExitCode.EmptyWorksheet);
}
else
{
Console.WriteLine(ce.Message);
return((int)ExitCode.UnknownError);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
ReleaseCOMObject(activeSheet);
ReleaseCOMObject(activeWindow);
ReleaseCOMObject(wbWin);
GC.Collect();
GC.WaitForPendingFinalizers();
// Excel sometimes needs a bit of a delay before we close in order to
// let things get cleaned up
workbook.Saved = true;
CloseExcelWorkbook(workbook);
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
ReleaseCOMObject(workbook);
ReleaseCOMObject(workbooks);
ReleaseCOMObject(app);
GC.Collect();
GC.WaitForPendingFinalizers();
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
public static void ExportTable3(string saveFileName, List <sp_SelectTable3ForExcel_Result> dataRows, string datetime, string dsn, string company, List <MessageInfo> messages)
{
try
{
string fileName;
fileName = AppDomain.CurrentDomain.BaseDirectory + "Excel\\Table33.xlsx";
EX.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
EX.Workbook book = app.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
EX.Worksheet sheet = (EX.Worksheet)book.Worksheets[1];
sheet.Cells[1, "A"] = datetime;
//string[] dsnArray = dsn.Split('-');
//if (dsnArray.Length<3)
//{
// Log.WriteLog.WriteOperateLog("导出失败，DSN为：" + dsn);
// MessageBox.Show("导出失败，DSN为：" + dsn);
// return;
//}
//sheet.Cells[1, "E"] = company + ("-") + dsnArray[1] + ("-") + dsnArray[2];
sheet.Cells[1, "E"] = dsn;
sheet.Cells[1, "H"] = company;
//sheet.Cells[1, "I"] = MainContext.UserCompanyName;
int row = 3;
foreach (var dataRow in dataRows)
{
row++;
sheet.Cells[row, "A"] = row - 3;
sheet.Cells[row, "B"] = dataRow.Name;
sheet.Cells[row, "C"] = dataRow.BirthDay;
sheet.Cells[row, "D"] = dataRow.Sex;
sheet.Cells[row, "E"] = dataRow.FBirthNationlityPresentEn;
sheet.Cells[row, "F"] = dataRow.FBirthNationlityEn;
sheet.Cells[row, "G"] = "'" + dataRow.PID;
//sheet.Cells[row, "G"] = dataRow.Work;
sheet.Cells[row, "H"] = ((DateTime)(dataRow.FPassportValidDate)).ToString("dd/MM/yyyy");
sheet.Cells[row, "I"] = dataRow.CQJDate;
sheet.Cells[row, "J"] = dataRow.TimesName;
sheet.Cells[row, "K"] = dataRow.FCreateCompany;
sheet.Cells[row, "L"] = dataRow.FSysMemo;
EX.Range range = sheet.Range[sheet.Cells[row, "A"], sheet.Cells[row, "L"]];
switch (dataRow.iType)
{
case 1:
break;
case 2:
range.Font.ColorIndex = 10; //绿色
break;
case 3:
range.Font.ColorIndex = 5; // 蓝色
break;
case 4:
range.Font.ColorIndex = 3; // 红色
break;
case 5:
range.Font.ColorIndex = 10; //绿色
break;
case 6:
case 7:
range.Font.ColorIndex = 5;
break;
case 8:
range.Font.ColorIndex = 3;
break;
case 9:
range.Font.ColorIndex = 45; //橙色
break;
case 10:
range.Font.ColorIndex = 5;
break;
case 11:
range.Font.ColorIndex = 26; //粉红色
break;
case 12:
range.Font.ColorIndex = 10; //绿色
break;
case 13:
range.Font.ColorIndex = 26;
break;
case 14:
range.Font.ColorIndex = 5;
break;
case 15:
range.Font.ColorIndex = 3;
break;
case 16:
range.Font.ColorIndex = 16;
break;
case 17:
range.Font.ColorIndex = 5;
break;
case 18:
range.Font.ColorIndex = 3;
break;
case 19:
range.Font.ColorIndex = 5;
break;
case 20:
range.Font.ColorIndex = 3;
break;
case 21:
case 22:
case 23:
case 24:
case 27:
case 28:
case 29:
case 30:
case 31:
case 32:
range.Font.ColorIndex = 16;
break;
case 25:
case 26:
break;
default:
break;
}
}
EX.Range range22 = sheet.Range[sheet.Cells[2, "A"], sheet.Cells[row, "L"]];
NewMethod(range22, Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft);
NewMethod(range22, Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop);
NewMethod(range22, Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom);
NewMethod(range22, Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight);
NewMethod(range22, Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical);
NewMethod(range22, Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal);
foreach (MessageInfo info in messages)
{
row++;
EX.Range range = sheet.Range[sheet.Cells[row, "B"], sheet.Cells[row, "I"]];
range.Merge(Missing.Value);
sheet.Cells[row, "B"] = info.message;
switch (info.iType)
{
case 1:
break;
case 2:
range.Font.ColorIndex = 10; //绿色
break;
case 3:
range.Font.ColorIndex = 5; // 蓝色
break;
case 4:
range.Font.ColorIndex = 3; // 红色
break;
case 5:
range.Font.ColorIndex = 10; //绿色
break;
case 6:
case 7:
range.Font.ColorIndex = 5;
break;
case 8:
range.Font.ColorIndex = 3;
break;
case 9:
range.Font.ColorIndex = 45; //橙色
break;
case 10:
range.Font.ColorIndex = 5;
break;
case 11:
range.Font.ColorIndex = 26; //粉红色
break;
case 12:
range.Font.ColorIndex = 10; //绿色
break;
case 13:
range.Font.ColorIndex = 26;
break;
case 14:
range.Font.ColorIndex = 5;
break;
case 15:
range.Font.ColorIndex = 3;
break;
case 16:
range.Font.ColorIndex = 16;
break;
case 17:
range.Font.ColorIndex = 5;
break;
case 18:
range.Font.ColorIndex = 3;
break;
case 19:
range.Font.ColorIndex = 5;
break;
case 20:
range.Font.ColorIndex = 3;
break;
case 21:
case 22:
case 23:
case 24:
case 27:
case 28:
case 29:
case 30:
case 31:
case 32:
range.Font.ColorIndex = 16;
break;
case 25:
case 26:
break;
default:
break;
}
}
book.SaveAs(saveFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//book.PrintPreview();
book.PrintOutEx();
book.Close(Missing.Value, Missing.Value, Missing.Value);
IntPtr t = new IntPtr(app.Hwnd);
int id = 0;
GetWindowThreadProcessId(t, out id);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(id);
p.Kill();
MessageBox.Show("导出成功，生成的文件位置为：" + saveFileName);
}
catch (System.Exception ex)
{
Log.WriteLog.WriteErorrLog(ex);
MessageBox.Show(ex.ToString());
}
}
///----------------------------------------------------------------------
/// <summary>
/// ＦＡＸ注文書印刷処理 </summary>
/// <param name="prnName">
/// プリンタ名</param>
/// <param name="copies">
/// 印刷部数</param>
/// <param name="ptof">
/// ファイルに出力</param>
///----------------------------------------------------------------------
private void prnSheet(string prnName, int copies, bool ptof)
{
//マウスポインタを待機にする
this.Cursor = Cursors.WaitCursor;
// Excel起動
string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory;
Excel.Application oXls = new Excel.Application();
Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(Properties.Settings.Default.xlsFaxPattern, 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.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1];
Excel.Worksheet oxlsMsSheet = (Excel.Worksheet)oXlsBook.Sheets[1]; // テンプレートシート
oxlsSheet.Select(Type.Missing);
Excel.Range rng = null;
int pCnt = 1; // ページカウント
//int bCount = 0; // progressBar部署カウント
object[,] rtnArray = null;
try
{
//// progressBar
//toolStripProgressBar1.Maximum = 100;
//toolStripProgressBar1.Minimum = 0;
//toolStripProgressBar1.Visible = true;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
// チェックされている部署を対象とする
if (dataGridView1[colChk, i].Value.ToString() == "False")
{
continue;
}
// テンプレートシートを追加する
pCnt++;
oxlsMsSheet.Copy(Type.Missing, oXlsBook.Sheets[pCnt - 1]);
oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[pCnt];
// シートのセルを一括して配列に取得します
rng = oxlsMsSheet.Range[oxlsMsSheet.Cells[1, 1], oxlsMsSheet.Cells[oxlsMsSheet.UsedRange.Rows.Count, oxlsMsSheet.UsedRange.Columns.Count]];
rtnArray = (object[, ])rng.Value2;
// パターンＩＤ
string pPID = dataGridView1[colPtnID, i].Value.ToString().PadLeft(4, '0');
rtnArray[4, 32] = pPID.Substring(0, 1);
rtnArray[4, 38] = pPID.Substring(1, 1);
rtnArray[4, 44] = pPID.Substring(2, 1);
rtnArray[4, 50] = pPID.Substring(3, 1);
// お客様コード（届先番号）
pPID = dataGridView1[colNouCode, i].Value.ToString().PadLeft(6, '0');
rtnArray[4, 87] = pPID.Substring(0, 1);
rtnArray[4, 93] = pPID.Substring(1, 1);
rtnArray[4, 99] = pPID.Substring(2, 1);
rtnArray[4, 105] = pPID.Substring(3, 1);
rtnArray[4, 111] = pPID.Substring(4, 1);
rtnArray[4, 117] = pPID.Substring(5, 1);
// お客様名
rtnArray[8, 29] = Utility.NulltoStr(dataGridView1[colNouName, i].Value);
var s = dts.パターンID.Single(a => a.ID == Utility.StrtoInt(dataGridView1[colID, i].Value.ToString()));
// 商品名
string sIrisu = string.Empty;
string sTani = string.Empty;
rtnArray[16, 2] = getVSyohin(s.商品1, out sIrisu, out sTani);
rtnArray[17, 44] = sIrisu;
rtnArray[16, 73] = sTani;
if (s.商品1 != global.flgOff)
{
rtnArray[17, 2] = s.商品1.ToString("D8");
}
rtnArray[18, 2] = getVSyohin(s.商品2, out sIrisu, out sTani);
rtnArray[19, 44] = sIrisu;
rtnArray[18, 73] = sTani;
if (s.商品2 != global.flgOff)
{
rtnArray[19, 2] = s.商品2.ToString("D8");
}
rtnArray[20, 2] = getVSyohin(s.商品3, out sIrisu, out sTani);
rtnArray[21, 44] = sIrisu;
rtnArray[20, 73] = sTani;
if (s.商品3 != global.flgOff)
{
rtnArray[21, 2] = s.商品3.ToString("D8");
}
rtnArray[22, 2] = getVSyohin(s.商品4, out sIrisu, out sTani);
rtnArray[23, 44] = sIrisu;
rtnArray[22, 73] = sTani;
if (s.商品4 != global.flgOff)
{
rtnArray[23, 2] = s.商品4.ToString("D8");
}
rtnArray[24, 2] = getVSyohin(s.商品5, out sIrisu, out sTani);
rtnArray[25, 44] = sIrisu;
rtnArray[24, 73] = sTani;
if (s.商品5 != global.flgOff)
{
rtnArray[25, 2] = s.商品5.ToString("D8");
}
rtnArray[26, 2] = getVSyohin(s.商品6, out sIrisu, out sTani);
rtnArray[27, 44] = sIrisu;
rtnArray[26, 73] = sTani;
if (s.商品6 != global.flgOff)
{
rtnArray[27, 2] = s.商品6.ToString("D8");
}
rtnArray[28, 2] = getVSyohin(s.商品7, out sIrisu, out sTani);
rtnArray[29, 44] = sIrisu;
rtnArray[28, 73] = sTani;
if (s.商品7 != global.flgOff)
{
rtnArray[29, 2] = s.商品7.ToString("D8");
}
rtnArray[30, 2] = getVSyohin(s.商品8, out sIrisu, out sTani);
rtnArray[31, 44] = sIrisu;
rtnArray[30, 73] = sTani;
if (s.商品8 != global.flgOff)
{
rtnArray[31, 2] = s.商品8.ToString("D8");
}
rtnArray[32, 2] = getVSyohin(s.商品9, out sIrisu, out sTani);
rtnArray[33, 44] = sIrisu;
rtnArray[32, 73] = sTani;
if (s.商品9 != global.flgOff)
{
rtnArray[33, 2] = s.商品9.ToString("D8");
}
rtnArray[34, 2] = getVSyohin(s.商品10, out sIrisu, out sTani);
rtnArray[35, 44] = sIrisu;
rtnArray[34, 73] = sTani;
if (s.商品10 != global.flgOff)
{
rtnArray[35, 2] = s.商品10.ToString("D8");
}
rtnArray[36, 2] = getVSyohin(s.商品11, out sIrisu, out sTani);
rtnArray[37, 44] = sIrisu;
rtnArray[36, 73] = sTani;
if (s.商品11 != global.flgOff)
{
rtnArray[37, 2] = s.商品11.ToString("D8");
}
rtnArray[38, 2] = getVSyohin(s.商品12, out sIrisu, out sTani);
rtnArray[39, 44] = sIrisu;
rtnArray[38, 73] = sTani;
if (s.商品12 != global.flgOff)
{
rtnArray[39, 2] = s.商品12.ToString("D8");
}
rtnArray[40, 2] = getVSyohin(s.商品13, out sIrisu, out sTani);
rtnArray[41, 44] = sIrisu;
rtnArray[40, 73] = sTani;
if (s.商品13 != global.flgOff)
{
rtnArray[41, 2] = s.商品13.ToString("D8");
}
rtnArray[42, 2] = getVSyohin(s.商品14, out sIrisu, out sTani);
rtnArray[43, 44] = sIrisu;
rtnArray[42, 73] = sTani;
if (s.商品14 != global.flgOff)
{
rtnArray[43, 2] = s.商品14.ToString("D8");
}
rtnArray[44, 2] = getVSyohin(s.商品15, out sIrisu, out sTani);
rtnArray[45, 44] = sIrisu;
rtnArray[44, 73] = sTani;
if (s.商品15 != global.flgOff)
{
rtnArray[45, 2] = s.商品15.ToString("D8");
}
rtnArray[16, 77] = getVSyohin(s.商品16, out sIrisu, out sTani);
rtnArray[17, 119] = sIrisu;
rtnArray[16, 148] = sTani;
if (s.商品16 != global.flgOff)
{
rtnArray[17, 77] = s.商品16.ToString("D8");
}
rtnArray[18, 77] = getVSyohin(s.商品17, out sIrisu, out sTani);
rtnArray[19, 119] = sIrisu;
rtnArray[18, 148] = sTani;
if (s.商品17 != global.flgOff)
{
rtnArray[19, 77] = s.商品17.ToString("D8");
}
rtnArray[20, 77] = getVSyohin(s.商品18, out sIrisu, out sTani);
rtnArray[21, 119] = sIrisu;
rtnArray[20, 148] = sTani;
if (s.商品18 != global.flgOff)
{
rtnArray[21, 77] = s.商品18.ToString("D8");
}
rtnArray[22, 77] = getVSyohin(s.商品19, out sIrisu, out sTani);
rtnArray[23, 119] = sIrisu;
rtnArray[22, 148] = sTani;
if (s.商品19 != global.flgOff)
{
rtnArray[23, 77] = s.商品19.ToString("D8");
}
rtnArray[24, 77] = getVSyohin(s.商品20, out sIrisu, out sTani);
rtnArray[25, 119] = sIrisu;
rtnArray[24, 148] = sTani;
if (s.商品20 != global.flgOff)
{
rtnArray[25, 77] = s.商品20.ToString("D8");
}
rtnArray[26, 77] = getVSyohin(s.商品21, out sIrisu, out sTani);
rtnArray[27, 119] = sIrisu;
rtnArray[26, 148] = sTani;
if (s.商品21 != global.flgOff)
{
rtnArray[27, 77] = s.商品21.ToString("D8");
}
rtnArray[28, 77] = getVSyohin(s.商品22, out sIrisu, out sTani);
rtnArray[29, 119] = sIrisu;
rtnArray[28, 148] = sTani;
if (s.商品22 != global.flgOff)
{
rtnArray[29, 77] = s.商品22.ToString("D8");
}
rtnArray[30, 77] = getVSyohin(s.商品23, out sIrisu, out sTani);
rtnArray[31, 119] = sIrisu;
rtnArray[30, 148] = sTani;
if (s.商品23 != global.flgOff)
{
rtnArray[31, 77] = s.商品23.ToString("D8");
}
rtnArray[32, 77] = getVSyohin(s.商品24, out sIrisu, out sTani);
rtnArray[33, 119] = sIrisu;
rtnArray[32, 148] = sTani;
if (s.商品24 != global.flgOff)
{
rtnArray[33, 77] = s.商品24.ToString("D8");
}
rtnArray[34, 77] = getVSyohin(s.商品25, out sIrisu, out sTani);
rtnArray[35, 119] = sIrisu;
rtnArray[34, 148] = sTani;
if (s.商品25 != global.flgOff)
{
rtnArray[35, 77] = s.商品25.ToString("D8");
}
rtnArray[36, 77] = getVSyohin(s.商品26, out sIrisu, out sTani);
rtnArray[37, 119] = sIrisu;
rtnArray[36, 148] = sTani;
if (s.商品26 != global.flgOff)
{
rtnArray[37, 77] = s.商品26.ToString("D8");
}
rtnArray[38, 77] = getVSyohin(s.商品27, out sIrisu, out sTani);
rtnArray[39, 119] = sIrisu;
rtnArray[38, 148] = sTani;
if (s.商品27 != global.flgOff)
{
rtnArray[39, 77] = s.商品27.ToString("D8");
}
rtnArray[40, 77] = getVSyohin(s.商品28, out sIrisu, out sTani);
rtnArray[41, 119] = sIrisu;
rtnArray[40, 148] = sTani;
if (s.商品28 != global.flgOff)
{
rtnArray[41, 77] = s.商品28.ToString("D8");
}
rtnArray[42, 77] = getVSyohin(s.商品29, out sIrisu, out sTani);
rtnArray[43, 119] = sIrisu;
rtnArray[42, 148] = sTani;
if (s.商品29 != global.flgOff)
{
rtnArray[43, 77] = s.商品29.ToString("D8");
}
rtnArray[44, 77] = getVSyohin(s.商品30, out sIrisu, out sTani);
rtnArray[45, 119] = sIrisu;
rtnArray[44, 148] = sTani;
if (s.商品30 != global.flgOff)
{
rtnArray[45, 77] = s.商品30.ToString("D8");
}
// 備考
rtnArray[62, 2] = s.備考;
// 配列からシートセルに一括してデータをセットします
rng = oxlsSheet.Range[oxlsSheet.Cells[1, 1], oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, oxlsSheet.UsedRange.Columns.Count]];
rng.Value2 = rtnArray;
}
// 確認のためExcelのウィンドウを表示する
oXls.Visible = true;
// 1枚目はテンプレートシートなので印刷時には削除する
oXls.DisplayAlerts = false;
oXlsBook.Sheets[1].Delete();
//System.Threading.Thread.Sleep(1000);
// 印刷
oXlsBook.PrintOutEx(Type.Missing, Type.Missing, copies, true, prnName, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//if (ptof)
//{
// if (copies > 1)
// {
// int iX = 1;
// while (iX <= copies)
// {
// oXlsBook.PrintOutEx(Type.Missing, Type.Missing, Type.Missing, true, prnName, ptof, Type.Missing, Type.Missing, Type.Missing);
// iX++;
// }
// }
// else
// {
// oXlsBook.PrintOutEx(Type.Missing, Type.Missing, copies, true, prnName, ptof, Type.Missing, Type.Missing, Type.Missing);
// }
//}
//else
//{
// oXlsBook.PrintOutEx(Type.Missing, Type.Missing, copies, true, prnName, ptof, Type.Missing, Type.Missing, Type.Missing);
//}
// 確認のためExcelのウィンドウを非表示にする
oXls.Visible = false;
// 終了メッセージ
MessageBox.Show("終了しました");
}
catch (Exception e)
{
MessageBox.Show(e.Message, "印刷処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
// ウィンドウを非表示にする
oXls.Visible = false;
// 保存処理
oXls.DisplayAlerts = false;
// Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
// Excelを終了
oXls.Quit();
// COM オブジェクトの参照カウントを解放する
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsMsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls);
oXls = null;
oXlsBook = null;
oxlsSheet = null;
oxlsMsSheet = null;
GC.Collect();
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
}
///----------------------------------------------------------------------
/// <summary>
/// ＦＡＸ注文書印刷処理 </summary>
/// <param name="prnName">
/// プリンタ名</param>
/// <param name="copies">
/// 印刷部数</param>
/// <param name="ptof">
/// ファイルに出力</param>
///----------------------------------------------------------------------
private void prnSheet(string prnName, int copies, bool ptof)
{
//マウスポインタを待機にする
this.Cursor = Cursors.WaitCursor;
// Excel起動
string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory;
Excel.Application oXls = new Excel.Application();
Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(Properties.Settings.Default.FAX注文書, 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.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1];
Excel.Worksheet oxlsMsSheet = (Excel.Worksheet)oXlsBook.Sheets[1]; // テンプレートシート
oxlsSheet.Select(Type.Missing);
Excel.Range rng = null;
Excel.Range rngFormura = null;
int pCnt = 1; // ページカウント
object[,] rtnArray = null;
try
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
// チェックされている行を対象とする
if (dataGridView1[colChk, i].Value.ToString() == "False")
{
continue;
}
// テンプレートシートを追加する
pCnt++;
oxlsMsSheet.Copy(Type.Missing, oXlsBook.Sheets[pCnt - 1]);
oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[pCnt];
// シートのセルを一括して配列に取得します
rng = oxlsMsSheet.Range[oxlsMsSheet.Cells[1, 1], oxlsMsSheet.Cells[oxlsMsSheet.UsedRange.Rows.Count, oxlsMsSheet.UsedRange.Columns.Count]];
//rng.Value2 = "";
//rtnArray = (object[,])rng.Value2;
rtnArray = rng.Value2;
rtnArray = rng.Formula;
// 年月
rtnArray[1, 1] = txtYear.Text.Substring(0, 1);
rtnArray[1, 7] = txtYear.Text.Substring(1, 1);
rtnArray[1, 13] = txtYear.Text.Substring(2, 1);
rtnArray[1, 19] = txtYear.Text.Substring(3, 1);
rtnArray[1, 31] = txtMonth.Text.PadLeft(2, '0').Substring(0, 1);
rtnArray[1, 37] = txtMonth.Text.PadLeft(2, '0').Substring(1, 1);
// パターンＩＤ
string pPID = dataGridView1[colPtnID, i].Value.ToString().PadLeft(3, '0');
rtnArray[1, 79] = pPID.Substring(0, 1);
rtnArray[1, 85] = pPID.Substring(1, 1);
rtnArray[1, 91] = pPID.Substring(2, 1);
// 枝番
pPID = dataGridView1[colSecoundNum, i].Value.ToString().PadLeft(2, '0');
rtnArray[3, 121] = pPID.Substring(0, 1);
rtnArray[3, 127] = pPID.Substring(1, 1);
// 得意先コード
pPID = dataGridView1[colNouCode, i].Value.ToString().PadLeft(7, '0');
rtnArray[1, 121] = pPID.Substring(0, 1);
rtnArray[1, 127] = pPID.Substring(1, 1);
rtnArray[1, 133] = pPID.Substring(2, 1);
rtnArray[1, 139] = pPID.Substring(3, 1);
rtnArray[1, 145] = pPID.Substring(4, 1);
rtnArray[1, 151] = pPID.Substring(5, 1);
rtnArray[1, 157] = pPID.Substring(6, 1);
// 得意先名
rtnArray[3, 31] = Utility.NulltoStr(dataGridView1[colNouName, i].Value);
//// 更新日：2018/11/01
//DateTime uDt;
//if (DateTime.TryParse(dataGridView1[colDate, i].Value.ToString(), out uDt))
//{
// rtnArray[15, 2] = uDt.ToShortDateString() + " 更新";
//}
//else
//{
// rtnArray[15, 2] = string.Empty;
//}
ClsOrderPattern = dbPtn.Single(a => a.ID == Utility.StrtoInt(dataGridView1[colID, i].Value.ToString()));
// コメント１：2020/04/01
if (Utility.NulltoStr(ClsOrderPattern.comment1) != string.Empty)
{
rtnArray[5, 1] = Utility.NulltoStr(ClsOrderPattern.comment1);
}
// 会社名：2020/04/01
if (Utility.NulltoStr(ClsOrderPattern.comment2) != string.Empty)
{
rtnArray[3, 133] = Utility.NulltoStr(ClsOrderPattern.comment2);
}
// 担当者：2020/04/01
if (Utility.NulltoStr(ClsOrderPattern.comment3) != string.Empty)
{
rtnArray[4, 133] = Utility.NulltoStr(ClsOrderPattern.comment3);
}
// FAX：2020/04/01
if (Utility.NulltoStr(ClsOrderPattern.comment4) != string.Empty)
{
rtnArray[5, 133] = Utility.NulltoStr(ClsOrderPattern.comment4);
}
string sIrisu = string.Empty;
string sTani = string.Empty;
string _G_Code = "";
int _R_Days = 0;
int xR = 9;
for (int r = 0; r < global.MAX_GYO; r++)
{
switch (r)
{
case 0:
_G_Code = ClsOrderPattern.G_Code1;
_R_Days = ClsOrderPattern.G_Read1;
break;
case 1:
_G_Code = ClsOrderPattern.G_Code2;
_R_Days = ClsOrderPattern.G_Read2;
break;
case 2:
_G_Code = ClsOrderPattern.G_Code3;
_R_Days = ClsOrderPattern.G_Read3;
break;
case 3:
_G_Code = ClsOrderPattern.G_Code4;
_R_Days = ClsOrderPattern.G_Read4;
break;
case 4:
_G_Code = ClsOrderPattern.G_Code5;
_R_Days = ClsOrderPattern.G_Read5;
break;
case 5:
_G_Code = ClsOrderPattern.G_Code6;
_R_Days = ClsOrderPattern.G_Read6;
break;
case 6:
_G_Code = ClsOrderPattern.G_Code7;
_R_Days = ClsOrderPattern.G_Read7;
break;
case 7:
_G_Code = ClsOrderPattern.G_Code8;
_R_Days = ClsOrderPattern.G_Read8;
break;
case 8:
_G_Code = ClsOrderPattern.G_Code9;
_R_Days = ClsOrderPattern.G_Read9;
break;
case 9:
_G_Code = ClsOrderPattern.G_Code10;
_R_Days = ClsOrderPattern.G_Read10;
break;
case 10:
_G_Code = ClsOrderPattern.G_Code11;
_R_Days = ClsOrderPattern.G_Read11;
break;
case 11:
_G_Code = ClsOrderPattern.G_Code12;
_R_Days = ClsOrderPattern.G_Read12;
break;
case 12:
_G_Code = ClsOrderPattern.G_Code13;
_R_Days = ClsOrderPattern.G_Read13;
break;
case 13:
_G_Code = ClsOrderPattern.G_Code14;
_R_Days = ClsOrderPattern.G_Read14;
break;
case 14:
_G_Code = ClsOrderPattern.G_Code15;
_R_Days = ClsOrderPattern.G_Read15;
break;
default:
break;
}
// 2020/04/08 コメント化
//csvSyohin = Utility.GetSyohinData(SyoArray, SySzArray, ShiireArray, _G_Code);
// 商品情報取得 2020/04/09
csvSyohin = Utility.GetSyohinsFromDataTable(global.dtSyohin, _G_Code);
int xRow = r * 2 + xR;
int xRow2 = r * 2 + xR + 1;
if (csvSyohin.SYOHIN_CD == "")
{
rtnArray[xRow, 1] = string.Empty;
rtnArray[xRow2, 1] = string.Empty;
rtnArray[xRow, 23] = string.Empty;
rtnArray[xRow, 44] = string.Empty;
rtnArray[xRow, 48] = string.Empty;
rtnArray[xRow2, 48] = string.Empty;
rtnArray[xRow, 60] = string.Empty;
rtnArray[xRow, 74] = string.Empty;
rtnArray[xRow2, 60] = string.Empty;
}
else
{
rtnArray[xRow, 1] = csvSyohin.SIRESAKI_NM;
rtnArray[xRow2, 1] = csvSyohin.SYOHIN_NM;
rtnArray[xRow, 23] = csvSyohin.SYOHIN_KIKAKU;
rtnArray[xRow, 44] = csvSyohin.CASE_IRISU;
rtnArray[xRow, 48] = csvSyohin.SYOHIN_CD.PadLeft(8, '0');
rtnArray[xRow2, 48] = _R_Days + "日前";
// 納価売価取得：2020/04/10
ClsCsvData.ClsCsvNoukaBaika noukaBaika = Utility.GetNoukaBaikaFromDataTable(pPID, csvSyohin.SYOHIN_CD.PadLeft(8, '0'), global.dtNoukaBaika);
//rtnArray[xRow, 60] = noukaBaika.NOUKA; // 2020/04/10
//rtnArray[xRow, 74] = noukaBaika.BAIKA; // 2020/04/10
rtnArray[xRow, 60] = String.Format("{0:0.##}", noukaBaika.NOUKA); // 2020/08/05 小数点以下対応
rtnArray[xRow, 74] = String.Format("{0:0.##}", noukaBaika.BAIKA); // 2020/08/05 小数点以下対応
rtnArray[xRow2, 60] = csvSyohin.JAN_CD;
}
}
// 備考
//rtnArray[62, 2] = s.備考;
// 配列からシートセルに一括してデータをセットします
rng = oxlsSheet.Range[oxlsSheet.Cells[1, 1], oxlsSheet.Cells[oxlsSheet.UsedRange.Rows.Count, oxlsSheet.UsedRange.Columns.Count]];
rng.Value2 = rtnArray;
}
// 確認のためExcelのウィンドウを表示する
oXls.Visible = true;
// 1枚目はテンプレートシートなので印刷時には削除する
oXls.DisplayAlerts = false;
oXlsBook.Sheets[1].Delete();
// 印刷：2020/04/14
if (radioButton1.Checked)
{
// 印刷
oXlsBook.PrintOutEx(Type.Missing, Type.Missing, copies, true, prnName, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
// 確認のためExcelのウィンドウを非表示にする
oXls.Visible = false;
// Excel出力：2020/04/14
if (radioButton2.Checked)
{
//ダイアログボックスの初期設定
DialogResult ret;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Title = "FAX発注書";
saveFileDialog1.OverwritePrompt = true;
saveFileDialog1.RestoreDirectory = true;
saveFileDialog1.FileName = "FAX発注書";
saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xlsx)|*.xlsx|全てのファイル(*.*)|*.*";
//ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示
string fileName;
ret = saveFileDialog1.ShowDialog();
if (ret == System.Windows.Forms.DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
oXlsBook.SaveAs(fileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
}
// 終了メッセージ
MessageBox.Show("終了しました");
}
catch (Exception e)
{
MessageBox.Show(e.Message, "出力処理", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
// ウィンドウを非表示にする
oXls.Visible = false;
// 保存処理
oXls.DisplayAlerts = false;
// Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
// Excelを終了
oXls.Quit();
// COM オブジェクトの参照カウントを解放する
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsMsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls);
oXls = null;
oXlsBook = null;
oxlsSheet = null;
oxlsMsSheet = null;
GC.Collect();
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
}
/// ---------------------------------------------------------
/// <summary>
/// 請求書発行 </summary>
/// ---------------------------------------------------------
private void seikyuRep()
{
int _Pages = 0;
int _Rows = 0;
int sIr = 26;
int _RowsPrn = 21;
decimal _nebikigo = 0; // 売上金額 - 値引額
decimal _tax = 0; // 消費税
decimal _seikyu = 0; // 請求金額
bool openStatus = true;
string seikyuNum = string.Empty; // 請求書番号
string corp = string.Empty; // 請求先
try
{
//マウスポインタを待機にする
this.Cursor = Cursors.WaitCursor;
string sAppPath = System.AppDomain.CurrentDomain.BaseDirectory;
Excel.Application oXls = new Excel.Application();
// 請求書テンプレートBOOK
Excel.Workbook oXlsBook = (Excel.Workbook)(oXls.Workbooks.Open(sAppPath + Properties.Settings.Default.エクセル請求書, 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));
// 印刷用BOOK
Excel.Workbook oXlsBookPrn = (Excel.Workbook)(oXls.Workbooks.Open(sAppPath + Properties.Settings.Default.エクセル受注確定書印刷, 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.Worksheet oxlsSheet = (Excel.Worksheet)oXlsBook.Sheets[1];
// 請求書印刷シート
Excel.Worksheet oxlsSheetPrn = null;
Excel.Range[] rng = new Microsoft.Office.Interop.Excel.Range[2];
try
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
// チェック行
if (dataGridView1[colSel, i].Value.ToString() == "True")
{
int iX = 0;
// 受注データ取得
foreach (var it in dts.新請求書.Where(a => a.ID == Utility.strToInt(dataGridView1[colID, i].Value.ToString())))
{
// 明細数
foreach (var t in it.Get受注1Rows().OrderBy(a => a.配布開始日))
{
if (iX == 0 || _Rows >= _RowsPrn)
{
// ページ計印字
if (!openStatus)
{
oxlsSheetPrn.Cells[47, 16] = _nebikigo.ToString(); // 小計
oxlsSheetPrn.Cells[48, 16] = _tax.ToString(); // 消費税
oxlsSheetPrn.Cells[49, 16] = _seikyu.ToString(); // 合計
// 備考
if (it.Is備考Null())
{
oxlsSheetPrn.Cells[47, 4] = string.Empty;
}
else
{
oxlsSheetPrn.Cells[47, 4] = it.備考;
}
// ページ計初期化
_nebikigo = 0;
_tax = 0;
_seikyu = 0;
}
// ページ数加算
iX++;
_Pages = iX;
// ページシートを追加する
oxlsSheet.Copy(Type.Missing, oXlsBookPrn.Sheets[_Pages]);
// 印刷用カレントシート
oxlsSheetPrn = (Excel.Worksheet)oXlsBookPrn.Sheets[_Pages + 1];
// 対象行をゼロにしページを初期化
_Rows = 0;
// 請求書№
seikyuNum = t.請求書発行日.Year.ToString() + t.請求書発行日.Month.ToString().PadLeft(2, '0') + t.請求書ID.ToString().PadLeft(5, '0');
oxlsSheetPrn.Cells[1, 16] = "№ " + seikyuNum;
// 発行日
oxlsSheetPrn.Cells[2, 15] = t.請求書発行日.ToShortDateString();
//// ページ数
//oxlsSheetPrn.Cells[3, 16] = "(" + iX.ToString() + "/" + it.明細数.ToString() + ")";
corp = string.Empty;
// 請求先住所
if (it.得意先Row != null)
{
oxlsSheetPrn.Cells[2, 3] = "〒 " + it.得意先Row.請求先郵便番号;
oxlsSheetPrn.Cells[3, 3] = it.得意先Row.請求先都道府県 + " " + it.得意先Row.請求先住所1;
oxlsSheetPrn.Cells[4, 3] = it.得意先Row.請求先住所2;
// 請求先名
//if (it.得意先Row.Is請求先名称Null())
//{
// corp = it.得意先Row.略称;
//}
//else
//{
// corp = it.得意先Row.請求先名称;
//}
// 2019/02/21
if (it.得意先Row.請求先名称 == null)
{
corp = it.得意先Row.略称;
}
else
{
corp = it.得意先Row.請求先名称;
}
// 2019/02/21 コメント化
//// 部署・担当者名
//string tn = (it.得意先Row.部署名 + " " + it.得意先Row.請求先担当者名).Trim();
//if (tn != string.Empty)
//{
// oxlsSheetPrn.Cells[8, 3] = tn + " 様";
// oxlsSheetPrn.Cells[6, 3] = corp;
//}
//else
//{
// oxlsSheetPrn.Cells[8, 3] = string.Empty;
// oxlsSheetPrn.Cells[6, 3] = corp + " 御中";
//}
// 部署・担当者名（得意先@請求先部署名、得意先@請求先敬称を使用）: 2019/02/21
string tn = (it.得意先Row.請求先部署名 + " " + it.得意先Row.請求先担当者名).Trim();
if (tn != string.Empty)
{
oxlsSheetPrn.Cells[8, 3] = tn + " " + it.得意先Row.請求先敬称;
oxlsSheetPrn.Cells[6, 3] = corp;
}
else
{
oxlsSheetPrn.Cells[8, 3] = string.Empty;
oxlsSheetPrn.Cells[6, 3] = corp + " " + it.得意先Row.請求先敬称;
}
}
// 1ページ目
if (_Pages == 1)
{
// 合計請求金額
//oxlsSheetPrn.Cells[22, 5] = dataGridView1[colKingaku, i].Value.ToString().Trim().Replace(",", "");
//oxlsSheetPrn.Cells[22, 5] = it.請求金額.ToString();
oxlsSheetPrn.Cells[22, 5] = it.残金.ToString(); // 残金を請求金額とする 2015/10/20
}
else
{
// 合計請求金額
oxlsSheetPrn.Cells[22, 5] = "********************";
}
// 支払期日
oxlsSheetPrn.Cells[55, 4] = dataGridView1[colSDt, i].Value.ToString();
// 開始ステータス
openStatus = false;
}
// 明細内容
//oxlsSheetPrn.Cells[sIr + _Rows, 1] = t.受注日.Month.ToString() + "." + t.受注日.Day.ToString().PadLeft(2, '0'); // 日付
oxlsSheetPrn.Cells[sIr + _Rows, 1] = t.配布開始日.Month.ToString() + "." + (t.配布開始日.Day.ToString().PadLeft(2, '0')); // 日付
// 受注内容
if (t.受注種別Row != null)
{
oxlsSheetPrn.Cells[sIr + _Rows, 3] = t.受注種別Row.称;
}
else
{
oxlsSheetPrn.Cells[sIr + _Rows, 3] = string.Empty;
}
// サイズ
if (t.判型Row != null)
{
oxlsSheetPrn.Cells[sIr + _Rows, 5] = t.判型Row.称;
}
else
{
oxlsSheetPrn.Cells[sIr + _Rows, 5] = string.Empty;
}
// チラシ名
oxlsSheetPrn.Cells[sIr + _Rows, 6] = t.チラシ名;
// 配布形態
if (t.配布形態Row != null)
{
oxlsSheetPrn.Cells[sIr + _Rows, 12] = t.配布形態Row.称;
}
else
{
oxlsSheetPrn.Cells[sIr + _Rows, 12] = string.Empty;
}
oxlsSheetPrn.Cells[sIr + _Rows, 14] = t.単価.ToString("n2");
oxlsSheetPrn.Cells[sIr + _Rows, 15] = t.枚数.ToString();
oxlsSheetPrn.Cells[sIr + _Rows, 16] = t.金額.ToString();
// 行加算
_Rows++;
// 値引額があるとき値引行を印字します
if (t.値引額 > 0)
{
// 明細内容
oxlsSheetPrn.Cells[sIr + _Rows, 6] = "値引";
oxlsSheetPrn.Cells[sIr + _Rows, 14] = (t.値引額 * (-1)).ToString("#,0");
oxlsSheetPrn.Cells[sIr + _Rows, 15] = "1";
oxlsSheetPrn.Cells[sIr + _Rows, 16] = (t.値引額 * (-1)).ToString();
// 行加算
_Rows++;
}
// ページ計加算
_nebikigo += (t.金額 - t.値引額); // 小計
_tax += t.消費税; // 消費税
_seikyu += t.売上金額; // 合計
}
// 入金額 2015/10/20
foreach (var nt in it.Get新入金Rows().OrderBy(a => a.入金年月日))
{
// 明細内容
oxlsSheetPrn.Cells[sIr + _Rows, 1] = nt.入金年月日.Month.ToString() + "." + (nt.入金年月日.Day.ToString().PadLeft(2, '0')); // 日付
oxlsSheetPrn.Cells[sIr + _Rows, 6] = "入金 ";
oxlsSheetPrn.Cells[sIr + _Rows, 14] = (nt.金額 * (-1)).ToString("#,0");
oxlsSheetPrn.Cells[sIr + _Rows, 15] = "1";
oxlsSheetPrn.Cells[sIr + _Rows, 16] = (nt.金額 * (-1)).ToString();
// 行加算
_Rows++;
// ページ計加算
//_nebikigo -= nt.金額; // 小計
}
// ページ計印字
oxlsSheetPrn.Cells[47, 16] = _nebikigo.ToString(); // 小計
oxlsSheetPrn.Cells[48, 16] = _tax.ToString(); // 消費税
oxlsSheetPrn.Cells[49, 16] = _seikyu.ToString(); // 合計
// 備考
if (it.Is備考Null())
{
oxlsSheetPrn.Cells[47, 4] = string.Empty;
}
else
{
oxlsSheetPrn.Cells[47, 4] = it.備考;
}
// 営業担当 2015/11/18
if (it.得意先Row != null && it.得意先Row.社員Row != null)
{
oxlsSheetPrn.Cells[57, 15] = it.得意先Row.社員Row.氏名;
}
else
{
oxlsSheetPrn.Cells[57, 15] = string.Empty;
}
}
}
}
// 印刷用BOOKの1番目のシートは削除する
((Excel.Worksheet)oXlsBookPrn.Sheets[1]).Delete();
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
// 確認のためExcelのウィンドウを表示する
//oXls.Visible = true;
// 印刷
oXlsBookPrn.PrintOutEx(1, Type.Missing, 1, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// ウィンドウを非表示にする
oXls.Visible = false;
// 保存処理
oXls.DisplayAlerts = false;
DialogResult ret;
// ダイアログボックスの初期設定
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Title = "請求書発行";
saveFileDialog1.OverwritePrompt = true;
saveFileDialog1.RestoreDirectory = true;
//saveFileDialog1.FileName = "請求書_" + corp + seikyuNum;
saveFileDialog1.FileName = "請求書";
saveFileDialog1.Filter = "Microsoft Office Excelファイル(*.xlsx)|*.xlsx|全てのファイル(*.*)|*.*";
// ダイアログボックスを表示し「保存」ボタンが選択されたらファイル名を表示
string fileName;
ret = saveFileDialog1.ShowDialog();
if (ret == System.Windows.Forms.DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
oXlsBookPrn.SaveAs(fileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
// 処理終了メッセージ
MessageBox.Show("終了しました", "請求書発行", MessageBoxButtons.OK, MessageBoxIcon.Information);
// 請求書発行フラグ更新
seikyuRepFlgUpdate();
// 請求書データ再表示
dataShow(0, 0);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "請求書発行", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
//Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
//Excelを終了
oXls.Quit();
}
finally
{
// ウィンドウを非表示にする
oXls.Visible = false;
// 保存処理
oXls.DisplayAlerts = false;
// Bookをクローズ
oXlsBook.Close(Type.Missing, Type.Missing, Type.Missing);
oXlsBookPrn.Close(Type.Missing, Type.Missing, Type.Missing);
// Excelを終了
oXls.Quit();
// COM オブジェクトの参照カウントを解放する
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oxlsSheetPrn);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXlsBookPrn);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXls);
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "請求書作成", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
//マウスポインタを元に戻す
this.Cursor = Cursors.Default;
}
//打印整个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
{
}
}
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);
}
