public Excel2(String titolo) { excelWorkBook = excelApplication.Workbooks.Add(); excelApplication.DisplayAlerts = false; filename = @"C:\report\" + titolo + "_" + DateTime.Today.Month + "-" + DateTime.Today.Year + ".xls"; excelApplication.Cells[1, 1] = "Applicazione"; excelApplication.Cells[1, 2] = "Data"; excelApplication.Cells[1, 3] = "Linguaggio"; excelApplication.Cells[1, 4] = "Files"; excelApplication.Cells[1, 5] = "Righe"; excelApplication.Cells[1, 6] = "Utenti"; ((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 1]).EntireRow.Font.Bold = true; ((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 1]).EntireColumn.AutoFit(); ((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 2]).ColumnWidth = 10; ((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 3]).EntireColumn.AutoFit(); ((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 4]).EntireColumn.AutoFit(); ((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 5]).EntireColumn.AutoFit(); ((Microsoft.Office.Interop.Excel.Range)excelApplication.Cells[1, 6]).EntireColumn.AutoFit(); excelWorkBook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value); }
public void SpremanjeFaktura() { string OtvorenaFaktura = Application.StartupPath + "\\CMR\\CMR.xlsx"; //unos podataka ExcelObj = new Microsoft.Office.Interop.Excel.Application(); theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); sheets = theWorkbook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); excelApp.Workbooks.Open(OtvorenaFaktura); var BrojOtpremnice = (worksheet.Cells[1, 10] as Microsoft.Office.Interop.Excel.Range).Value; int NoviBrojOtpremnice = Convert.ToInt16(BrojOtpremnice) + 1; excelApp.Cells[1, 10] = NoviBrojOtpremnice; excelApp.ActiveWorkbook.Save(); theWorkbook.Close(0); excelApp.Quit(); foreach (Process proc in Process.GetProcessesByName("EXCEL")) { proc.Kill(); } Process.Start(OtvorenaFaktura); }
public ExcelWriter(string filename, ReportType type) { Report = type; oApplication = new Microsoft.Office.Interop.Excel.Application(); oWorkbook = null; oWorksheet = null; string outputPath = Filename = System.Configuration.ConfigurationManager.AppSettings["OutputLocation"]; if (type == ReportType.MonthyTally) { Filename = Path.Combine(outputPath, "MonthlyTally"); } if (!Directory.Exists(Filename)) Directory.CreateDirectory(Filename); Filename = Path.Combine(Filename, filename); if (File.Exists(Filename)) File.Delete(Filename); logger = Logger.CreateLogger(); }
/// <summary> /// Initialize File /// </summary> private void CreateExcel() { app = new Excel.Application(); workbook = app.Workbooks.Open(ExcelFilePath, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheets = workbook.Sheets; //get first sheet of file excel worksheet = (Microsoft.Office.Interop.Excel.Worksheet)worksheets.get_Item(1); }
private double WriteMonthlyTally(List<Donation> donations) { double total = 0.0; try { if (oApplication == null) { logger.WriteError("Unable to start an excel sheet"); return total; } oApplication.Visible = false; oWorkbook = oApplication.Workbooks.Add(Type.Missing); oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Worksheets[1]; //oWorksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; FormatFont(); int maxRowDepth = 0; // Fill in all the columns (categories) with the donations foreach (Donation.Category currentCategory in Enum.GetValues(typeof(Donation.Category))) { int row = 1; oWorksheet.Cells[row, (int)currentCategory] = currentCategory.ToString(); foreach (var item in donations) { string value = item.Get(currentCategory).ToString(); if (value.Equals("0")) continue; oWorksheet.Cells[++row, (int)currentCategory] = value; if (currentCategory == Donation.Category.Other) { oWorksheet.Cells[1, (int)currentCategory + 1] = "Specify"; oWorksheet.Cells[row, (int)currentCategory + 1] = item.OtherCategory; } } // keep a running total of how deep we go (rows) into the spreadsheet maxRowDepth = row > maxRowDepth ? row : maxRowDepth; } int totalsRow = CalculateTotals(2, maxRowDepth, out total); // Some formatting of row 1 (font and bold) oWorksheet.Rows[1, Type.Missing].Font.Size = 9; oWorksheet.Rows[1, Type.Missing].Font.Bold = true; // Totals row bold. oWorksheet.Cells[totalsRow, Type.Missing].Font.Bold = true; // Calculations at the end of the file (jeffersonville data...) oWorksheet.Cells[totalsRow + 3, 3] = "Total of Tithe & Offering:"; Microsoft.Office.Interop.Excel.Range r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 3, 1], oWorksheet.Cells[totalsRow + 3, 3]]; MergeAndAlignRight(r); Microsoft.Office.Interop.Excel.Range TitheOffering = oWorksheet.Cells[totalsRow + 3, 4]; TitheOffering.Formula = string.Format("=SUM({0},{1})", oWorksheet.Cells[totalsRow, (int)Donation.Category.Tithes].Address, oWorksheet.Cells[totalsRow, (int)Donation.Category.Offering].Address); AddBoxAroundRange(TitheOffering); SetCurrencyFormat(TitheOffering); oWorksheet.Cells[totalsRow + 1, 8] = "Jeffersonville 10%:"; r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 1, 6], oWorksheet.Cells[totalsRow + 1, 8]]; MergeAndAlignRight(r); Microsoft.Office.Interop.Excel.Range Jeff10 = oWorksheet.Cells[totalsRow + 1, 10]; Jeff10.Formula = string.Format("=({0}*0.1)", TitheOffering.Address); AddBoxAroundRange(Jeff10); SetCurrencyFormat(Jeff10); oWorksheet.Cells[totalsRow + 2, 8] = "Jeffersonville First Fruits:"; r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 2, 6], oWorksheet.Cells[totalsRow + 2, 8]]; MergeAndAlignRight(r); Microsoft.Office.Interop.Excel.Range JeffFF = oWorksheet.Cells[totalsRow + 2, 10]; JeffFF.Formula = string.Format("=({0}*0.5)", oWorksheet.Cells[totalsRow, (int)Donation.Category.FirstFruits].Address); AddBoxAroundRange(JeffFF); SetCurrencyFormat(JeffFF); oWorksheet.Cells[totalsRow + 3, 8] = "Jeffersonville Total Tithes & First Fruits:"; r = oWorksheet.Range[oWorksheet.Cells[totalsRow + 3, 5], oWorksheet.Cells[totalsRow + 3, 8]]; MergeAndAlignRight(r); oWorksheet.Cells[totalsRow + 3, 10].Formula = string.Format("=SUM({0},{1})", Jeff10.Address, JeffFF.Address); AddBoxAroundRange(oWorksheet.Cells[totalsRow + 3, 10]); SetCurrencyFormat(oWorksheet.Cells[totalsRow + 3, 10]); int lastRow = totalsRow + 3; int lastCol = (int)Donation.Category.Other + 1; // Format cells to have boxes and set print area AddBoxAroundEachCellInRange(oWorksheet.Range[oWorksheet.Cells[1,1], oWorksheet.Cells[totalsRow,(int)Donation.Category.Other +1]]); SetPrintArea(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[lastRow, lastCol]]); //oWorksheet.Columns[Type.Missing, lastCol].PageBreak = Microsoft.Office.Interop.Excel.XlPageBreak.xlPageBreakManual; // set the return value equal to the total calculated } catch (Exception e ) { logger.WriteError("Failure while writing excel file. Message: {0}, Stack: {1}", e.Message, e.StackTrace); } finally { Cleanup(); } return total; }
/// <summary> /// Expecting to get a list of donation for a single doner /// </summary> /// <param name="donations"></param> /// <returns></returns> private double WriteYearlyDonerReport(List<Donation> donations, Doner doner) { double total = 0.0; try { if (oApplication == null) { logger.WriteError("Unable to start an excel sheet"); return total; } oApplication.Visible = false; oWorkbook = oApplication.Workbooks.Add(Type.Missing); oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Worksheets[1]; FormatFont(); int maxRowDepth = 0; int donationStartingRow = 10; MergeColumns(1, 1, 10); oWorksheet.Cells[1, 1] = "Christ Gospel Church of Tacoma WA"; MergeColumns(2, 1, 10); oWorksheet.Cells[2, 1] = "3909 Steilacoom Blvd SW"; MergeColumns(3, 1, 10); oWorksheet.Cells[3, 1] = "Lakewood, WA 98499"; MergeColumns(4, 1, 10); oWorksheet.Cells[4, 1] = "(253) 584-3904"; MergeColumns(6, 4, 8); oWorksheet.Cells[6, 4] = String.Format("As of {0:dd/MM/yyyy}", donations.Last().DonationTime.AddMonths(1).AddDays(-1.0)); MergeColumns(8, 1, 10); oWorksheet.Cells[8, 1] = String.Format("{0}:", doner.Name); // Fill in all the columns (categories) with the donations foreach (Donation.Category currentCategory in Enum.GetValues(typeof(Donation.Category))) { int row = donationStartingRow; oWorksheet.Cells[row, (int)currentCategory] = currentCategory.ToString(); foreach (var item in donations) { string value = item.Get(currentCategory).ToString(); if (value.Equals("0")) value = String.Empty; oWorksheet.Cells[++row, (int)currentCategory] = value; } // keep a running total of how deep we go (rows) into the spreadsheet maxRowDepth = row > maxRowDepth ? row : maxRowDepth; } // Fill out the first column int rowNum = donationStartingRow; oWorksheet.Cells[rowNum, 1] = "Date"; foreach (var item in donations) { oWorksheet.Cells[++rowNum, 1] = String.Format("{0:MM/yyyy}", item.DonationTime); } // donationStartingRow + 1 == where the actual donations start (first row is column name) int totalsRow = CalculateTotals(donationStartingRow+1, donationStartingRow + 1 + donations.Count , out total); // Some formatting of Donation Name row (font and bold) oWorksheet.Rows[donationStartingRow, Type.Missing].Font.Size = 9; oWorksheet.Rows[donationStartingRow, Type.Missing].Font.Bold = true; // Totals row bold. oWorksheet.Cells[totalsRow, Type.Missing].Font.Bold = true; rowNum = totalsRow + 4; MergeColumns(++rowNum, 1, 13); oWorksheet.Cells[rowNum, 1] = "The goods or services that Christ Gospel Church of Tacoma provided in return for your contribution consisted entirely of intangible religious benefits."; ++rowNum; ++rowNum; oWorksheet.Cells[rowNum, 1] = "Sincerely,"; oWorksheet.Cells[++rowNum, 1] = "Treasury Department"; int lastCol = (int)Donation.Category.Other + 1; SetPrintArea(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[rowNum, lastCol]]); } catch (Exception e) { logger.WriteError("Failure while writing excel file. Message: {0}, Stack: {1}", e.Message, e.StackTrace); } finally { Cleanup(); } return total; }
private double WriteYearlyDonerReportBackup(List<Donation> donations) { double total = 0.0; try { if (oApplication == null) { logger.WriteError("Unable to start an excel sheet"); return total; } oApplication.Visible = false; oWorkbook = oApplication.Workbooks.Add(Type.Missing); oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Worksheets[1]; FormatFont(); int maxRowDepth = 0; foreach (Donation.Category currentCategory in Enum.GetValues(typeof(Donation.Category))) { int row = 1; oWorksheet.Cells[row, (int)currentCategory] = currentCategory.ToString(); foreach (var item in donations) { oWorksheet.Cells[++row, (int)currentCategory] = item.Get(currentCategory).ToString(); if (currentCategory == Donation.Category.Other) { oWorksheet.Cells[1, (int)currentCategory + 1] = "Specify"; oWorksheet.Cells[row, (int)currentCategory + 1] = item.OtherCategory; } } maxRowDepth = row > maxRowDepth ? row : maxRowDepth; } int totalsRow = CalculateTotals(2, maxRowDepth, out total); // Fill out the first column int r = 1; oWorksheet.Cells[r, 1] = "Date"; foreach (var item in donations) { oWorksheet.Cells[++r, 1] = item.DonationTime.ToShortDateString(); } // Some formatting: oWorksheet.Rows[1, Type.Missing].Font.Size = 9; oWorksheet.Rows[1, Type.Missing].Font.Bold = true; oWorksheet.Cells[maxRowDepth + 3, 2].Font.Bold = true; oWorksheet.Columns[1].Item(1).ColumnWidth = 10.71; total = Double.Parse(oWorksheet.Cells[maxRowDepth + 3, 2].Value); int lastRow = totalsRow + 3; int lastCol = (int)Donation.Category.Other + 1; // Format cells to have boxes and set print area AddBoxAroundEachCellInRange(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[totalsRow, (int)Donation.Category.Other + 1]]); SetPrintArea(oWorksheet.Range[oWorksheet.Cells[1, 1], oWorksheet.Cells[lastRow, lastCol]]); } catch { } finally { Cleanup(); } return total; }
public void SpremanjeFaktura() { //unos podataka ExcelObj = new Microsoft.Office.Interop.Excel.Application(); excelApp = new Excel.Application(); theWorkbook = ExcelObj.Workbooks.Open(OtvorenaFaktura, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true); sheets = theWorkbook.Worksheets; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); int rowIndex, colIndex; excelApp.Workbooks.Open(OtvorenaFaktura); //DVO var qa = (worksheet.Cells[18, 6] as Microsoft.Office.Interop.Excel.Range).Value; string slovoqa = Convert.ToString(qa); if (slovoqa == "Datum valute:") { rowIndex = 17; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumDvo; } else rowIndex = 18; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumDvo; //valuta var qb = (worksheet.Cells[19, 6] as Microsoft.Office.Interop.Excel.Range).Value; string slovoqb = Convert.ToString(qb); if (slovoqb == "Vrijeme:") { rowIndex = 18; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumValuta; } else rowIndex = 19; colIndex = 8; excelApp.Cells[rowIndex, colIndex] = DatumValuta; //faktura var q = (worksheet.Cells[22, 6] as Microsoft.Office.Interop.Excel.Range).Value; string slovoq = Convert.ToString(q); if (slovoq == "OIB:" || slovoq == "oib:" || slovoq == "Oib:") { rowIndex = 21; colIndex = 7; excelApp.Cells[rowIndex, colIndex] = BrojFakture; } else rowIndex = 22; colIndex = 7; excelApp.Cells[rowIndex, colIndex] = BrojFakture; //cijena, količina, rabat i PDV for (int red = 26; red <= 31; red += 1) { var trazi = (worksheet.Cells[red, 6] as Microsoft.Office.Interop.Excel.Range).Value; string STRtrazi = Convert.ToString(trazi); float val; if (((float.TryParse(STRtrazi, out val))) && STRtrazi.Length != 0) { excelApp.Cells[red, 6] = CijenaFakture; excelApp.Cells[red, 5] = Kolicina; if (rabat != 0) { excelApp.Cells[red, 9] = Convert.ToDouble(rabat); excelApp.Cells[red, 10] = Convert.ToDouble(PDV); } else { excelApp.Cells[red, 9] = Convert.ToDouble(PDV); } break; } } //relacija string RedRelacija = File.ReadAllText(Application.StartupPath + "\\RedRelacija"); string StupacRelacija = File.ReadAllText(Application.StartupPath + "\\StupacRelacija"); excelApp.Cells[Convert.ToInt16(RedRelacija), Convert.ToInt16(StupacRelacija)] = Relacija; File.Delete(Application.StartupPath + "\\RedRelacija"); File.Delete(Application.StartupPath + "\\StupacRelacija"); //pozicija for (int stupac = 1; stupac <= 5; stupac++) { for (int red = 26; red <= 34; red++) { var trazi = (worksheet.Cells[red, stupac] as Microsoft.Office.Interop.Excel.Range).Value; string STRtrazi = Convert.ToString(trazi); Boolean equals = String.Equals(STRtrazi, "Pozicija:", StringComparison.OrdinalIgnoreCase); if (equals == true) { excelApp.Cells[red, stupac + 1] = Pozicija; //MessageBox.Show(red + " " + stupac); break; } } } var datum = Convert.ToDateTime(DatumDvo); int mjesec = datum.Month; int godina = datum.Year; FileInfo fInfo = new FileInfo(PutanjaOtvoreneFakture); string strFilePath = fInfo.DirectoryName; //plačeno,postojeća relacija DirectoryInfo dirPlaceno = new DirectoryInfo(strFilePath + "\\" + mjesec + "-" + godina + "\\PLAČENO\\"); string dir = Convert.ToString(dirPlaceno); //neplačeno,postojeća relacija DirectoryInfo dirNePlaceno = new DirectoryInfo(strFilePath + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\"); string dirNe = Convert.ToString(dirNePlaceno); //plačeno,nova relacija DirectoryInfo dirPlacenoNovo = new DirectoryInfo(PutanjaOtvoreneFakture + "\\" + Relacija+ "\\" + mjesec + "-" + godina + "\\PLAČENO\\"); string dirNovo1 = Convert.ToString(dirPlacenoNovo); string dirNovo = dirNovo1.Replace("\\\\", "\\"); //neplačeno,nova relacija DirectoryInfo dirNePlacenoNovo = new DirectoryInfo(PutanjaOtvoreneFakture + "\\" + Relacija + "\\" + mjesec + "-" + godina + "\\NEPLAČENO\\"); string dirNeNovo1 = Convert.ToString(dirNePlacenoNovo); string dirNeNovo = dirNeNovo1.Replace("\\\\", "\\"); string open; //nova ruta if (NovaRuta == true) { //stvori oba direktorija,PLAČENO i NEPLAČENO dirPlacenoNovo.Create(); dirNePlacenoNovo.Create(); if (Placeno == true) { excelApp.ActiveWorkbook.SaveCopyAs(dirNovo + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNovo + BrojFakture + " " + DatumDvo + ".xlsx"; } else { excelApp.ActiveWorkbook.SaveCopyAs(dirNeNovo + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNeNovo + BrojFakture + " " + DatumDvo + ".xlsx"; } } //normalna faktura,postojeća relacija else { //stvori oba direktorija,PLAČENO i NEPLAČENO dirNePlaceno.Create(); dirPlaceno.Create(); if (Placeno == true) { if (dirPlaceno.Exists) { excelApp.ActiveWorkbook.SaveCopyAs(dir + BrojFakture + " " + DatumDvo + ".xlsx"); open = dir + BrojFakture + " " + DatumDvo + ".xlsx"; } //Ako taj direktorij ne postoji->napravi ga else { excelApp.ActiveWorkbook.SaveCopyAs(dir + BrojFakture + " " + DatumDvo + ".xlsx"); open = dir + BrojFakture + " " + DatumDvo + ".xlsx"; } } else { if (dirNePlaceno.Exists) { excelApp.ActiveWorkbook.SaveCopyAs(dirNe + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNe + BrojFakture + " " + DatumDvo + ".xlsx"; } //Ako taj direktorij ne postoji->napravi ga else { excelApp.ActiveWorkbook.SaveCopyAs(dirNe + BrojFakture + " " + DatumDvo + ".xlsx"); open = dirNe + BrojFakture + " " + DatumDvo + ".xlsx"; } } } //Otvaranje spremljene fakture SpremljenaFaktura = open.Replace("\\", "\\\\"); SpremanjeZaSync(); Zatvaranje(); System.Diagnostics.Process.Start(SpremljenaFaktura); File.AppendAllText(Application.StartupPath + "\\Fakture\\data", Environment.NewLine + BrojFakture); }