private void ProceedProductMonthCharts(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
double chartTop = productSheet.Rows[15].Top;
double chartWidth = productSheet.Columns[14].Left;
double chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top;
XlChartObject chartMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight);
chartMonths.Chart.SetSourceData(productSheet.Range("$A9:$M12"));
chartTop = productSheet.Rows[31].Top;
chartWidth = productSheet.Columns[14].Left;
chartHeight = productSheet.Rows[40].Top - productSheet.Rows[33].Top;
XlChartObject chartCountMonths = productSheet.ChartObjects.Add(1, chartTop, chartWidth, chartHeight);
chartCountMonths.Chart.ChartType = XlChartType.xlLine;
chartCountMonths.Chart.SetSourceData(productSheet.Range("$A13:$M13"));
double chartLeft = productSheet.Columns[15].Left;
chartTop = productSheet.Rows[15].Top;
chartWidth = productSheet.Columns[19].Left - productSheet.Columns[15].Left;
chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top;
XlChartObject chartCountYears = productSheet.ChartObjects.Add(chartLeft, chartTop, chartWidth, chartHeight);
chartCountYears.Chart.ChartType = XlChartType.xlCylinderColClustered;
chartCountYears.Chart.SetSourceData(productSheet.Range("$O9:$P12"));
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
// we need some data to display
XlRange dataRange = PutSampleData(workSheet);
// create a nice diagram
XlChartObject chart = workSheet.ChartObjects.Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example5{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private XlRange PutSampleData(XlWorksheet workSheet)
{
workSheet.Cells(2, 2).Value = "Datum";
workSheet.Cells(3, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(4, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(5, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(6, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(2, 3).Value = "Column1";
workSheet.Cells(3, 3).Value = 25;
workSheet.Cells(4, 3).Value = 33;
workSheet.Cells(5, 3).Value = 30;
workSheet.Cells(6, 3).Value = 22;
workSheet.Cells(2, 4).Value = "Column2";
workSheet.Cells(3, 4).Value = 25;
workSheet.Cells(4, 4).Value = 33;
workSheet.Cells(5, 4).Value = 30;
workSheet.Cells(6, 4).Value = 22;
workSheet.Cells(2, 5).Value = "Column3";
workSheet.Cells(3, 5).Value = 25;
workSheet.Cells(4, 5).Value = 33;
workSheet.Cells(5, 5).Value = 30;
workSheet.Cells(6, 5).Value = 22;
return(workSheet.Range("$B2:$E6"));
}
private static void WriteWorkbook(string fName)
{
Console.WriteLine("Creating workbook named [{0}]", fName);
XlWorkbook book = new XlWorkbook();
XlWorksheets sheets = book.Worksheets;
XlSchema schema = GetDeploymentSchema();
XlWorksheet sheet = sheets.AddWorksheet("DET_Deployments", XlColor.White, schema);
GetDeploymentRows(sheet);
schema = GetMeasurementSchema();
sheet = sheets.AddWorksheet("DET_Measurements", XlColor.White, schema);
GetMeasurementRows(sheet);
schema = GetSiteSchema();
sheet = sheets.AddWorksheet("LIST_Sites", XlColor.Orange, schema);
GetSiteRows(sheet);
schema = GetSensorSchema();
sheet = sheets.AddWorksheet("LIST_Sensors", XlColor.Orange, schema);
GetSensorRows(sheet);
XlCustomProperties properties = book.Properties;
properties.AddCustomProperty("oncorId", Guid.NewGuid().ToString());
properties.AddCustomProperty("oncorUser", "Dr. Frank N. Furter, ESQ");
book.Save(fName);
Console.WriteLine("Closing workbook named [{0}]", fName);
}
private static XlRows GetSensorRows(XlWorksheet sheet)
{
XlRows rows = sheet.Rows;
AddDummyRows(rows, sheet.Schema);
return(rows);
}
private void ProceedSummaryPrintSettings(XlWorksheet summarySheet)
{
summarySheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
summarySheet.PageSetup.Zoom = false;
summarySheet.PageSetup.FitToPagesTall = 1;
summarySheet.PageSetup.FitToPagesWide = 1;
}
private static void GenerateMergeCells(XlWorksheet ws)
{
// объединение ячеек для заголовка
ws.AppendMergeCell(
new String[] { "A1:C1", "A2:C2", "A3:C3" }
);
}
private static XlRows GetMeasurementRows(XlWorksheet sheet)
{
XlRows rows = sheet.Rows;
AddDummyRows(rows, sheet.Schema);
return(rows);
}
private void CheckHeaders(XlWorksheet worksheet)
{
if (worksheet != null)
{
Schema s;
List <string> h;
if (worksheet.Name == "DET_Deployments")
{
s = DeploymentDTO.GetSchema();
}
else if (worksheet.Name == "DET_Measurements")
{
s = MeasurementDTO.GetSchema();
}
else if (worksheet.Name == "LIST_Sites")
{
s = SiteDTO.GetSchema();
}
else if (worksheet.Name == "LIST_Sensors")
{
s = SensorDTO.GetSchema();
}
else
{
return;
}
h = new List <string>();
List <string> hdrs = ExcelBaseDet.Headers(worksheet);
bool bad = false;
for (int i = 0; i < hdrs.Count - 1; i++)
{
string t = hdrs[i];
for (int j = i + 1; j < hdrs.Count; j++)
{
if (t == hdrs[j])
{
bad = true;
break; //inner
}
}
if (bad)
{
break; //outer
}
}
if (bad)
{
generalDet.ValidationIssues.Add(ValidationIssue.Code.DuplicateHeader, "Duplicate column header in " + worksheet.Name);
}
foreach (SchemaEntry c in s)
{
h.Add(c.LowerColumnName);
}
if (!ExcelBaseDet.HasHeaders(hdrs, h))
{
generalDet.ValidationIssues.Add(ValidationIssue.Code.MissingFieldHeader, "Missing column header in " + worksheet.Name);
}
}
}
private static void AppendValueToSpreadsheetCell(XlWorksheet ws, UInt32 rowIndex, UInt32 columnIndex, Object value, Type type)
{
String cellValueText = null;
if (value != null && value != DBNull.Value)
{
switch (type.ToString())
{
case "System.Decimal":
ws.UpsertCell(rowIndex, columnIndex, 3, value);
break;
case "System.Double":
ws.UpsertCell(rowIndex, columnIndex, 3, value);
break;
case "System.Int32":
ws.UpsertCell(rowIndex, columnIndex, 4, value);
break;
default:
if (value != null)
{
cellValueText = value.ToString();
}
ws.UpsertCell(rowIndex, columnIndex, 2, cellValueText);
break;
}
}
else
{
ws.UpsertCell(rowIndex, columnIndex, 2, cellValueText);
}
}
private void ProceedProductPrintSettings(XlWorksheet productSheet)
{
productSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
productSheet.PageSetup.Zoom = false;
productSheet.PageSetup.FitToPagesTall = 1;
productSheet.PageSetup.FitToPagesWide = 1;
productSheet.PageSetup.PrintArea = "$A$1:$R$39";
}
private void ProceedSummaryMatrix(SalesReport report, XlWorksheet summarySheet, XlStyle matrixStyle)
{
// table columns
summarySheet.Range("B2").Value = "Count";
summarySheet.Range("C2").Value = "Revenue";
summarySheet.Range("D2").Value = "%";
summarySheet.Range("E2").Value = "Storage";
string leftBottomCellAdress = XlConverter.ToCellAdress(1, 3 + report.Products.Length);
string rightBottomCellAdress = XlConverter.ToCellAdress(5, 3 + report.Products.Length);
summarySheet.Range("$A2:$" + rightBottomCellAdress).Style = matrixStyle;
int rowIndex = 3;
int columnIndex = 1;
int i = 0;
foreach (SalesReportProduct itemProduct in report.Products)
{
string prodName = itemProduct.ProductName;
int prodId = itemProduct.ProductId;
summarySheet.Cells(rowIndex, columnIndex).Value = prodName;
string formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 13));
summarySheet.Cells(rowIndex, columnIndex + 1).Value = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, XlConverter.ToCellAdress(_monthToReport + 1, 12));
summarySheet.Cells(rowIndex, columnIndex + 2).Value = formula;
formula = string.Format("={0}*100/{1}", XlConverter.ToCellAdress(3, rowIndex), XlConverter.ToCellAdress(3, 3 + report.Products.Length));
summarySheet.Cells(rowIndex, columnIndex + 3).Formula = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, "B6");
summarySheet.Cells(rowIndex, columnIndex + 4).Value = formula;
int storeCount = Convert.ToInt16(summarySheet.Cells(rowIndex, columnIndex + 4).Value);
if ((i % 2) == 0)
{
summarySheet.Range("$A" + (i + 3).ToString() + ":$E" + (i + 3).ToString()).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
}
rowIndex++;
i++;
}
string sumFormula = string.Format("=Sum({0}:{1})", "C3", "C" + (report.Products.Length + 3 - 1).ToString());
summarySheet.Cells(rowIndex, columnIndex + 2).Value = sumFormula;
summarySheet.Range("$C3:$C" + (report.Products.Length + 3).ToString()).NumberFormat = "#,##0.00 €";
summarySheet.Range("$D3:$D" + (report.Products.Length + 3).ToString()).NumberFormat = "0\"%\"";
summarySheet.Cells(3 + report.Products.Length, 1).Value = "Total:";
summarySheet.Range("D2").HorizontalAlignment = XlHAlign.xlHAlignCenter;
summarySheet.Range("$B2:$E2").Font.Bold = true;
summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).Font.Bold = true;
summarySheet.Range(leftBottomCellAdress + ":" + rightBottomCellAdress).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
/*do background color for cells*/
string listSeperator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;
// draw the face
string rangeAdressFace = string.Format("$C10:$M10{0}$C30:$M30{0}$C11:$C30{0}$M11:$M30", listSeperator);
workSheet.Range(rangeAdressFace).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator);
workSheet.Range(rangeAdressEyes).Interior.Color = XlConverter.ToDouble(Color.Black);
string rangeAdressNoise = string.Format("$G18:$I19", listSeperator);
workSheet.Range(rangeAdressNoise).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator);
workSheet.Range(rangeAdressMouth).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
/*do borderlines for cells*/
// border the face with the border arround method
workSheet.Range(rangeAdressFace).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
workSheet.Range(rangeAdressEyes).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
workSheet.Range(rangeAdressNoise).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDouble, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
// border explicitly
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble;
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Weight = 4;
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Color = 400;
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example1{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
static void Main(string[] args)
{
Console.WriteLine("XlLateBinding Performance Test - 5000 Cells.");
/*
* start excel and disable messageboxes and screen updating
*/
XlApplication excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
excelApplication.ScreenUpdating = false;
/*
* create new empty worksheet
*/
excelApplication.Workbooks.Add();
XlWorksheet sheet = excelApplication.Workbooks[1].Worksheets[1];
/*
* do the test
*/
DateTime timeStart = DateTime.Now;
for (int i = 1; i <= 5000; i++)
{
// cells property for a sheet in OfficeFor.Net are not supported
// the reason for all examples use range
string rangeAdress = "$A" + i.ToString();
XlRange cellRange = sheet.Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black);
}
DateTime timeEnd = DateTime.Now;
TimeSpan timeElapsed = timeEnd - timeStart;
/*
* display for user
*/
string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);
Console.WriteLine(outputConsole);
Console.Read();
/*
* write result in logfile
*/
string logFile = Path.Combine(Environment.CurrentDirectory, "LateBinding.log");
string logFileAppend = timeElapsed.ToString() + Environment.NewLine;
File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);
excelApplication.Quit();
excelApplication.Dispose();
}
static void Main(string[] args)
{
Console.WriteLine("XlLateBinding Performance Test - 10.000 Cells.");
/*
* start excel and disable messageboxes and screen updating
*/
XlApplication excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
excelApplication.ScreenUpdating = false;
/*
* create new empty worksheet
*/
excelApplication.Workbooks.Add();
XlWorksheet sheet = excelApplication.Workbooks[1].Worksheets[1];
/*
* do the test
*/
DateTime timeStart = DateTime.Now;
for (int i = 1; i <= 10000; i++)
{
string rangeAdress = "$A" + i.ToString();
XlRange cellRange = sheet.Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = false;
XlComment sampleComment = cellRange.AddComment("Sample Comment");
}
DateTime timeEnd = DateTime.Now;
TimeSpan timeElapsed = timeEnd - timeStart;
/*
* display for user
*/
string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);
Console.WriteLine(outputConsole);
Console.Read();
/*
* write result in logfile
*/
string logFile = Path.Combine(Environment.CurrentDirectory, "LateBinding.log");
string logFileAppend = timeElapsed.ToString() + Environment.NewLine;
File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);
excelApplication.Quit();
excelApplication.Dispose();
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new XlApplication();
_excelApplication.DisplayAlerts = false;
_excelApplication.ScreenUpdating = false;
// add a new workbook
XlWorkbook workBook = _excelApplication.Workbooks.Add();
// we use the first sheet as summary sheet and remove the 2 last sheets
XlWorksheet summarySheet = workBook.Worksheets[1];
workBook.Worksheets[3].Delete();
workBook.Worksheets[2].Delete();
// we get the data & perform the report
_report = new SalesReport(_yearToReport, _monthToReport);
_report.Proceed();
// we create named styles for the range.Style property
CreateStorageAndRankingStyle(workBook, "StorageAndRanking");
CreateMonthStyle(workBook, "MonthInfos");
CreateMonthStyle(workBook, "YearTotalInfos");
// write product sheets
XlWorksheet productSheet = null;
foreach (SalesReportProduct itemProduct in _report.Products)
{
productSheet = workBook.Worksheets.Add();
ProceedProductWorksheet(productSheet, itemProduct);
productSheet.Move(null, workBook.Worksheets[workBook.Worksheets.Count]);
}
// write summary sheet
ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet);
summarySheet.Range("$A2").Select();
// save the book
string fileExtension = XlConverter.GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example10{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
internal static List <string> Headers(XlWorksheet worksheet)
{
List <string> headers = new List <string>();
IXlRowData row = worksheet.Rows[0];
foreach (IXlCell o in row)
{
headers.Add(o.CellValue);
}
return(headers);
}
private void ProceedProductMonthInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 9;
int iMonthCellIndex = 1;
productSheet.Range("$A9:$M13").StyleAsString = "MonthInfos";
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = "ManufacturerPriceSummary";
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = "SalesPricesSummary";
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = "TotalRevenue";
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = "CountOfSales";
iMonthCellIndex = 2;;
foreach (SalesReportReportEntity itemMonth in itemProduct.PrevMonths)
{
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(iMonthCellIndex - 2);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemMonth.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemMonth.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemMonth.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemMonth.CountOfSales;
iMonthCellIndex++;
}
string cellAdress1 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10);
string cellAdress2 = XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12);
productSheet.Range("$B10:$" + cellAdress1).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$B12:$" + cellAdress2).Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(_monthToReport - 1);
productSheet.Cells(rowIndex + 1, iMonthCellIndex).Value = itemProduct.Month.ManufactorPriceSummary;
productSheet.Cells(rowIndex + 2, iMonthCellIndex).Value = itemProduct.Month.SalesPricesSummary;
productSheet.Cells(rowIndex + 3, iMonthCellIndex).Value = itemProduct.Month.OutcomeSummary;
productSheet.Cells(rowIndex + 4, iMonthCellIndex).Value = itemProduct.Month.CountOfSales;
for (int i = itemProduct.PrevMonths.Count + 2; i <= 12; i++)
{
iMonthCellIndex++;
productSheet.Cells(rowIndex, iMonthCellIndex).Value = GetMonthName(i - 1);
}
productSheet.Range("$B9:$M9").NumberFormat = "";
productSheet.Range("$B9:$M9").Font.Bold = true;
productSheet.Range("$B13:$M13").NumberFormat = "";
productSheet.Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (itemProduct.PrevMonths.Count < 11)
{
string topLeftMergeCellAdress = "$" + XlConverter.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10);
productSheet.Range(topLeftMergeCellAdress + ":$M13").MergeCells = true;
}
}
private void AddRows(XlWorkbook book, DataTab data)
{
XlWorksheets sheets = book.Worksheets;
List <BaseDTO> list = new List <BaseDTO>(data);
XlWorksheet sheet = sheets.AddWorksheet(data.Name, data.Color, data.Schema);
int numberRows = list.Count;
for (int rowIndex = 0; rowIndex < numberRows; rowIndex++)
{
Dictionary <string, string> values = list[rowIndex].Values();
sheet.AddRow(rowIndex, values);
}
}
private void ProceedProductWorksheetHeader(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 1;
int columnIndex = 1;
productSheet.PageSetup.LeftHeader = "&D created";
productSheet.PageSetup.CenterHeader = "Vintage Digital Inc.";
productSheet.PageSetup.RightHeader = string.Format("Monthly Sales Report {1:00}/{0}", _yearToReport, _monthToReport);
productSheet.Cells(rowIndex, columnIndex).Value = itemProduct.ProductName;
productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
productSheet.Cells(rowIndex, columnIndex).Font.Underline = true;
productSheet.Cells(rowIndex, columnIndex).Font.Size = 14;
productSheet.Cells(rowIndex, columnIndex).Font.Name = "Verdana";
}
private static void GenerateBackgroundColor(XlWorksheet ws, DataTable t)
{
for (int ri = 0; ri < t.Rows.Count; ri++)
{
DataRow dr = t.Rows[ri];
String bgColor = dr["bg_color"] as String;
if (!String.IsNullOrWhiteSpace(bgColor))
{
int ci = 0;
foreach (Md.TableColumn col in Md.Table1Columns)
{
ws.SetCellBackgroundColor((ri + 1), ci, bgColor); // !!! 1 - количество строк в шапке
ci++;
}
}
}
}
private void LoadRows(XlWorksheet worksheet, LoadRow loadRow)
{
for (int index = 1; index < worksheet.Rows.Count; index++)
{
var values = worksheet.ValueRow(index);
if (values.Count > 0)
{
// Skip empty rows
foreach (String cur in values.Values)
{
if (!string.IsNullOrEmpty(cur))
{
loadRow(worksheet.Name, values); //we got at least one non-empty cell
break;
}
}
}
}
}
private void ProceedProductWorksheet(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
string sheetName = string.Format("{0}-{1}", itemProduct.ProductName, itemProduct.ProductId.ToString());
productSheet.Name = sheetName;
// its not a random chain, write data first and create charts second
ProceedProductStorageInfo(productSheet, itemProduct);
ProceedProductMonthInfo(productSheet, itemProduct);
ProceedProductYearTotalInfo(productSheet, itemProduct);
ProceedProductMonthCharts(productSheet, itemProduct);
ProceedProductPrintSettings(productSheet);
productSheet.Columns.AutoFit(); // proceed AutoFit before header & ranking
ProceedProductWorksheetHeader(productSheet, itemProduct);
ProceedProductRankingInfo(productSheet, itemProduct);
productSheet.Columns[14].ColumnWidth = 2.14;
productSheet.Columns[17].ColumnWidth = 5.14;
}
private static void GenerateColumns(XlWorksheet ws, Md.TableColumn[] cols)
{
uint cn = 1;
for (int ci = 0; ci < cols.Length; ci++)
{
Md.TableColumn col = cols[ci];
if (col.Width == null)
{
ws.AppendColumn(cn, cn, true);
}
else
{
Double.TryParse(col.Width, out Double width);
ws.AppendColumn(cn, cn, false, true, width);
}
cn++;
}
}
private void ProceedProductRankingInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 4;
productSheet.Range("$D3:$F6").StyleAsString = "StorageAndRanking";
productSheet.Cells(rowIndex, columnIndex).Value = "Count Ranking";
productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
productSheet.Cells(rowIndex + 1, columnIndex).Value = "Month";
productSheet.Cells(rowIndex + 2, columnIndex).Value = "Year";
productSheet.Cells(rowIndex + 3, columnIndex).Value = "Total";
productSheet.Cells(rowIndex + 1, columnIndex + 2).Value = itemProduct.SalesRankMonth;
productSheet.Cells(rowIndex + 2, columnIndex + 2).Value = itemProduct.SalesRankYear;
productSheet.Cells(rowIndex + 3, columnIndex + 2).Value = itemProduct.SalesRankTotal;
productSheet.Range("$D4:$E6").Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
}
private void ProceedProductYearTotalInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int ColumnIndex = 15;
int RowIndex = 9;
productSheet.Range("$O9:$R13").StyleAsString = "YearTotalInfos";
productSheet.Cells(RowIndex, ColumnIndex).Value = "Year " + _yearToReport.ToString();
productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Year.ManufactorPriceSummary;
productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Year.SalesPricesSummary;
productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Year.OutcomeSummary;
productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Year.CountOfSales;
productSheet.Cells(RowIndex, ColumnIndex + 1).Value = "Year " + (_yearToReport - 1).ToString();
productSheet.Cells(RowIndex + 1, ColumnIndex + 1).Value = itemProduct.PrevYear.ManufactorPriceSummary;
productSheet.Cells(RowIndex + 2, ColumnIndex + 1).Value = itemProduct.PrevYear.SalesPricesSummary;
productSheet.Cells(RowIndex + 3, ColumnIndex + 1).Value = itemProduct.PrevYear.OutcomeSummary;
productSheet.Cells(RowIndex + 4, ColumnIndex + 1).Value = itemProduct.PrevYear.CountOfSales;
productSheet.Range("$O10:$P10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$O12:$P12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
ColumnIndex = 18;
RowIndex = 9;
productSheet.Cells(RowIndex, ColumnIndex).Value = "Total";
productSheet.Cells(RowIndex + 1, ColumnIndex).Value = itemProduct.Total.ManufactorPriceSummary;
productSheet.Cells(RowIndex + 2, ColumnIndex).Value = itemProduct.Total.SalesPricesSummary;
productSheet.Cells(RowIndex + 3, ColumnIndex).Value = itemProduct.Total.OutcomeSummary;
productSheet.Cells(RowIndex + 4, ColumnIndex).Value = itemProduct.Total.CountOfSales;
productSheet.Range("$R10").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$R12").Interior.Color = XlConverter.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Range("$O9:$R9").NumberFormat = "";
productSheet.Range("$O9:$R9").Font.Bold = true;
productSheet.Range("$O13:$R13").NumberFormat = "";
productSheet.Range("$O13:$R13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
productSheet.Range("$Q9:$Q13").MergeCells = true;
}
private void ProceedProductStorageInfo(XlWorksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 1;
productSheet.Range("$A3:$B6").StyleAsString = "StorageAndRanking";
productSheet.Cells(rowIndex, columnIndex).Value = "Storage Info";
productSheet.Cells(rowIndex, columnIndex).Font.Bold = true;
productSheet.Cells(rowIndex + 1, columnIndex).Value = "Storage Count";
productSheet.Cells(rowIndex + 2, columnIndex).Value = "Sales in Progress";
productSheet.Cells(rowIndex + 3, columnIndex).Value = "Recalc Storage Count ";
productSheet.Cells(rowIndex + 1, columnIndex + 1).Value = itemProduct.StorageCount;
productSheet.Cells(rowIndex + 2, columnIndex + 1).Value = itemProduct.OpenOrders.CountOfSales;
productSheet.Cells(rowIndex + 3, columnIndex + 1).Value = itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales;
SetProductStorageCountColor(itemProduct.StorageCount, productSheet.Cells(rowIndex + 1, columnIndex + 1));
SetProductStorageCountColor(itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales, productSheet.Cells(rowIndex + 3, columnIndex + 1));
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
workSheet.Cells(1, 1).Value = "these sample shapes was dynamicly created by code.";
// create a star
XlShape starShape = workSheet.Shapes.AddShape(LateBindingApi.Excel.Enums.MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
XlShape textBox = workSheet.Shapes.AddTextbox(LateBindingApi.Excel.Enums.MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
XlShape textEffect = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12, LateBindingApi.Excel.Enums.MsoTriState.msoTrue, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 250);
// create text effect
XlShape textDiagram = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 350);
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example4{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private static void PrintWorksheet(XlWorksheet worksheet)
{
int rowIndex = 1;
Color color = Color.FromArgb(worksheet.TabColor.ArgbColor);
Console.WriteLine("Worksheet Name: {0}, Color: {1}, Position: {2}", worksheet.Name, color, worksheet.Position);
foreach (XlColumn cur in worksheet.Columns)
{
Console.Write(cur.Name + ", ");
}
Console.WriteLine();
foreach (IXlRowData row in worksheet.Rows)
{
int columnIndex = 1;
PrintRow(worksheet.ValueRow(rowIndex - 1));
//foreach (IXlCell cell in row)
//{
// Console.WriteLine("Row: {3}, Col: {4}, Cell Value: {0}, Type: {1}, Style: {2}", cell.CellValue, cell.CellType, cell.CellStyle, rowIndex, columnIndex++);
//}
rowIndex++;
}
}
private void CheckHeaders(XlWorksheet worksheet)
{
if (worksheet != null)
{
Schema s = null;
List <string> h;
//if (worksheet.Name == "DET_Deployments")
//{
// s = DeploymentDTO.GetSchema();
//}
//else
// return;
//h = new List<string>();
//foreach (SchemaEntry c in s)
//{
// h.Add(c.ColumnName);
//}
//if (!ExcelBaseDet.HasHeaders(ExcelBaseDet.Headers(worksheet), h))
//{
// generalDet.ValidationIssues.Add(ValidationIssue.Code.MissingFieldHeader, "Missing column header in " + worksheet.Name);
//}
}
}