/// <summary>
/// 保存sheet页为pdf,指定转化页数
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheetIndex"></param>
/// <param name="toPath"></param>
/// <param name="toPage"></param>
/// <returns></returns>
public static bool SaveExcelWorkSheetAsPDFWithPage(EXCEL.Workbook workBook, int sheetIndex, string toPath, int toPage)
{
bool flag = false;
object missing = Type.Missing;
try
{
if (toPath.Length == 0)
{
flag = false;
throw new Exception("需要转换的目标文件路径不能为空。");
}
EXCEL.Worksheet sheet = (EXCEL.Worksheet)workBook.Sheets[sheetIndex];
//sheet.Activate();
sheet.ExportAsFixedFormat(
EXCEL.XlFixedFormatType.xlTypePDF,
toPath,
EXCEL.XlFixedFormatQuality.xlQualityStandard, //可设置为 xlQualityStandard 或 xlQualityMinimum。
true, //包含文档属性
false, //如果设置为 True，则忽略在发布时设置的任何打印区域。如果设置为 False，则使用在发布时设置的打印区域。
1, //发布的起始页码。如果省略此参数，则从起始位置开始发布。
toPage, //发布的终止页码。如果省略此参数，则发布至最后一页。
false, //是否发布文件后在查看器中显示文件。
Type.Missing);
flag = true;
}
catch (Exception exception)
{
classLims_NPOI.WriteLog(exception, "");
flag = false;
}
return(flag);
}
bool XLSConvertToPDFEach(string sourceFile)
{
string sourcePath = sourceFile;
string targetPath = GetFileName(sourceFile);
bool result = false;
Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF;// Excel.XlFixedFormatType.xlTypePDF;
object paramMissing = Type.Missing;
Excel.ApplicationClass application = null;
Excel.Workbook excelWorkBook = null;
Excel.Worksheet excelWorksheet = null;
try
{
application = new Excel.ApplicationClass();
object target = targetPath;
object type = targetType;
excelWorkBook = application.Workbooks.Open(sourcePath);
List <string> lstSheets = new List <string>();
foreach (string item in this.checkedListBox1.CheckedItems)
{
excelWorksheet = (Excel.Worksheet)excelWorkBook.Worksheets[item];
if (excelWorksheet != null)
{
excelWorksheet.ExportAsFixedFormat(targetType, target + "_" + excelWorksheet.Name, Excel.XlFixedFormatQuality.xlQualityStandard, true, paramMissing, paramMissing, paramMissing, false, paramMissing);
result = true;
}
}
}
catch
{
result = false;
}
finally
{
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
if (application != null)
{
application.Quit();
application = null;
}
//ms solution is like this
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
public static string SaveAsPdf(string filename)
{
var path = string.Empty;
Transaction(filename, (wkbk) =>
{
Excel.Worksheet sheet = wkbk.Sheets[1];
path = Path.Combine(Path.GetDirectoryName(filename), Path.GetFileNameWithoutExtension(filename) + ".pdf");
sheet.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, path, Excel.XlFixedFormatQuality.xlQualityStandard);
});
return(path);
}
/*
* Do not Parrelism here.
* It will be controlled by ... Program, it just callee
*/
public int EntryPoint(string pathXls)
{
RAIIFuncs rFuncs = new RAIIFuncs();
using (RAIICont _raiiCont = new RAIICont())
{
Excel.Application appExcel = new Excel.Application();
#region Removed_Code
/*
* Func<bool> toCall = () =>
* {
* appExcel.Quit();
* RMarschal.ReleaseComObject(appExcel);
* return true;
* };
*/
/*
* _raiiCont.Push( new RAII<Excel.Application>(
* x => {
* x.Quit();
* RMarschal.ReleaseComObject(x);
* return true;
* }
* , appExcel
* ) );
*/
//throw new System.Exception("OMG");
#endregion
_raiiCont.Push(rFuncs.GetFunc(appExcel));
Excel.Workbooks oWBs = appExcel.Workbooks;
_raiiCont.Push(rFuncs.GetFunc(oWBs));
Excel.Workbook _WB = oWBs.Open(pathXls, ReadOnly: true);
_raiiCont.Push(rFuncs.GetFunc(_WB));
string strName = _WB.Name;
string strBaseName = System.IO.Path.GetFileNameWithoutExtension(_WB.FullName);
//System.Diagnostics.Debug.WriteLine( strBaseName );
Excel.Sheets oWSs = _WB.Sheets;
_raiiCont.Push(rFuncs.GetFunc(oWSs));
Excel.Worksheet _WS = oWSs["ARBETISTABELLE"];
_raiiCont.Push(rFuncs.GetFunc(_WS));
SetPrintAreaToTable(_WS, "Tabelle1");
SetPrnAreaToSingleWidth(appExcel, _WS);
_WS.ExportAsFixedFormat(
Type: Excel.XlFixedFormatType.xlTypePDF
, Filename: @"C:\Users\A.Roennburg\Documents\GH_ArbeitPlatz\Arbeits vom Sandra\MARKANT_CalculateSheet\PDF Daten\" + strBaseName + ".pdf"
, Quality: Excel.XlFixedFormatQuality.xlQualityStandard
, IncludeDocProperties: true
, IgnorePrintAreas: false
, OpenAfterPublish: false
);
ExportCSV(appExcel, oWBs, _WS, "Tabelle1", strBaseName);
}
return(0);
}
public String facture(ArrayList lesEnfants, int facture_id, DateTime debut, DateTime fin
, string codeClient, string nomClient, string prenomClient
, string adresseClient, string villeClient, string paysClient)
{
try
{
// Création de l'objet de communication avec la db
DbConnection dbTalk = new DbConnection();
//// Création de l'objet de communication avec un autre controleur
//Ctrl_EncodageFactures controle = new Ctrl_EncodageFactures();
// Création de l'objet etablissement avec ses détails
DetailsEtablissement etablissement = new DetailsEtablissement();
// Récupération de la valeur de la TVA dans une variable
String query = dbTalk.getQuery("tva");
float tva = float.Parse(dbTalk.recupDataScalar(query));
// Ouverture d'excel en mode caché et y intégrer le brouillon facture
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = false;
object misValue = System.Reflection.Missing.Value;
// Initialisation du path vers le répertoire courant de l'exécutable
string path = System.IO.Directory.GetCurrentDirectory();
// Brouillon Facture.xlsx dans les Ressources du projet
string pathFacture = path + @"\Resources\Facture.xlsx";
// Chemin d'accès pour l'enregistrement des factures
string pathSortie = @"C:\Factures";
// Création de la facture
// Ouvrir un workbook avec le brouillon
Excel.Workbook wbk = xlApp.Workbooks.Open(pathFacture);
Excel.Worksheet ws = new Excel.Worksheet();
ws = (Excel.Worksheet)wbk.Worksheets[1];
//Encodage établissement
if (etablissement.get_nom().Length > 0) // Si l'objet contient au moins le nom, des données ont été insérées dans la db
{
ws.Cells[7, "A"] = etablissement.get_nom();
ws.Cells[8, "A"] = etablissement.get_numEtRue();
ws.Cells[9, "A"] = etablissement.get_cpEtVille() + " (" + etablissement.get_pays() + ")";
ws.Cells[15, "A"] = "Mail : " + etablissement.get_courriel();
ws.Cells[11, "A"] = "Téléphone : " + etablissement.get_tel();
ws.Cells[13, "A"] = "Fax : " + etablissement.get_fax();
ws.Cells[48, "B"] = stringSplit(etablissement.get_banque_BE());
ws.Cells[49, "B"] = stringSplit(etablissement.get_bic_BE());
ws.Cells[48, "E"] = stringSplit(etablissement.get_banque_LU());
ws.Cells[49, "E"] = stringSplit(etablissement.get_bic_LU());
}
else // si non pas d'infos d'établissement (on peu refaire une nouvelle facture, mais celle-ci restera répertioré dans la db)
{
ws.Cells[7, "A"] = "Aucun établissement est enregistré!";
}
// Insertion informations générales dans la facture
ws.Cells[7, "D"] = facture_id; //N° facture
ws.Cells[7, "E"] = DateTime.Today; // Date Facture
ws.Cells[7, "F"] = codeClient; //Code Client
ws.Cells[9, "D"] = nomClient + " " + prenomClient;
ws.Cells[11, "D"] = adresseClient;
ws.Cells[13, "D"] = villeClient + " " + paysClient;
// Insertion période
ws.Cells[20, "C"] = debut;
ws.Cells[20, "E"] = fin;
// Insertion du détail des repas par l'intermédiaire de l'objet "enfant"
int reference = 0;
int position = 22;
foreach (DetailsEnfant enfant in lesEnfants)
{
List <String[]> listePrix = enfant.get_priceList();
foreach (String[] details in listePrix)
{
if (details[1] != "0")
{
ws.Cells[position, "A"] = ++reference;
ws.Cells[position, "B"] = typeRepas(int.Parse(details[0]));
ws.Cells[position, "D"] = details[1];
ws.Cells[position, "E"] = float.Parse(details[2]);
position++;
}
}
}
// Affichage et calcul de montant de la tva
ws.Cells[41, "D"] = "Montant de la TVA à " + tva.ToString() + " %";
ws.Cells[41, "G"] = float.Parse(ws.Cells[39, 7].Value.ToString()) * (tva / 100);
// Mode de paiement et date d'échéance
ws.Cells[17, 3] = "virement bancaire";
ws.Cells[18, 3] = (DateTime.Now.AddDays(30)).ToString("dd/MM/yyyy");
// répertoire sauvegarde et fermeture du programme
if (File.Exists(pathSortie) == false)
{
DirectoryInfo di = Directory.CreateDirectory(pathSortie);
}
// chemin d'accès de la facture
pathSortie = pathSortie + "\\F_" + facture_id.ToString() + ".pdf";
ws.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, pathSortie);
wbk.Close(false, misValue, misValue);
xlApp.Quit();
releaseObject(ws);
releaseObject(wbk);
releaseObject(xlApp);
return(pathSortie);
// Excel persiste, on le voit avec CTRL+ALT+DEL
}
catch (Exception msg)
{
return("Problème lors de la phase de création du pdf :\r\n\r\n" + msg);
}
}
/// <summary>
/// EXCEL转PDF
/// </summary>
/// <param name="strSourceFile">要转换的EXCEL文件路径</param>
/// <param name="strTargetFile">目标文件</param>
/// <returns>转换结果：TRUE FALSE</returns>
public bool ExcelConvertTOPDF(string strSourceFile, string strTargetFile)
{
bool flag = false;
if (File.Exists(strSourceFile))
{
//转换成的上档格式PDF
EXCEL.XlFixedFormatType targetType = EXCEL.XlFixedFormatType.xlTypePDF;
object targetFile = strTargetFile;
object missing = Type.Missing;
EXCEL.ApplicationClass excel = null;
EXCEL.Workbook workBook = null;
EXCEL.Workbooks workBooks = null;
EXCEL.Worksheet sheet = null;
try
{
excel = new EXCEL.ApplicationClass();
//excel.DisplayAlerts = true;
workBooks = excel.Workbooks;
workBook = workBooks.Open(strSourceFile, missing, missing,
missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing,
missing, missing);
//设置格式，导出成PDF
sheet = (EXCEL.Worksheet)workBook.Worksheets[1];//下载从1开始
//把sheet设置成横向
//sheet.PageSetup.Orientation = EXCEL.XlPageOrientation.xlLandscape;
//可以设置sheet页的其他相关设置，不列举
sheet.ExportAsFixedFormat(targetType, targetFile, EXCEL.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
flag = true;
}
catch (Exception ex)
{
classLims_NPOI.WriteLog(ex, "");
flag = false;
}
finally
{
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
//Marshal.FinalReleaseComObject(sheet);
sheet = null;
}
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;
//flag = KillSpecialExcel(excel);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
return(flag);
}
