public void SheetInit()
{
//sheet 한개부터 시작하기 위해 삭제
Mws_Sheet = (Excel.Worksheet)Mwb_Book.Worksheets.get_Item(Mwb_Book.Worksheets.Count);
Mws_Sheet.Delete();
Mws_Sheet = (Excel.Worksheet)Mwb_Book.Worksheets.get_Item(Mwb_Book.Worksheets.Count);
Mws_Sheet.Delete();
}
/// <summary>
/// 创建一个Excel程序实例
/// </summary>
private List <Excel._Worksheet> CreateExcelRef(List <DataTable> tables)
{
List <Excel._Worksheet> sheets = new List <Excel._Worksheet>();
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
Excel._Worksheet sheet1 = (Excel._Worksheet)(_sheets.get_Item(1)); //删除Sheet1
Excel._Worksheet sheet2 = (Excel._Worksheet)(_sheets.get_Item(2)); //删除Sheet2
Excel._Worksheet sheet3 = (Excel._Worksheet)(_sheets.get_Item(3)); //删除Sheet3
foreach (var table in tables)
{
Excel._Worksheet sheet = _sheets.Add();
sheet.Name = table.TableName;
sheets.Add(sheet);
}
sheet1.Delete();
sheet2.Delete();
sheet3.Delete();
return(sheets);
}
public void DeleteSheet()
{
excelApp.DisplayAlerts = false;
CurrSheet.Delete();
excelApp.DisplayAlerts = true;
}
private void excelManagement()
{
DateTime pReadDate = DateTime.Now;
DateTime pRestance30Date = pReadDate.AddDays(-30);
string pSheetName = "MedlemManagement";
_Excel.Application oXL = null;;
_Excel._Workbook oWB;
_Excel._Worksheet oSheet;
_Excel.Window oWindow;
_Excel.Range oRng;
string rec_MedlemsLister = @"Z:\Medlemslister";
rec_MedlemsLister = Environment.ExpandEnvironmentVariables(rec_MedlemsLister);
string SaveAs = rec_MedlemsLister + @"\" + pSheetName + pReadDate.ToString("_yyyyMMdd_HHmmss") + ".xlsx";
List <clsMedlemExternAll> MedlemmerAll = new List <clsMedlemExternAll>();
var api = UCInitializer.GetBaseAPI;
var task1 = api.Query <Medlem>();
task1.Wait();
var Result1 = task1.Result;
foreach (var m in Result1)
{
clsMedlemExternAll recMedlem = new clsMedlemExternAll
{
Nr = int.Parse(m._KeyStr),
Navn = m._KeyName,
Adresse = m.Adresse,
Postnr = m.Postnr,
Bynavn = m.By,
Telefon = m.Mobil,
Email = m.Email,
Kon = m.Gender,
FodtAar = m.Fodtaar.ToString(),
MedlemTil = m.medlemtil,
Status = m.status
};
MedlemmerAll.Add(recMedlem);
}
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new _Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = oXL.Workbooks.Add((Missing.Value));
oSheet = (_Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheet.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = MedlemmerAll.Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
foreach (clsMedlemExternAll m in MedlemmerAll)
{
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();
oSheet.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() == "Medlem3060uc.Fieldattr")
{
Fieldattr attr = (Fieldattr)att;
string heading = attr.Heading;
oSheet.Cells[1, col] = heading;
}
}
}
}
}
oRng = (_Excel.Range)oSheet.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 = "J" + row.ToString();
oRng = oSheet.get_Range("J2", BottomRight);
oRng.NumberFormat = "dd-mm-yyyy";
oSheet.Cells.EntireColumn.AutoFit();
oWindow.SplitRow = 1;
oWindow.SplitColumn = 2;
oWindow.FreezePanes = true;
oSheet.get_Range("A1", Missing.Value).Select();
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
_Excel._Worksheet oSheetWrk = (_Excel._Worksheet)oWB.Worksheets.get_Item(i);
if (oSheetWrk.Name != "MedlemManagement")
{
oSheetWrk.Delete();
}
}
oWB.SaveAs(SaveAs, _Excel.XlFileFormat.xlWorkbookDefault, "", "", 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;
oXL.Quit();
oXL = null;
this.imapSaveExcelFile(SaveAs, "Puls3060 Medlems-oplysninger");
}
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");
}
}
}
public void DeleteSheet()
{
Mws_Sheet = (Excel.Worksheet)Mwb_Book.Worksheets.get_Item(Mwb_Book.Worksheets.Count);
Mws_Sheet.Delete();
}
public 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") + ".xlsx";
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.Nettobeløb,
};
using (new ExcelUILanguageHelper())
{
try
{
//Start Excel and get Application object.
oXL = new _Excel.Application();
//oXL.Visible = false;
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;
Program.frmMain.MainformProgressBar.Value = 0;
Program.frmMain.MainformProgressBar.Minimum = 0;
Program.frmMain.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count();
Program.frmMain.MainformProgressBar.Step = 1;
Program.frmMain.MainformProgressBar.Visible = true;
foreach (clsJournalposter m in JournalPoster)
{
Program.frmMain.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() == "Trans2Summa3060.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);
//oXL.Visible = true; //For debug
_Excel.Range x1 = oSheetPoster.Cells[1, 1];
_Excel.Range x2 = oSheetPoster.Cells[row, 9];
_Excel.Range xx = oSheetPoster.get_Range(x1, x2);
_Excel.PivotField _pvtField = null;
_Excel.PivotTable _pivot = oSheetPoster.PivotTableWizard(
_Excel.XlPivotTableSourceType.xlDatabase, //SourceType
xx, //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 = (_Excel.PivotField)_pivot.PivotFields("ds");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("k");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Konto");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Dato");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlColumnField;
_pvtField = (_Excel.PivotField)_pivot.PivotFields("Beløb");
_pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlDataField;
_pvtField.Function = _Excel.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, true };
oRng.Group(true, true, Missing.Value, Periods);
oRng = oSheetRegnskab.get_Range("D4", "P4");
oRng.HorizontalAlignment = _Excel.XlHAlign.xlHAlignRight;
oSheetRegnskab.PageSetup.LeftHeader = "&14" + rec_regnskab.Navn;
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 = _Excel.XlPageOrientation.xlLandscape;
oSheetRegnskab.PageSetup.Draft = false;
oSheetRegnskab.PageSetup.PaperSize = _Excel.XlPaperSize.xlPaperA4;
oSheetRegnskab.PageSetup.FirstPageNumber = 1;
oSheetRegnskab.PageSetup.Order = _Excel.XlOrder.xlDownThenOver;
oSheetRegnskab.PageSetup.BlackAndWhite = false;
oSheetRegnskab.PageSetup.Zoom = 100;
oSheetRegnskab.PageSetup.PrintErrors = _Excel.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.xlWorkbookDefault, "", "", false, false, _Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
oXL.Visible = true;
Program.frmMain.MainformProgressBar.Visible = false;
//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");
}
}
}
static void VagtplanExcel(string pSlutDT)
{
DateTime pReadDate = DateTime.Now;
string pSheetName = "Vagtplan";
string SaveAs = @"C:\Users\mha\Documents\" + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xls";
Excel.Application oXL = null;;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Window oWindow;
Excel.Range oRng;
Excel.Range oCell1;
Excel.Range oCell2;
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook
oWB = oXL.Workbooks.Add((Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oWindow = oXL.ActiveWindow;
if (pSheetName.Length > 0)
{
oSheet.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length);
}
int row = 1;
int col = 1;
DateTime StartDT = DateTime.Now;
DateTime SlutDT = DateTime.Parse(pSlutDT);
DateTime StartMD = new DateTime(StartDT.Year, StartDT.Month, 1);
clsTemplate objTemplate = new clsTemplate();
int Maxi = (SlutDT.Subtract(StartDT).Days / 28) + 1;
Dictionary <DateTime, int> dicMD = new Dictionary <DateTime, int>();
for (int i = 0; i < Maxi; i++)
{
dicMD.Add(StartMD.AddMonths(i), i);
}
oRng = oSheet.Columns[1];
oRng.ColumnWidth = 0.5;
for (DateTime dt = StartMD.Date; dt.Date <= SlutDT.Date; dt = dt.AddDays(1).Date)
{
DateTime dtMD = new DateTime(dt.Year, dt.Month, 1);
int spalte;
if (dicMD.TryGetValue(dtMD, out spalte))
{
col = 2 + spalte * 3;
if (dt.Day == 1)
{
row = 1;
oSheet.Cells[row, col] = dtMD;
oSheet.Cells[row, col].NumberFormat = "mmm-åååå";
oCell1 = oSheet.Cells[row, col] as Excel.Range;
oCell2 = oSheet.Cells[row, col + 1] as Excel.Range;
oRng = oSheet.get_Range(oCell1, oCell2);
oRng.MergeCells = true;
oRng.Font.Name = "Arial";
oRng.Font.Size = 10;
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;
oCell1 = oSheet.Cells[row + 1, col] as Excel.Range;
oCell2 = oSheet.Cells[row + 31, col] as Excel.Range;
oRng = oSheet.get_Range(oCell1, oCell2);
oRng.NumberFormat = "ddd d";
oCell1 = oSheet.Cells[row + 1, col + 1] as Excel.Range;
oCell2 = oSheet.Cells[row + 31, col + 1] as Excel.Range;
oRng = oSheet.get_Range(oCell1, oCell2);
oRng.HorizontalAlignment = Excel.Constants.xlCenter;
oCell1 = oSheet.Cells[row + 1, col] as Excel.Range;
oCell2 = oSheet.Cells[row + 31, col + 1] as Excel.Range;
oRng = oSheet.get_Range(oCell1, oCell2);
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].TintAndShade = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].TintAndShade = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
oRng = oSheet.Columns[col + 2];
oRng.ColumnWidth = 0.5;
}
row = dt.Day + 1;
recTemplate rec = objTemplate.getDag(dt);
oSheet.Cells[row, col] = dt.Date;
if (!rec.Fri)
{
oSheet.Cells[row, col + 1] = string.Format("'{0}-{1}", rec.Start.Value.Hours, rec.S**t.Value.Hours);
}
else
{
oSheet.Cells[row, col + 1] = "F";
}
if (dt.DayOfWeek == DayOfWeek.Sunday)
{
oCell1 = oSheet.Cells[row, col] as Excel.Range;
oCell2 = oSheet.Cells[row, col + 1] as Excel.Range;
oRng = oSheet.get_Range(oCell1, oCell2);
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].TintAndShade = 0;
oRng.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
}
}
}
oSheet.Cells.EntireColumn.AutoFit();
oSheet.get_Range("A1", Missing.Value).Select();
for (var i = oWB.Worksheets.Count; i > 0; i--)
{
Excel._Worksheet oSheetWrk = (Excel._Worksheet)oWB.Worksheets.get_Item(i);
if (oSheetWrk.Name != pSheetName)
{
oSheetWrk.Delete();
}
}
oSheet.PageSetup.LeftHeader = "&14Vagtplan for Alice";
oSheet.PageSetup.CenterHeader = "";
oSheet.PageSetup.RightHeader = "&P af &N";
oSheet.PageSetup.LeftFooter = "&Z&F";
oSheet.PageSetup.CenterFooter = "";
oSheet.PageSetup.RightFooter = "&D&T";
oSheet.PageSetup.LeftMargin = oXL.InchesToPoints(0.75);
oSheet.PageSetup.RightMargin = oXL.InchesToPoints(0.75);
oSheet.PageSetup.TopMargin = oXL.InchesToPoints(1.00);
oSheet.PageSetup.BottomMargin = oXL.InchesToPoints(0.75);
oSheet.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5);
oSheet.PageSetup.FooterMargin = oXL.InchesToPoints(0.5);
oSheet.PageSetup.PrintHeadings = false;
oSheet.PageSetup.PrintGridlines = false;
oSheet.PageSetup.CenterHorizontally = false;
oSheet.PageSetup.CenterVertically = false;
oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
oSheet.PageSetup.Draft = false;
oSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
oSheet.PageSetup.FirstPageNumber = 1;
oSheet.PageSetup.Order = Excel.XlOrder.xlDownThenOver;
oSheet.PageSetup.BlackAndWhite = false;
oSheet.PageSetup.Zoom = 100;
oSheet.PageSetup.PrintErrors = Excel.XlPrintErrors.xlPrintErrorsDisplayed;
oWB.SaveAs(SaveAs, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Saved = true;
}
/// <summary>
/// Кнопка экспорта
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonOK_Click(object sender, EventArgs e)
{
try
{
// Стартуем Excel-приложение
m_OXl = new Excel.Application();
// Создаем новую книгу
m_OWb = m_OXl.Workbooks.Add(Missing.Value);
m_OWs = (Excel._Worksheet)m_OWb.Sheets[3];
m_OWs.Delete();
m_OWs = (Excel._Worksheet)m_OWb.Sheets[2];
m_OWs.Delete();
m_OWs = (Excel._Worksheet)m_OWb.Sheets[1];
m_OWs.Cells.WrapText = true;
m_OWs.Cells.VerticalAlignment = 2;
m_OWs.Cells.HorizontalAlignment = 2;
m_OWr = m_OWs.get_Range("A1", "D1");
m_OWr.MergeCells = true;
m_OWr.Font.Bold = true;
m_OWr.Font.Size = 14;
m_OWr.RowHeight = 30;
m_OWr.HorizontalAlignment = 3;
m_OWs.Cells[1, 1] = "Список фильмов на " + DateTime.Now.ToShortDateString();
m_OWr = m_OWs.get_Range("A2", "A2");
m_OWr.ColumnWidth = 10;
m_OWr.Font.Bold = true;
m_OWr.HorizontalAlignment = 3;
m_OWr.Value2 = "Номер диска";
m_OWr = m_OWs.get_Range("B2", "B2");
m_OWr.ColumnWidth = 15;
m_OWr.Font.Bold = true;
m_OWr.HorizontalAlignment = 3;
m_OWr.Value2 = "Тип диска";
m_OWr = m_OWs.get_Range("C2", "C2");
m_OWr.ColumnWidth = 50;
m_OWr.Font.Bold = true;
m_OWr.HorizontalAlignment = 3;
m_OWr.Value2 = "Название фильма";
m_OWr = m_OWs.get_Range("D2", "D2");
m_OWr.ColumnWidth = 50;
m_OWr.Font.Bold = true;
m_OWr.HorizontalAlignment = 3;
m_OWr.Value2 = "Информация о фильме";
for (int i = 0; i < dataGridViewPreview.Rows.Count; i++)
{
m_OWs.Cells[i + 3, 1] = dataGridViewPreview.Rows[i].Cells[0].Value.ToString();
m_OWs.Cells[i + 3, 2] = dataGridViewPreview.Rows[i].Cells[1].Value.ToString();
m_OWs.Cells[i + 3, 3] = dataGridViewPreview.Rows[i].Cells[2].Value.ToString();
m_OWs.Cells[i + 3, 4] = dataGridViewPreview.Rows[i].Cells[3].Value.ToString().Replace("\r\n", "\n");
}
if (checkBoxWithoutFilmInfo.Checked)
{
m_OWr = m_OWs.get_Range("D1", "D" + (dataGridViewPreview.Rows.Count + 3));
m_OWr.Delete(-4159);
}
if (checkBoxWithoutDiskInfo.Checked)
{
m_OWr = m_OWs.get_Range("B1", "B" + (dataGridViewPreview.Rows.Count + 3));
m_OWr.Delete(-4159);
}
if (checkBoxWithoutNumber.Checked)
{
m_OWr = m_OWs.get_Range("A1", "A" + (dataGridViewPreview.Rows.Count + 3));
m_OWr.Delete(-4159);
}
Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (m_OXl != null)
{
m_OXl.Visible = true;
m_OXl.UserControl = true;
if (m_OWb != null)
{
Marshal.ReleaseComObject(m_OWb);
m_OWb = null;
}
if (m_OWs != null)
{
Marshal.ReleaseComObject(m_OWs);
m_OWs = null;
}
if (m_OWr != null)
{
Marshal.ReleaseComObject(m_OWr);
m_OWr = null;
}
Marshal.ReleaseComObject(m_OXl);
m_OXl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
}
private void createExcel(DataGridView DW1, DataGridView DW2, DataGridView DW3)
{
Excel.Application app = new Excel.Application();
if (app == null)
{
MessageBox.Show("Excel не установлен на вашем устройстве", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
app.DisplayAlerts = false;
Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Excel._Worksheet worksheet1 = null;
app.Visible = true;
try
{
worksheet1 = workbook.Sheets["Sheet1"];
}
catch (System.Runtime.InteropServices.COMException)
{
worksheet1 = workbook.Sheets["Лист1"];
}
Excel._Worksheet worksheet2 = null;
Excel._Worksheet worksheet3 = null;
worksheet2 = workbook.Sheets.Add(After: worksheet1);
worksheet3 = workbook.Sheets.Add(After: worksheet2);
worksheet1.Delete();
worksheet1 = workbook.Sheets.Add(Before: worksheet2);
worksheet1 = workbook.ActiveSheet;
// changing the name of active sheet
worksheet1.Name = "Categorii";
worksheet2.Name = "Produse";
worksheet3.Name = "Vinzari";
//_________________ DW1 __________________
// storing header part in Excel
for (int i = 1; i < DW1.Columns.Count + 1; i++)
{
worksheet1.Cells[1, i] = DW1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < DW1.Rows.Count - 1; i++)
{
for (int j = 0; j < DW1.Columns.Count; j++)
{
worksheet1.Cells[i + 2, j + 1] = DW1.Rows[i].Cells[j].Value.ToString();
}
}
//_________________ DW2 __________________
for (int i = 1; i < DW2.Columns.Count + 1; i++)
{
worksheet2.Cells[1, i] = DW2.Columns[i - 1].HeaderText;
}
for (int i = 0; i < DW2.Rows.Count - 1; i++)
{
for (int j = 0; j < DW2.Columns.Count; j++)
{
worksheet2.Cells[i + 2, j + 1] = DW2.Rows[i].Cells[j].Value.ToString();
}
}
//_________________ DW3 __________________
for (int i = 1; i < DW3.Columns.Count + 1; i++)
{
worksheet3.Cells[1, i] = DW3.Columns[i - 1].HeaderText;
}
for (int i = 0; i < DW3.Rows.Count - 1; i++)
{
for (int j = 0; j < DW3.Columns.Count; j++)
{
worksheet3.Cells[i + 2, j + 1] = DW3.Rows[i].Cells[j].Value.ToString();
}
}
// Summ Formule
worksheet3.Cells[1, 5] = "=SUM(C2:C200)";
var cellValue = (double)(worksheet3.Cells[1, 5] as Excel.Range).Value;
textBox1.Text = "Sum = " + cellValue.ToString();
SaveFileDialog saveFileDialog = new SaveFileDialog
{
FileName = "output",
DefaultExt = ".xlsx"
};
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
workbook.SaveAs(saveFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
// Exit from the application
try
{
app.Quit();
}
catch (System.Runtime.InteropServices.COMException)
{
MessageBox.Show(
"Не удалось сохранить файл",
"Ошибка!",
MessageBoxButtons.OK,
MessageBoxIcon.Information,
MessageBoxDefaultButton.Button1,
MessageBoxOptions.DefaultDesktopOnly
);
}
}
/// <summary>
/// Create excel document and put collected errors to it
/// </summary>
/// <param name="errorsList"></param>
private void PutDataToExcel(Dictionary <string, int> errorsList)
{
if (errorsList.Count == 0)
{
MessageBox.Show("No errors for selected period", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
labelInfo.Text = "Action: Put errors to excel document";
Application.DoEvents();
CultureInfo oldCi = Thread.CurrentThread.CurrentCulture;
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
// Стартуем Excel-приложение
m_OXl = new Excel.Application();
// Создаем новую книгу
m_OWb = m_OXl.Workbooks.Add(Missing.Value);
m_OWs = (Excel._Worksheet)m_OWb.Sheets[3];
m_OWs.Delete();
m_OWs = (Excel._Worksheet)m_OWb.Sheets[2];
m_OWs.Delete();
m_OWs = (Excel._Worksheet)m_OWb.Sheets[1];
m_OWs.Cells.WrapText = false;
m_OWs.Cells.VerticalAlignment = 1;
m_OWs.Cells.HorizontalAlignment = 2;
m_OWr = m_OWs.get_Range("A1", "B1");
m_OWr.MergeCells = true;
m_OWr.Font.Bold = true;
m_OWr.Font.Size = 14;
m_OWr.RowHeight = 30;
m_OWr.HorizontalAlignment = 3;
string computerName = textBoxComputerName.Text;
if (computerName == "." || computerName.ToLower() == "localhost")
{
computerName = Environment.MachineName;
}
m_OWs.Cells[1, 1] = "Errors on computer \"" + computerName + "\" between " + dateTimePickerStartDate.Value.ToString("dd.MM.yyyy HH:mm:ss") + " and " + dateTimePickerEndDate.Value.ToString("dd.MM.yyyy HH:mm:ss");
m_OWr = m_OWs.get_Range("A2", "A2");
m_OWr.ColumnWidth = 146;
m_OWr.Font.Bold = true;
m_OWr.HorizontalAlignment = 3;
m_OWr.Value2 = "Error message";
m_OWr = m_OWs.get_Range("B2", "B2");
m_OWr.ColumnWidth = 12;
m_OWr.Font.Bold = true;
m_OWr.HorizontalAlignment = 3;
m_OWr.Value2 = "Count of error";
int i = 3;
foreach (string error in errorsList.Keys)
{
m_OWr = m_OWs.get_Range(m_OWs.Cells[i, 1], m_OWs.Cells[i, 1]);
m_OWr.RowHeight = 70;
m_OWs.Cells[i, 1] = error;
m_OWs.Cells[i, 2] = errorsList[error];
i++;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (m_OXl != null)
{
m_OXl.Visible = true;
m_OXl.UserControl = true;
if (m_OWb != null)
{
Marshal.ReleaseComObject(m_OWb);
m_OWb = null;
}
if (m_OWs != null)
{
Marshal.ReleaseComObject(m_OWs);
m_OWs = null;
}
if (m_OWr != null)
{
Marshal.ReleaseComObject(m_OWr);
m_OWr = null;
}
Marshal.ReleaseComObject(m_OXl);
m_OXl = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
Thread.CurrentThread.CurrentCulture = oldCi;
}
}
/// <summary>
/// 将指定数据集里的表转换成工作簿里sheet
/// </summary>
/// <param name="starPos">数据表开始位置从0开始计数</param>
/// <param name="Count">要转换数据表的数目</param>
/// <returns>成功返回true</returns>
public bool ConvertSheet(int starPos, int Count)
{
System.Data.DataTable tempTable; //创建临时表
Excel.Application xApp = new Excel.Application();
xApp.Visible = false;
object objOpt = System.Reflection.Missing.Value;
Excel.Workbook xBook = xApp.Workbooks.Add(true);//添加新工作簿
Excel.Sheets xSheets = xBook.Sheets;
Excel._Worksheet xSheet = null;
bool ret = true;
try
{
//
//转换从指定起始位置以后一定数目的数据集
//
for (int i = starPos, iCount = 1; iCount <= Count && i < this.mDs.Tables.Count; i++, iCount++)
{
tempTable = this.mDs.Tables[i];
//
//创建空的sheet
//
xSheet = (Excel._Worksheet)(xBook.Sheets.Add(objOpt, objOpt, objOpt, objOpt));
ret = DataTable2Sheet(ref tempTable, ref xSheet, "");
if (ret == false)
{
break;
}
}
//
//获取默认生成的sheet并将其删除
//
//Excel._Worksheet tempXSheet = (Excel._Worksheet) (xSheets.get_Item(1)) ;
//
Excel._Worksheet tempXSheet = (Excel._Worksheet)(xBook.Worksheets[Count + 1]);
tempXSheet.Delete();
System.Runtime.InteropServices.Marshal.ReleaseComObject(tempXSheet);
tempXSheet = null;
//
//保存
//
xBook.Saved = true;
xBook.SaveCopyAs(this.mFilePath);
}
catch
{
ret = false;
}
finally
{
//
//释放资源
//
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSheet);
xSheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSheets);
xSheets = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBook);
xBook = null;
xApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
xApp = null;
GC.Collect();//强行销毁
}
return(ret);
}
public void Export(ExportedData[] exportedDataList)
{
CultureInfo oldCi = Thread.CurrentThread.CurrentCulture;
try
{
// Стартуем Excel-приложение
_application = new Excel.Application();
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
// Создаем новую книгу
_workbook = _application.Workbooks.Add(Missing.Value);
_worksheet = (Excel._Worksheet)_workbook.Sheets[3];
_worksheet.Delete();
_worksheet = (Excel._Worksheet)_workbook.Sheets[2];
_worksheet.Delete();
_worksheet = (Excel._Worksheet)_workbook.Sheets[1];
_worksheet.Cells.WrapText = true;
_worksheet.Cells.VerticalAlignment = 2;
_worksheet.Cells.HorizontalAlignment = 3;
_range = _worksheet.Range["A1", "D1"];
_range.Font.Bold = true;
_range = _worksheet.Range["A1", "A1"];
_range.Font.Size = 14;
_range.RowHeight = 30;
_range.ColumnWidth = 90;
_range.Value2 = "Название";
_range = _worksheet.Range["B1", "C1"];
_range.Font.Size = 12;
_range.ColumnWidth = 7;
_worksheet.Cells[1, 2] = "Время в сек";
_worksheet.Cells[1, 3] = "Время в мин";
_range = _worksheet.Range["D1", "D1"];
_range.Font.Size = 12;
_range.ColumnWidth = 14;
_range.Value2 = "% от общего выполнения";
_range = _worksheet.Range["A2", "A" + (exportedDataList.Length + 1)];
_range.HorizontalAlignment = 2;
for (int i = 0; i < exportedDataList.Length; i++)
{
_worksheet.Cells[i + 2, 1] = exportedDataList[i].Name;
_worksheet.Cells[i + 2, 2] = exportedDataList[i].TimeInSec;
_worksheet.Cells[i + 2, 3] = exportedDataList[i].TimeInMin;
_worksheet.Cells[i + 2, 4] = exportedDataList[i].Percent;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (_application != null)
{
_application.Visible = true;
_application.UserControl = true;
if (_workbook != null)
{
Marshal.ReleaseComObject(_workbook);
_workbook = null;
}
if (_worksheet != null)
{
Marshal.ReleaseComObject(_worksheet);
_worksheet = null;
}
if (_range != null)
{
Marshal.ReleaseComObject(_range);
_range = null;
}
Marshal.ReleaseComObject(_application);
_application = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
Thread.CurrentThread.CurrentCulture = oldCi;
}
}
}
