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);
}
