public static void ConvertExcelAsMemoryStream()
{
// Convert Excel to DOCX in memory
ExcelToPdf x = new ExcelToPdf();
// Set DOCX as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Docx;
string excelFile = Path.GetFullPath(@"..\..\test.xls");
string docxFile = Path.ChangeExtension(excelFile, ".docx");
byte[] docxBytes = null;
try
{
// Let us say, we have a memory stream with Excel data.
using (MemoryStream ms = new MemoryStream(File.ReadAllBytes(excelFile)))
{
docxBytes = x.ConvertBytes(ms.ToArray());
}
// Save docxBytes to a file for demonstration purposes.
File.WriteAllBytes(docxFile, docxBytes);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(docxFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
static void Main(string[] args)
{
// Convert Excel file to DOCX file
ExcelToPdf x = new ExcelToPdf();
// Set DOCX as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Docx;
string excelFile = Path.GetFullPath(@"..\..\test.xls");
string docxFile = Path.ChangeExtension(excelFile, ".docx");;
try
{
x.ConvertFile(excelFile, docxFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(docxFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
public static void ConvertExcelAsByteArray()
{
// Convert Excel to PDF in memory
ExcelToPdf x = new ExcelToPdf();
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
string excelFile = Path.GetFullPath(@"..\..\test.xls");
string pdfFile = Path.ChangeExtension(excelFile, ".pdf");;
byte[] excelBytes = File.ReadAllBytes(excelFile);
byte[] pdfBytes = null;
try
{
pdfBytes = x.ConvertBytes(excelBytes);
// Save pdfBytes to a file for demonstration purposes.
File.WriteAllBytes(pdfFile, pdfBytes);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(pdfFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
private static void Main(string[] args)
{
//Prepare variables with path.
string excelFile = Path.GetFullPath(@"..\..\test.xlsx");
string docxFile = Path.ChangeExtension(excelFile, ".docx");;
string rtfFile = Path.ChangeExtension(excelFile, ".rtf");;
string pdfFile = Path.ChangeExtension(excelFile, ".pdf");;
ExcelToPdf x = new ExcelToPdf();
// Set DOCX as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Docx;
x.ConvertFile(excelFile, docxFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(docxFile)
{
UseShellExecute = true
});
// Set RTF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Rtf;
x.ConvertFile(excelFile, rtfFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(rtfFile)
{
UseShellExecute = true
});
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
x.ConvertFile(excelFile, pdfFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(pdfFile)
{
UseShellExecute = true
});
}
static void Main(string[] args)
{
// Set custom sheets to convert.
ExcelToPdf x = new ExcelToPdf();
x.PageStyle.PageSize.Letter();
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
// Let's convert only sheets: 1,3.
x.Sheets.Custom(new int[] { 1, 3 });
string excelFile = Path.GetFullPath(@"..\..\test.xlsx");
string pdfFile = Path.ChangeExtension(excelFile, ".pdf");;
try
{
x.ConvertFile(excelFile, pdfFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(pdfFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
public static void ConvertExcelAsByteArray()
{
// Convert Excel to RTF in memory
ExcelToPdf x = new ExcelToPdf();
// Set RTF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Rtf;
// Let's create trace file.
x.CreateTraceFile = true;
x.TraceFilePath = Path.GetFullPath(@"..\..\trace.txt");
string excelFile = Path.GetFullPath(@"..\..\test.xls");
string rtfFile = Path.ChangeExtension(excelFile, ".rtf");;
byte[] excelBytes = File.ReadAllBytes(excelFile);
byte[] rtfBytes = null;
try
{
rtfBytes = x.ConvertBytes(excelBytes);
// Save rtfBytes to a file for demonstration purposes.
File.WriteAllBytes(rtfFile, rtfBytes);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(rtfFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
static void Main(string[] args)
{
// Fit each sheet to a single page fixed size.
ExcelToPdf x = new ExcelToPdf();
// Fit each sheet to single PDF page, A4 format.
x.PageStyle.PageSize.A4();
x.PageStyle.PageScale.Auto();
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
string excelFile = Path.GetFullPath(@"..\..\test.xlsx");
string pdfFile = Path.ChangeExtension(excelFile, ".pdf");;
try
{
x.ConvertFile(excelFile, pdfFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(pdfFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
static void Main(string[] args)
{
// Convert Excel file to DOCX file
ExcelToPdf x = new ExcelToPdf();
// You may download the latest version of SDK here:
// www.sautinsoft.com/convert-excel-xls-to-pdf/free-download-spreadsheet-xls-excel-to-pdf-component.php
// Set DOCX as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Docx;
string excelFile = Path.GetFullPath(@"d:\Download\test.xls");
string docxFile = Path.ChangeExtension(excelFile, ".docx");;
try
{
x.ConvertFile(excelFile, docxFile);
System.Diagnostics.Process.Start(docxFile);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
static void Main(string[] args)
{
// Add page numbers in the resulting PDF.
ExcelToPdf x = new ExcelToPdf();
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
// Set page numbers in format "Page 1 of N" formatted by Arial, 14 pt.
x.PageStyle.PageNumFormat.Text = "Page {page} of {numPages}";
x.PageStyle.PageNumFormat.FontFace = "Arial";
x.PageStyle.PageNumFormat.FontSize = 14;
string excelFile = Path.GetFullPath(@"..\..\test.xlsx");
string pdfFile = Path.ChangeExtension(excelFile, ".pdf");;
try
{
x.ConvertFile(excelFile, pdfFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(pdfFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
static void Main(string[] args)
{
// Convert a password protected workbook
ExcelToPdf x = new ExcelToPdf();
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
// Set the password for protected workbook.
x.Password = "******";
string excelFile = Path.GetFullPath(@"..\..\test.xls");
string pdfFile = Path.ChangeExtension(excelFile, ".pdf");;
try
{
x.ConvertFile(excelFile, pdfFile);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(pdfFile)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
private void dönüştür_Click(object sender, EventArgs e)
{
ExcelToPdf x = new ExcelToPdf();
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Docx;
try
{
x.ConvertFile(excelFile, docxFile);
MessageBox.Show("Dönüştürme işlemi başarılı .");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
static void Main(string[] args)
{
// Get sheets number.
ExcelToPdf x = new ExcelToPdf();
FileInfo excelFile = new FileInfo(@"..\..\test.xlsx");
try
{
int sheets = x.GetSheetsNumber(excelFile.FullName);
Console.WriteLine("The file {0} has {1} sheet{2}.", excelFile.Name, sheets, (sheets > 1)?"s":"");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
private void Button_Click2(object sender, RoutedEventArgs e) //.xlsx
{
//SautinSoft.ExcelToPdf convert.xls and .xlsx to PDF, RTF, DOCX
//SautinSoft.ExcelToPdf x = new SautinSoft.ExcelToPdf();
////x.ConvertFile(@"C:\Users\user\Desktop\kkkkkkkk\d\d\dokum\Business-Budget.xlsx", @"d:C:\Users\user\Desktop\kkkkkkkk\d\d\dokum\Table.pdf");
//Convert Excel to PDF in memory
ExcelToPdf x = new ExcelToPdf();
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.CheckFileExists = true;
openFileDialog.Multiselect = true;
//openFileDialog.Filter = "Doc files (*.xls)|*.xls|All files (*.*)|*.*";
openFileDialog.Filter = "Doc files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles);
Nullable <bool> result = openFileDialog.ShowDialog();
if (result == true)
{
string excelFile = openFileDialog.FileName; // Open document
string pdfFile = @"C:\Users\Mariia\Desktop\Convertor\PDFfromXLS.pdf";
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
byte[] pdfBytes = null;
try
{
// Let us say, we have a memory stream with Excel data.
using (MemoryStream ms = new MemoryStream(File.ReadAllBytes(excelFile)))
{
pdfBytes = x.ConvertBytes(ms.ToArray());
}
// Save pdfBytes to a file for demonstration purposes.
File.WriteAllBytes(pdfFile, pdfBytes);
System.Diagnostics.Process.Start(pdfFile);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
//string excelFile = @"D:\Desktop\1ДЗ\Курсач\d3 (1)\d3\d\d\dokum\Business-Budget.xlsx";
//string pdfFile = System.IO.Path.ChangeExtension(excelFile, ".pdf");
}
static void Main(string[] args)
{
// Split PDF by pages.
// 1. Let's get a PDF with 3 pages from .xlsx.
// 2. Let's split it by 3 PDF files and show them.
ExcelToPdf x = new ExcelToPdf();
x.PageStyle.PageSize.Letter();
x.PageStyle.PageMarginTop.mm(5);
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
string excelFile = Path.GetFullPath(@"..\..\test.xlsx");
FileInfo pdfFile = new FileInfo(Path.ChangeExtension(excelFile, ".pdf"));
try
{
// 1. Let's get a PDF with 3 pages from .xlsx.
x.ConvertFile(excelFile, pdfFile.FullName);
// 2. Let's split it by 3 PDF files and show them.
// Create a directory for storing separate PDFs.
DirectoryInfo pdfDir = pdfFile.Directory.CreateSubdirectory("My Pages");
x.SplitPDFFileToPDFFolder(pdfFile.FullName, pdfDir.FullName);
// Show result
FileInfo[] pageFiles = pdfDir.GetFiles("*.pdf");
Console.WriteLine("Resulting PDF files:\n");
foreach (FileInfo pageFile in pageFiles)
{
Console.WriteLine(pageFile.Name);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
static void Main(string[] args)
{
// Merge two PDF files.
// 1. Let's get a PDF from .xlsx.
// 2. Let's merge this PDF with itself.
ExcelToPdf x = new ExcelToPdf();
x.PageStyle.PageSize.Letter();
x.PageStyle.PageMarginTop.mm(5);
// Set PDF as output format.
x.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
// Let's convert only 3rd page.
x.Sheets.Custom(new int[] { 3 });
string excelFile = Path.GetFullPath(@"..\..\test.xlsx");
FileInfo pdfFile = new FileInfo(Path.ChangeExtension(excelFile, ".pdf"));
string singlePdf = Path.Combine(pdfFile.Directory.FullName, "Single.pdf");
try
{
// 1. Convert Excel to PDF.
x.ConvertFile(excelFile, pdfFile.FullName);
// 2. Merge the PDF file with itself.
x.MergePDFFileArrayToPDFFile(new string[] { pdfFile.FullName, pdfFile.FullName },
singlePdf);
System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(singlePdf)
{
UseShellExecute = true
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
public ActionResult viewPRExcel(Int16 fiscalYear, Int16? coeID)
{
ModelState.Clear();
var viewModel = new PRViewModel
{
//allCoEs = db.CoEs.ToList(),
allCoEs = db.CoEs.ToList(),
allMaps = db.Indicator_CoE_Maps.ToList(),
allFootnoteMaps = db.Indicator_Footnote_Maps.ToList()
};
// Create the workbook
var wb = new ExcelFile();
var prBlue = ExcelColor.FromArgb(0, 51, 102);
var prGreen = ExcelColor.FromArgb(0, 118, 53);
var prYellow = ExcelColor.FromArgb(255, 192, 0);
var prRed = ExcelColor.FromArgb(255, 0, 0);
var prHeader1Fill = prBlue;
var prHeader1Font = ExcelColor.White;
var prHeader2Fill = ExcelColor.White;
var prHeader2Font = ExcelColor.Black;
var prBorder = ExcelColor.FromArgb(0, 0, 0);
var prAreaFill = ExcelColor.FromArgb(192, 192, 192);
var prAreaFont = ExcelColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeightSeperator = 7.5;
var prAreaObjectiveFontsize = 8;
var indentLength = 24;
var firstIndentLength = 20;
var innerIndentLength = 5;
var newLineHeight = 12.6;
var defNote = "Portal data from the Canadian Institute for Health Information (CIHI) has been used to generate data within this report with acknowledgement to CIHI, the Ministry of Health and Long-Term Care (MOHLTC) and Stats Canada (as applicable). Views are not those of the acknowledged sources. Facility identifiable data other than Mount Sinai Hospital (MSH) is not to be published without the consent of that organization (except where reported at an aggregate level). As this is not a database supported by MSH, please demonstrate caution with use and interpretation of the information. MSH is not responsible for any changes derived from the source data/canned reports. Data may be subject to change.";
var prNumberWidth = 4;
var prIndicatorWidth = 55;
var prValueWidth = 11;
var prDefWidth = 100;
var prRatiWidth = 50;
var prCompWidth = 50;
//var fitRatio = 3.77;
var fitRatio = 1.7;
List<int> fitAdjustableRows = new List<int>();
var prFootnoteCharsNewLine = 125;
var prObjectivesCharsNewLine = 226;
//DELETE THIS
//coeID = null;
var allCoes = new List<CoEs>();
if (coeID != 0 && coeID != null)
{
allCoes = viewModel.allCoEs.Where(x => x.CoE_ID == coeID).ToList();
}
else
{
allCoes = viewModel.allCoEs.ToList();
}
foreach (var coe in allCoes)
{
var wsPRName = coe.CoE_Abbr;
var wsDefName = "Def_" + coe.CoE_Abbr;
var wsPR = wb.Worksheets.Add(wsPRName);
var wsDef = wb.Worksheets.Add(wsDefName);
List<ExcelWorksheet> wsList = new List<ExcelWorksheet>();
wsList.Add(wsPR);
wsList.Add(wsDef);
foreach (var ws in wsList)
{
var currentRow = 4;
ws.Rows[2].Height = 21;
int startRow;
int indicatorNumber = 1;
ws.PrintOptions.TopMargin = 0;
ws.PrintOptions.HeaderMargin = 0;
ws.PrintOptions.BottomMargin = 0.5;
ws.PrintOptions.LeftMargin = 0;
ws.PrintOptions.RightMargin = 0;
ws.PrintOptions.Portrait = false;
ws.PrintOptions.PaperType = PaperType.Legal;
ws.PrintOptions.FitWorksheetWidthToPages = 1;
ws.PrintOptions.FitWorksheetHeightToPages = 1;
//ws.PageSetup.Margins.Top = 0;
//ws.PageSetup.Margins.Header = 0;
//ws.PageSetup.Margins.Left = 0.5;
//ws.PageSetup.Margins.Right = 0.5;
//ws.PageSetup.Margins.Bottom = 0.5;
//ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
//ws.PageSetup.PaperSize = XLPaperSize.LegalPaper;
//ws.PageSetup.FitToPages(1, 1);
string[,] columnHeaders = new string[0, 0];
if (ws.Name == wsPRName)
{
var prHeadder2Title = FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance";
prHeadder2Title = prHeadder2Title.Replace("_", " ");
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_3", fiscalYear), ""},
{FiscalYear.FYStrFull("FY_2", fiscalYear),""},
{FiscalYear.FYStrFull("FY_1", fiscalYear),""},
{prHeadder2Title,"Q1"},
{prHeadder2Title,"Q2"},
{prHeadder2Title,"Q3"},
{prHeadder2Title,"Q4"},
{prHeadder2Title,"YTD"},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance_Threshold",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator",""}
};
}
else if (ws.Name == wsDefName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source",""}
};
}
var currentCol = 1;
var prHeader2ColStart = 99;
var prHeader2ColEnd = 1;
int maxCol = columnHeaders.GetUpperBound(0) + 1;
var prTitle = ws.Cells[currentRow, 1];
prTitle.Value = coe.CoE;
prTitle.Style.Font.Size = prTitleFont;
prTitle.Style.Font.Weight = ExcelFont.BoldWeight;
prTitle.Style.Font.Color = prHeader1Font;
prTitle.Style.FillPattern.SetSolid(prHeader1Fill);
ws.Cells.GetSubrangeAbsolute(currentRow, 1, currentRow, maxCol).Merged = true;
ws.Cells.GetSubrangeAbsolute(currentRow + 1, 1, currentRow + 1, maxCol).Merged = true;
ws.Rows[currentRow + 1].Height = (int)prHeightSeperator;
currentRow += 2;
startRow = currentRow;
for (int i = 0; i <= columnHeaders.GetUpperBound(0); i++)
{
if (columnHeaders[i, 1] == "")
{
var columnField = columnHeaders[i, 0];
string cellValue;
Type t = typeof(Indicators);
cellValue = t.GetProperty(columnField) != null ?
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName :
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicator_CoE_Maps), columnField).DisplayName;
ws.Cells[currentRow, currentCol].Value = cellValue;
ws.Cells.GetSubrangeAbsolute(currentRow, currentCol, currentRow + 1, currentCol).Merged = true;
currentCol++;
}
else
{
var columnField = columnHeaders[i, 1];
var columnFieldTop = columnHeaders[i, 0];
ws.Cells[currentRow + 1, currentCol].Value = columnField;
ws.Cells[currentRow, currentCol].Value = columnFieldTop;
if (currentCol < prHeader2ColStart) { prHeader2ColStart = currentCol; }
if (currentCol > prHeader2ColEnd) { prHeader2ColEnd = currentCol; }
currentCol++;
}
}
currentCol--;
ws.Cells.GetSubrangeAbsolute(currentRow, prHeader2ColStart, currentRow, prHeader2ColEnd).Merged = true;
var prHeader1 = ws.Cells.GetSubrangeAbsolute(currentRow, 1, currentRow + 1, currentCol);
var prHeader2 = ws.Cells.GetSubrangeAbsolute(currentRow + 1, prHeader2ColStart, currentRow + 1, prHeader2ColEnd);
prHeader1.Style.VerticalAlignment = VerticalAlignmentStyle.Center;
prHeader1.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
prHeader1.Style.FillPattern.SetSolid(prHeader1Fill);
prHeader1.Style.Font.Color = prHeader1Font;
prHeader2.Style.FillPattern.SetSolid(prHeader2Fill);
prHeader2.Style.Font.Color = prHeader2Font;
currentRow += 2;
List<Footnotes> footnotes = new List<Footnotes>();
foreach (var areaMap in coe.Area_CoE_Map.Where(x=>x.Fiscal_Year == fiscalYear).OrderBy(x => x.Area.Sort))
{
tempStream = new MemoryStream();
wb.Save(tempStream, SaveOptions.XlsxDefault);
wbTemp = new XLWorkbook(tempStream);
wsTemp = wbTemp.Worksheet(ws.Name);
var cellLengthObjective = 0;
var prArea = wsTemp.Range(wsTemp.Cell(currentRow, 1), wsTemp.Cell(currentRow, maxCol));
//fitAdjustableRows.Add(currentRow);
prArea.Merge();
prArea.Style.Fill.BackgroundColor = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.Style.Font.FontColor = ClosedXML.Excel.XLColor.FromColor(prAreaFont);
prArea.FirstCell().RichText.AddText(areaMap.Area.Area).Bold = true;
cellLengthObjective += areaMap.Area.Area.Length;
if (ws == wsPR)
{
var indent = new string('_', indentLength);
var innerIndent = new string('_', innerIndentLength);
var firstIndent = indent.Substring(0, firstIndentLength - areaMap.Area.Area.Length);
var stringSeperators = new string[] { "•" };
if (areaMap.Objective != null)
{
var objectives = areaMap.Objective.Split(stringSeperators, StringSplitOptions.None);
for (var i = 1; i < objectives.Length; i++)
{
if (i == 1)
{
prArea.FirstCell().RichText.AddText(firstIndent).SetFontColor( ClosedXML.Excel.XLColor.FromColor(prAreaFill)).SetFontSize(prAreaObjectiveFontsize);
cellLengthObjective += firstIndent.Length;
}
//var innerIndentAdj = new string('_', maxObjectiveLength < objectives[i].Length ? 0 : maxObjectiveLength - objectives[i].Length);
var innerIndentAdj = "";
cellLengthObjective += objectives[i].Length + innerIndent.Length + innerIndentAdj.Length;
if (cellLengthObjective > prObjectivesCharsNewLine)
{
prArea.FirstCell().RichText.AddNewLine();
ws.Rows[currentRow].Height += (int)newLineHeight;
prArea.FirstCell().RichText.AddText(indent).FontColor = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstCell().RichText.AddText(indent).SetFontColor( ClosedXML.Excel.XLColor.FromColor(prAreaFont)).SetFontSize(prAreaObjectiveFontsize);
cellLengthObjective = indent.Length;
}
prArea.FirstCell().RichText.AddText(innerIndent + innerIndentAdj).FontColor = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstCell().RichText.AddText(" •" + objectives[i]).FontSize = prAreaObjectiveFontsize;
cellLengthObjective += objectives[i].Length;
}
}
}
wbTemp.SaveAs(tempStream);
wb = ExcelFile.Load(tempStream, LoadOptions.XlsxDefault);
currentRow++;
foreach (var map in viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number))
{
fitAdjustableRows.Add(currentRow);
currentCol = 1;
ws.Cells[currentRow, currentCol].Value = indicatorNumber;
indicatorNumber++;
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
currentCol++;
int j = 0;
ws.Cells[currentRow, currentCol].Value = map.Indicator.Indicator;
foreach (var footnote in map.Indicator.Indicator_Footnote_Map.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator_ID == map.Indicator_ID).OrderBy(e => e.Indicator_ID))
{
if (!footnotes.Contains(footnote.Footnote)) { footnotes.Add(footnote.Footnote); }
if (j != 0)
{
//ws.Cells[currentRow, currentCol].RichText.AddText(",").VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
//ws.Cell(currentRow, currentCol).RichText.AddText(footnote.Footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
j++;
}
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
currentCol++;
if (ws.Name == wsPRName)
{
for (var i = 3; i <= 15; i++)
{
ws.Columns[i].Width = ws.Name == wsPRName ? prValueWidth : prDefWidth;
}
var obj = map.Indicator;
var type = obj.GetType();
string[,] columnIndicators = new string[,]{
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator_Sup").GetValue(obj,null),
""
},
};
var startCol = currentCol;
int k = 1;
for (var i = 0; i <= columnIndicators.GetUpperBound(0); i++)
{
for (j = 0; j <= columnIndicators.GetUpperBound(1); j++)
{
if (columnIndicators[i, j] != null)
{
columnIndicators[i, j] = columnIndicators[i, j].Replace("<b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sub>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sub>", "");
}
}
if (i != columnIndicators.GetUpperBound(0) && columnIndicators[i, 0] == "=")
{
k = 1;
while (columnIndicators[i + k, 0] == "=") { k++; }
ws.Cells.GetSubrangeAbsolute(currentRow, startCol + i - 1, currentRow, startCol + i + k - 1).Merged = true;
i += k - 1;
k = 1;
}
else if (columnIndicators[i, 0] != "=")
{
var cell = ws.Cells[currentRow, currentCol + i];
string cellValue = "";
if (columnIndicators[i, 0] != null)
{
cellValue = columnIndicators[i, 0].ToString();
}
if (cellValue.Contains("$"))
{
}
cell.Value = "'" + cellValue;
if (columnIndicators[i, 1] != null)
{
//cell.RichText.AddText(columnIndicators[i, 1]).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
switch (columnIndicators[i, 2])
{
case "cssWhite":
//cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Font.Color = ExcelColor.Black;
cell.Style.FillPattern.SetSolid(ExcelColor.White);
break;
case "cssGreen":
cell.Style.Font.Color = ExcelColor.White;
cell.Style.FillPattern.SetSolid(prGreen);
break;
case "cssYellow":
cell.Style.Font.Color = ExcelColor.Black;
cell.Style.FillPattern.SetSolid(prYellow);
break;
case "cssRed":
cell.Style.Font.Color = ExcelColor.White;
cell.Style.FillPattern.SetSolid(prRed);
break;
}
cell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
}
}
currentRow++;
}
else if (ws.Name == wsDefName)
{
ws.Columns[3].Width = prDefWidth;
ws.Columns[4].Width = prRatiWidth;
ws.Columns[5].Width = prCompWidth;
var obj = map.Indicator;
var type = obj.GetType();
string defn = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation").GetValue(obj, null);
string rationale = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale").GetValue(obj, null);
string comp = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source").GetValue(obj, null);
double maxLines = 1;
double lines;
if (defn != null)
{
lines = defn.Length / ws.Columns[currentCol].Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = defn;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (rationale != null)
{
lines = rationale.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = rationale;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (comp != null)
{
lines = comp.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = comp;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
ws.Row(currentRow).Height = newLineHeight*Math.Ceiling(maxLines);
currentRow++;
}
}
}
var footnoteRow = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
footnoteRow.Merge();
footnoteRow.Style.Font.FontSize = prFootnoteSize;
/*Footnotes defaultFootnote = db.Footnotes.FirstOrDefault(x => x.Footnote_Symbol == "*");
if (!footnotes.Contains(defaultFootnote))
{
footnotes.Add(defaultFootnote);
}*/
int cellLengthFootnote = 0;
if (ws.Name == wsPRName)
{
foreach (var footnote in footnotes)
{
ws.Cell(currentRow, 1).RichText.AddText(footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
ws.Cell(currentRow, 1).RichText.AddText(" " + footnote.Footnote + ";");
ws.Cell(currentRow, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
cellLengthFootnote += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLengthFootnote > prFootnoteCharsNewLine)
{
ws.Cell(currentRow, 1).RichText.AddNewLine();
cellLengthFootnote = 0;
ws.Row(currentRow).Height += newLineHeight;
}
}
}
else
{
ws.Cell(currentRow, 1).Value = defNote;
ws.Row(currentRow).Height = 28;
}
var pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow - 1, maxCol));
pr.Style.Border.InsideBorder = prBorderWidth;
pr.Style.Border.InsideBorderColor = prBorder;
pr.Style.Border.OutsideBorder = prBorderWidth;
pr.Style.Border.OutsideBorderColor = prBorder;
pr.Style.Font.FontSize = prFontSize;
pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow, maxCol));
pr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
pr.Style.Alignment.WrapText = true;
ws.Column(1).Width = prNumberWidth;
ws.Column(2).Width = prIndicatorWidth;
footnotes.Clear();
indicatorNumber = 1;
var totalHeight = ExcelFunctions.getTotalHeight(ws,4);
var totalWidth = ExcelFunctions.getTotalWidth(ws,1);
var fitHeight = (int)(totalWidth / fitRatio);
var fitWidth = (int)(totalHeight * fitRatio);
if (ws.Name == "Def_WIH Obs") { System.Diagnostics.Debugger.Break(); }
if (fitHeight > totalHeight)
{
var fitAddHeightTotal = (fitHeight - totalHeight);
var fitAddHeightPerRow = fitAddHeightTotal / fitAdjustableRows.Count;
foreach (var row in fitAdjustableRows)
{
ws.Row(row).Height += fitAddHeightPerRow;
}
}
else
{
while ((fitWidth - totalWidth) / fitWidth > 0.001 )
{
var fitAddWidthTotal = (fitWidth - totalWidth) / 10;
var fitAddWidthPerRow = fitAddWidthTotal / (ws.LastColumnUsed().ColumnNumber() - 1);
foreach (var col in ws.Columns(2, ws.LastColumnUsed().ColumnNumber()))
{
col.Width += fitAddWidthPerRow / 5.69;
}
ExcelFunctions.AutoFitWorksheet(ws, 2, 3, newLineHeight);
totalHeight = ExcelFunctions.getTotalHeight(ws, 4);
totalWidth = ExcelFunctions.getTotalWidth(ws, 1);
fitHeight = (int)(totalWidth / fitRatio);
fitWidth = (int)(totalHeight * fitRatio);
}
}
}
}
MemoryStream preImage = new MemoryStream();
//wb.SaveAs(preImage);
wb.SaveAs(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test.xlsx"));
var test = new ExcelToPdf();
//test.ConvertFile(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test.xlsx"), this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test.pdf"));
MemoryStream postImage = new MemoryStream();
SLDocument postImageWb = new SLDocument(preImage);
string picPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logo.png");
SLPicture picLogo = new SLPicture(picPath);
string picPathOPEO = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logoOPEO.png");
SLPicture picLogoOPEO = new SLPicture(picPathOPEO);
string picMonthlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/Monthly.png");
SLPicture picMonthly = new SLPicture(picMonthlyPath);
string picQuaterlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/quaterly.png");
SLPicture picQuaterly = new SLPicture(picQuaterlyPath);
string picNAPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/na.png");
SLPicture picNA = new SLPicture(picNAPath);
string picTargetPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/target.png");
SLPicture picTarget = new SLPicture(picTargetPath);
foreach (var ws in wb.Worksheets)
{
postImageWb.SelectWorksheet(ws.Name);
for (int i = 1; i < 20; ++i)
{
var a = postImageWb.GetRowHeight(i);
}
picLogo.SetPosition(0, 0);
picLogo.ResizeInPercentage(25, 25);
postImageWb.InsertPicture(picLogo);
picLogoOPEO.SetRelativePositionInPixels(0, ws.LastColumnUsed().ColumnNumber() + 1, -140, 0);
picLogoOPEO.ResizeInPercentage(45, 45);
postImageWb.InsertPicture(picLogoOPEO);
if (ws.Name.Substring(0, 3) != "Def")
{
picTarget.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -240, 1);
picNA.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -400, 1);
picMonthly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -500, 1);
picQuaterly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -490, 1);
picMonthly.ResizeInPercentage(70, 70);
picQuaterly.ResizeInPercentage(70, 70);
picNA.ResizeInPercentage(70, 70);
picTarget.ResizeInPercentage(70, 70);
postImageWb.InsertPicture(picMonthly);
postImageWb.InsertPicture(picQuaterly);
postImageWb.InsertPicture(picNA);
postImageWb.InsertPicture(picTarget);
}
}
// Prepare the response
HttpResponse httpResponse = this.HttpContext.ApplicationInstance.Context.Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.xlsx\"");
//httpResponse.ContentType = "application/pdf";
//httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.pdf\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
postImageWb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
return View(viewModel);
}
public byte[] GenerarReciboPropietario(PagoAlquiler Pago, Contratos Contrato)
{
OleDbConnection conexion = null;
string PATH_DIRECTORIO_ARCHIVOS = ConfigurationManager.AppSettings["PathArchivosOriginal"];
string ReciboPropietario = ConfigurationManager.AppSettings["ArchivoReciboPropietario"];
string PATH_DIRECTORIO_TEMP = ConfigurationManager.AppSettings["PathArchivosTemp"];
string NombreArchFinal = ReciboPropietario.Split('.')[0] + Pago.Periodo.Value.ToString("MMyy") + Pago.InquilinoId.ToString() + Pago.PropiedadId.ToString();
try
{
string ArchivoOriginalRecibo = PATH_DIRECTORIO_ARCHIVOS + ReciboPropietario;
string ArchivoFinalRecibo = PATH_DIRECTORIO_TEMP + NombreArchFinal + "." + ReciboPropietario.Split('.')[1];
if (File.Exists(ArchivoFinalRecibo))
{
File.Delete(ArchivoFinalRecibo);
}
File.Copy(ArchivoOriginalRecibo, ArchivoFinalRecibo);
string cadenaConexion = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ArchivoFinalRecibo + ";Extended Properties='Excel 12.0 Xml;HDR=NO';";
using (conexion = new OleDbConnection(cadenaConexion))
{
conexion.Open();
using (OleDbCommand comando = conexion.CreateCommand())
{
comando.CommandText = "UPDATE [Hoja1 0$E2:E2] SET F1= '" + Pago.FechaPago.Value.ToShortDateString() + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$L2:L2] SET F1= '" + Pago.FechaPago.Value.ToShortDateString() + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$B7:B7] SET F1= '" + Pago.Propiedad.Domicilio.Calle + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$I7:I7] SET F1= '" + Pago.Propiedad.Domicilio.Calle + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$B8:B8] SET F1= '" + Pago.Propiedad.Personas.Apellido + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$I8:I8] SET F1= '" + Pago.Propiedad.Personas.Apellido + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$A13:A13] SET F1= '" + Funciones.NumeroALetras(Pago.MontoTotal.ToString()) + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$H13:H13] SET F1= '" + Funciones.NumeroALetras(Pago.MontoTotal.ToString()) + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$D14:D14] SET F1= '" + Enum.GetName(typeof(Meses), Pago.Periodo.Value.Month) + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$K14:K14] SET F1= '" + Enum.GetName(typeof(Meses), Pago.Periodo.Value.Month) + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$A28:A28] SET F1= '" + Pago.Observaciones + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$H28:H28] SET F1= '" + Pago.Observaciones + "'";
comando.ExecuteNonQuery();
if (Pago.DetallePago.Any(xx => xx.TipoId == (int)TipoImpuestoServicio.Alquiler))
{
var alquiler = Pago.DetallePago.First(x => x.TipoId == (int)TipoImpuestoServicio.Alquiler);
comando.CommandText = "UPDATE [Hoja1 0$C17:C17] SET F1= '" + Enum.GetName(typeof(Meses), alquiler.PeriodoPago.Month) + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$J17:J17] SET F1= '" + Enum.GetName(typeof(Meses), alquiler.PeriodoPago.Month) + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$C18:C18] SET F1= '" + Contrato.PorcentajeInmobiliaria + "'";
comando.ExecuteNonQuery();
comando.CommandText = "UPDATE [Hoja1 0$J18:J18] SET F1= '" + Contrato.PorcentajeInmobiliaria + "'";
comando.ExecuteNonQuery();
}
}
conexion.Close();
conexion.Dispose();
conexion = null;
}
SautinSoft.ExcelToPdf pdf = new ExcelToPdf();
pdf.OutputFormat = SautinSoft.ExcelToPdf.eOutputFormat.Pdf;
var response = pdf.ConvertFiletoBytes(ArchivoFinalRecibo);
return(response);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
