private void ecxelPoster()
{
DateTime pReadDate = DateTime.Now;
string pSheetName = "Poster";
Excel.Application oXL = null;;
Excel._Workbook oWB;
Excel._Worksheet oSheetPoster;
Excel._Worksheet oSheetRegnskab;
Excel.Window oWindow;
Excel.Range oRng;
var rec_regnskab = Program.qryAktivRegnskab();
string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xls";
var JournalPoster = from h in Program.karPosteringer
join d1 in Program.karKontoplan on h.Konto equals d1.Kontonr into details1
from x in details1.DefaultIfEmpty()
orderby h.Nr
select new clsJournalposter
{
ds = (x.Type == "Drift") ? "D" : "S",
k = IUAP(x.Type, x.DK),
Konto = h.Konto.ToString() + "-" + x.Kontonavn,
Dato = h.Dato,
Bilag = h.Bilag,
Nr = h.Nr,
Id = h.Id,
Tekst = h.Tekst,
Beløb = h.Bruttobeløb,
};
var erMedlem = from h in Program.dbData3060.tblMedlems
where h.Status == 1
select h;
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//oXL.Visible = true; //For debug
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheetPoster = (Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
foreach (clsJournalposter m in JournalPoster)
{
this.MainformProgressBar.PerformStep();
row++;
Type objectType = m.GetType();
PropertyInfo[] properties = objectType.GetProperties();
int col = 0;
foreach (PropertyInfo property in properties)
{
col++;
string Name = property.Name;
//string NamePropertyType = property.GetValue(m, null).GetType().ToString();
oSheetPoster.Cells[row, col] = property.GetValue(m, null);
if (row == 2)
{
object[] CustomAttributes = property.GetCustomAttributes(false);
foreach (var att in CustomAttributes)
{
Type tp = att.GetType();
if (tp.ToString() == "nsPuls3060.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheetPoster.Cells[1, col] = heading;
}
}
}
}
}
oRng = (Excel.Range)oSheetPoster.Rows[1, Missing.Value];
oRng.Font.Name = "Arial";
oRng.Font.Size = 12;
oRng.Font.Strikethrough = false;
oRng.Font.Superscript = false;
oRng.Font.Subscript = false;
oRng.Font.OutlineFont = false;
oRng.Font.Shadow = false;
oRng.Font.Bold = true;
oRng.HorizontalAlignment = Excel.Constants.xlCenter;
oRng.VerticalAlignment = Excel.Constants.xlBottom;
oRng.WrapText = false;
oRng.Orientation = 0;
oRng.AddIndent = false;
oRng.IndentLevel = 0;
oRng.ShrinkToFit = false;
oRng.MergeCells = false;
string BottomRight = "D" + row.ToString();
oRng = oSheetPoster.get_Range("D2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
oSheetPoster.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.FreezePanes = true;
oSheetPoster.get_Range("A1", Missing.Value).Select();
oSheetRegnskab = (Excel._Worksheet)oWB.Worksheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
oRng = oSheetRegnskab.get_Range("C2", Missing.Value);
oRng.Formula = "Antal medlemmer: " + erMedlem.Count().ToString();
//oXL.Visible = true; //For debug
PivotField _pvtField = null;
PivotTable _pivot = oSheetPoster.PivotTableWizard(
XlPivotTableSourceType.xlDatabase, //SourceType
oSheetPoster.get_Range(oSheetPoster.Cells[1, 1], oSheetPoster.Cells[row, 9]), //SourceData
oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination
"PivotTable1", //TableName
System.Type.Missing, //RowGrand
System.Type.Missing, //CollumnGrand
System.Type.Missing, //SaveData
System.Type.Missing, //HasAutoformat
System.Type.Missing, //AutoPage
System.Type.Missing, //Reserved
System.Type.Missing, //BackgroundQuery
System.Type.Missing, //OptimizeCache
System.Type.Missing, //PageFieldOrder
System.Type.Missing, //PageFieldWrapCount
System.Type.Missing, //ReadData
System.Type.Missing); //Connection
_pvtField = (PivotField)_pivot.PivotFields("ds");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("k");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("Konto");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("Dato");
_pvtField.Orientation = XlPivotFieldOrientation.xlColumnField;
_pvtField = (PivotField)_pivot.PivotFields("Beløb");
_pvtField.Orientation = XlPivotFieldOrientation.xlDataField;
_pvtField.Function = XlConsolidationFunction.xlSum;
_pvtField.NumberFormat = "#,##0";
oSheetRegnskab.Name = "Regnskab";
oRng = oSheetRegnskab.get_Range("D3", Missing.Value);
oRng.Select();
bool[] Periods = { false, false, false, false, true, false, false };
oRng.Group(true, true, Missing.Value, Periods);
oRng = oSheetRegnskab.get_Range("D4", "P4");
oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
oSheetRegnskab.PageSetup.LeftHeader = "&14Regnskab Puls 3060";
oSheetRegnskab.PageSetup.CenterHeader = "";
oSheetRegnskab.PageSetup.RightHeader = "&P af &N";
oSheetRegnskab.PageSetup.LeftFooter = "&Z&F";
oSheetRegnskab.PageSetup.CenterFooter = "";
oSheetRegnskab.PageSetup.RightFooter = "&D&T";
oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75);
oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1);
oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5);
oSheetRegnskab.PageSetup.PrintHeadings = false;
oSheetRegnskab.PageSetup.PrintGridlines = true;
oSheetRegnskab.PageSetup.CenterHorizontally = false;
oSheetRegnskab.PageSetup.CenterVertically = false;
oSheetRegnskab.PageSetup.Orientation = XlPageOrientation.xlLandscape;
oSheetRegnskab.PageSetup.Draft = false;
oSheetRegnskab.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
oSheetRegnskab.PageSetup.FirstPageNumber = 1;
oSheetRegnskab.PageSetup.Order = XlOrder.xlDownThenOver;
oSheetRegnskab.PageSetup.BlackAndWhite = false;
oSheetRegnskab.PageSetup.Zoom = 100;
oSheetRegnskab.PageSetup.PrintErrors = XlPrintErrors.xlPrintErrorsDisplayed;
oWB.ShowPivotTableFieldList = false;
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
Excel._Worksheet oSheetWrk = (Excel._Worksheet)oWB.Worksheets.get_Item(i);
if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster"))
{
oSheetWrk.Delete();
}
}
oSheetRegnskab.get_Range("A1", Missing.Value).Select();
oWB.SaveAs(SaveAs, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
this.MainformProgressBar.Visible = false;
this.emailExcelFile(SaveAs, "Puls3060 Regnskab");
//oXL.Quit();
//oXL = null;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
