public PictureToExcel()
{
InitializeWorkbook();
HSSFSheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");
TechStoreDocumentsDT = new DataTable();
using (SqlDataAdapter DA = new SqlDataAdapter(@"SELECT TOP (2000) TechStoreName, TechStoreDocumentID FROM TechStore
LEFT JOIN TechStoreDocuments ON TechStore.TechStoreID = TechStoreDocuments.TechID AND DocType = 0
WHERE TechStoreSubGroupID = 30 ORDER BY TechStoreName", ConnectionStrings.CatalogConnectionString))
{
using (DataTable DT = new DataTable())
{
DA.Fill(TechStoreDocumentsDT);
}
}
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
HSSFCell Cell1;
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
int RowIndex = 0;
for (int i = 0; i < TechStoreDocumentsDT.Rows.Count; i++)
{
string TechStoreName = TechStoreDocumentsDT.Rows[i]["TechStoreName"].ToString();
Cell1 = sheet1.CreateRow(RowIndex).CreateCell(0);
Cell1.SetCellValue(TechStoreName);
Cell1.CellStyle = ReportCS1;
if (TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"] != DBNull.Value)
{
int TechStoreDocumentID = Convert.ToInt32(TechStoreDocumentsDT.Rows[i]["TechStoreDocumentID"]);
anchor = new HSSFClientAnchor(0, 0, 0, 255, 2, RowIndex, 5, RowIndex + 7)
{
AnchorType = 2
};
HSSFPicture picture = patriarch.CreatePicture(anchor, GetTechStoreImage(TechStoreDocumentID, hssfworkbook));
//picture.Resize();
picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
}
RowIndex = RowIndex + 8;
}
WriteToFile();
}
private void BalancesMarketingReport(bool bZOV)
{
int pos = 0;
string SheetName = "Маркетинг";
string ConnectionString = ConnectionStrings.MarketingOrdersConnectionString;
#region Create fonts and styles
HSSFFont HeaderF = hssfworkbook.CreateFont();
HeaderF.FontHeightInPoints = 9;
HeaderF.Boldweight = 9 * 256;
HeaderF.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountCS1 = hssfworkbook.CreateCellStyle();
CountCS1.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
CountCS1.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS1.BottomBorderColor = HSSFColor.BLACK.index;
CountCS1.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS1.LeftBorderColor = HSSFColor.BLACK.index;
CountCS1.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS1.RightBorderColor = HSSFColor.BLACK.index;
CountCS1.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS1.TopBorderColor = HSSFColor.BLACK.index;
CountCS1.SetFont(SimpleF);
HSSFCellStyle CountCS2 = hssfworkbook.CreateCellStyle();
CountCS2.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.0000");
CountCS2.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS2.BottomBorderColor = HSSFColor.BLACK.index;
CountCS2.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS2.LeftBorderColor = HSSFColor.BLACK.index;
CountCS2.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS2.RightBorderColor = HSSFColor.BLACK.index;
CountCS2.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS2.TopBorderColor = HSSFColor.BLACK.index;
CountCS2.SetFont(SimpleF);
HSSFCellStyle CountCS3 = hssfworkbook.CreateCellStyle();
CountCS3.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
CountCS3.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS3.BottomBorderColor = HSSFColor.BLACK.index;
CountCS3.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS3.LeftBorderColor = HSSFColor.BLACK.index;
CountCS3.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS3.RightBorderColor = HSSFColor.BLACK.index;
CountCS3.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS3.TopBorderColor = HSSFColor.BLACK.index;
CountCS3.SetFont(SimpleF);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.SetFont(HeaderF);
#endregion Create fonts and styles
HSSFSheet sheet1 = hssfworkbook.CreateSheet(SheetName);
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
sheet1.SetColumnWidth(0, 20 * 256);
sheet1.SetColumnWidth(1, 30 * 256);
sheet1.SetColumnWidth(2, 20 * 256);
sheet1.SetColumnWidth(3, 20 * 256);
HSSFCell Cell1;
string SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(dbo.PackageDetails.Count) AS Expr2
FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length = - 1 AND dbo.DecorOrders.Height = - 1 AND
dbo.DecorOrders.Width = - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 2;
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina, dbo.DecorOrders.Length,
SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND
dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND dbo.DecorOrders.Width = - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName, dbo.DecorOrders.Length
ORDER BY ProductName, Decor, Color, Patina, dbo.DecorOrders.Length";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS3;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 2;
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(CAST(dbo.DecorOrders.Length * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(10,2))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND
dbo.DecorOrders.Width <> - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorConfig ON dbo.DecorOrders.DecorConfigID = infiniu2_catalog.dbo.DecorConfig.DecorConfigID AND infiniu2_catalog.dbo.DecorConfig.MeasureID = 2 INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
foreach (DataRow item in BalancesDT.Rows)
{
item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000;
}
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("м.п.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"]));
Cell1.CellStyle = CountCS1;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 2;
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(CAST(dbo.DecorOrders.Length * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(12,4))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND dbo.DecorOrders.Length <> - 1 AND dbo.DecorOrders.Height = - 1 AND
dbo.DecorOrders.Width <> - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorConfig ON dbo.DecorOrders.DecorConfigID = infiniu2_catalog.dbo.DecorConfig.DecorConfigID AND infiniu2_catalog.dbo.DecorConfig.MeasureID = 1 INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
foreach (DataRow item in BalancesDT.Rows)
{
item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000000;
}
}
if (BalancesDT.Rows.Count > 0)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("м.кв.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"]));
Cell1.CellStyle = CountCS2;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
}
SelectCommand = @"SELECT infiniu2_catalog.dbo.DecorProducts.ProductName AS ProductName, Decor.TechStoreName AS Decor, Colors.TechStoreName AS Color, Patina.PatinaName AS Patina,
SUM(CAST(dbo.DecorOrders.Height * dbo.DecorOrders.Width * dbo.PackageDetails.Count AS decimal(12,4))) AS Expr1, SUM(dbo.PackageDetails.Count) AS Expr2 FROM dbo.PackageDetails INNER JOIN
dbo.DecorOrders ON dbo.PackageDetails.OrderID = dbo.DecorOrders.DecorOrderID AND dbo.DecorOrders.FactoryID = 2 AND
dbo.DecorOrders.Length = - 1 AND dbo.DecorOrders.Height <> - 1 AND dbo.DecorOrders.Width <> - 1 INNER JOIN
dbo.Packages ON dbo.PackageDetails.PackageID = dbo.Packages.PackageID AND dbo.Packages.ProductType = 1 AND dbo.Packages.PackageStatusID IN (1, 2, 4) INNER JOIN
infiniu2_catalog.dbo.DecorProducts ON dbo.DecorOrders.ProductID = infiniu2_catalog.dbo.DecorProducts.ProductID INNER JOIN
infiniu2_catalog.dbo.TechStore AS Decor ON dbo.DecorOrders.DecorID = Decor.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.TechStore AS Colors ON dbo.DecorOrders.ColorID = Colors.TechStoreID LEFT OUTER JOIN
infiniu2_catalog.dbo.Patina AS Patina ON dbo.DecorOrders.PatinaID = Patina.PatinaID
GROUP BY infiniu2_catalog.dbo.DecorProducts.ProductName, Decor.TechStoreName, Colors.TechStoreName, Patina.PatinaName
ORDER BY ProductName, Decor, Color, Patina";
using (SqlDataAdapter DA = new SqlDataAdapter(SelectCommand, ConnectionString))
{
BalancesDT.Dispose();
BalancesDT = new DataTable();
DA.Fill(BalancesDT);
foreach (DataRow item in BalancesDT.Rows)
{
item["Expr1"] = Convert.ToDecimal(item["Expr1"]) / 1000000;
}
}
if (BalancesDT.Rows.Count > 0)
{
for (int i = 0; i < BalancesDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(BalancesDT.Rows[i]["ProductName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(BalancesDT.Rows[i]["Decor"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(BalancesDT.Rows[i]["Color"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(BalancesDT.Rows[i]["Patina"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToDouble(BalancesDT.Rows[i]["Expr1"]));
Cell1.CellStyle = CountCS2;
Cell1 = sheet1.CreateRow(pos).CreateCell(5);
Cell1.SetCellValue(Convert.ToInt32(BalancesDT.Rows[i]["Expr2"]));
Cell1.CellStyle = CountCS3;
pos++;
}
pos += 1;
}
}
public void Report(string FileName)
{
int pos = 0;
//Export to excel
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
#region Create fonts and styles
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFFont HeaderF2 = hssfworkbook.CreateFont();
HeaderF2.FontHeightInPoints = 10;
HeaderF2.Boldweight = 10 * 256;
HeaderF2.FontName = "Calibri";
HSSFFont HeaderF3 = hssfworkbook.CreateFont();
HeaderF3.FontHeightInPoints = 9;
HeaderF3.Boldweight = 9 * 256;
HeaderF3.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle();
CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS.BottomBorderColor = HSSFColor.BLACK.index;
CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS.LeftBorderColor = HSSFColor.BLACK.index;
CountCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS.RightBorderColor = HSSFColor.BLACK.index;
CountCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS.TopBorderColor = HSSFColor.BLACK.index;
CountCS.SetFont(SimpleF);
HSSFCellStyle WeightCS = hssfworkbook.CreateCellStyle();
WeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
WeightCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
WeightCS.BottomBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
WeightCS.LeftBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderRight = HSSFCellStyle.BORDER_THIN;
WeightCS.RightBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderTop = HSSFCellStyle.BORDER_THIN;
WeightCS.TopBorderColor = HSSFColor.BLACK.index;
WeightCS.SetFont(SimpleF);
HSSFCellStyle PriceBelCS = hssfworkbook.CreateCellStyle();
PriceBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
PriceBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
PriceBelCS.BottomBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
PriceBelCS.LeftBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderRight = HSSFCellStyle.BORDER_THIN;
PriceBelCS.RightBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderTop = HSSFCellStyle.BORDER_THIN;
PriceBelCS.TopBorderColor = HSSFColor.BLACK.index;
PriceBelCS.SetFont(SimpleF);
HSSFCellStyle PriceForeignCS = hssfworkbook.CreateCellStyle();
PriceForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
PriceForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.BottomBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.LeftBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.RightBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.TopBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.SetFont(SimpleF);
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
HSSFCellStyle ReportCS2 = hssfworkbook.CreateCellStyle();
ReportCS2.SetFont(HeaderF1);
HSSFCellStyle SummaryWithoutBorderBelCS = hssfworkbook.CreateCellStyle();
SummaryWithoutBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
SummaryWithoutBorderBelCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithoutBorderForeignCS = hssfworkbook.CreateCellStyle();
SummaryWithoutBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWithoutBorderForeignCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWeightCS = hssfworkbook.CreateCellStyle();
SummaryWeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWeightCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithBorderBelCS = hssfworkbook.CreateCellStyle();
SummaryWithBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
SummaryWithBorderBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.BottomBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.LeftBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.RightBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.TopBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.WrapText = true;
SummaryWithBorderBelCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithBorderForeignCS = hssfworkbook.CreateCellStyle();
SummaryWithBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWithBorderForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.BottomBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.LeftBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.RightBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.TopBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.WrapText = true;
SummaryWithBorderForeignCS.SetFont(HeaderF2);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
//SimpleHeaderCS.WrapText = true;
SimpleHeaderCS.SetFont(HeaderF3);
#endregion Create fonts and styles
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Маркетинг");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
int DisplayIndex = 0;
sheet1.SetColumnWidth(DisplayIndex++, 40 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 15 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 14 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 14 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 14 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 9 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 13 * 256);
sheet1.SetColumnWidth(DisplayIndex++, 13 * 256);
HSSFCell Cell1;
DisplayIndex = 0;
if (AdditionalCostDT.Rows.Count > 0)
{
pos += 2;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Менеджер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Дата согласования");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Сумма дополнительно");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Сумма заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Валюта");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Курс");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Расчетный курс");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Итого сумма дополнительно");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(DisplayIndex++);
Cell1.SetCellValue("Итого сумма заказа");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
int ColumnCount = AdditionalCostDT.Columns.Count;
for (int x = 0; x < AdditionalCostDT.Rows.Count; x++)
{
for (int y = 0; y < ColumnCount; y++)
{
Type t = AdditionalCostDT.Rows[x][y].GetType();
if (t.Name == "Decimal")
{
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(Convert.ToDouble(AdditionalCostDT.Rows[x][y]));
cell.CellStyle = CountCS;
continue;
}
if (t.Name == "Int32")
{
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(Convert.ToInt32(AdditionalCostDT.Rows[x][y]));
cell.CellStyle = SimpleCS;
continue;
}
if (t.Name == "Boolean")
{
bool b = Convert.ToBoolean(AdditionalCostDT.Rows[x][y]);
string str = "Да";
if (!b)
{
str = "Нет";
}
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(str);
cell.CellStyle = SimpleCS;
continue;
}
if (t.Name == "DateTime")
{
string dateTime = Convert.ToDateTime(AdditionalCostDT.Rows[x][y]).ToShortDateString();
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(dateTime);
cell.CellStyle = SimpleCS;
continue;
}
if (t.Name == "String" || t.Name == "DBNull")
{
HSSFCell cell = sheet1.CreateRow(pos).CreateCell(y);
cell.SetCellValue(AdditionalCostDT.Rows[x][y].ToString());
cell.CellStyle = SimpleCS;
continue;
}
}
pos++;
}
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
}
}
public void WeightReport(DateTime date1, DateTime date2, string FileName)
{
ClearReport();
MarketingWeightDispatch(date1, date2);
ZOVWeightDispatch(date1, date2);
CollectMarketingWeight();
CollectZOVWeight();
string MainOrdersList = string.Empty;
int pos = 0;
//Export to excel
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
#region Create fonts and styles
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFFont HeaderF2 = hssfworkbook.CreateFont();
HeaderF2.FontHeightInPoints = 10;
HeaderF2.Boldweight = 10 * 256;
HeaderF2.FontName = "Calibri";
HSSFFont HeaderF3 = hssfworkbook.CreateFont();
HeaderF3.FontHeightInPoints = 9;
HeaderF3.Boldweight = 9 * 256;
HeaderF3.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle();
CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS.BottomBorderColor = HSSFColor.BLACK.index;
CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS.LeftBorderColor = HSSFColor.BLACK.index;
CountCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS.RightBorderColor = HSSFColor.BLACK.index;
CountCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS.TopBorderColor = HSSFColor.BLACK.index;
CountCS.SetFont(SimpleF);
HSSFCellStyle WeightCS = hssfworkbook.CreateCellStyle();
WeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
WeightCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
WeightCS.BottomBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
WeightCS.LeftBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderRight = HSSFCellStyle.BORDER_THIN;
WeightCS.RightBorderColor = HSSFColor.BLACK.index;
WeightCS.BorderTop = HSSFCellStyle.BORDER_THIN;
WeightCS.TopBorderColor = HSSFColor.BLACK.index;
WeightCS.SetFont(SimpleF);
HSSFCellStyle PriceBelCS = hssfworkbook.CreateCellStyle();
PriceBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
PriceBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
PriceBelCS.BottomBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
PriceBelCS.LeftBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderRight = HSSFCellStyle.BORDER_THIN;
PriceBelCS.RightBorderColor = HSSFColor.BLACK.index;
PriceBelCS.BorderTop = HSSFCellStyle.BORDER_THIN;
PriceBelCS.TopBorderColor = HSSFColor.BLACK.index;
PriceBelCS.SetFont(SimpleF);
HSSFCellStyle PriceForeignCS = hssfworkbook.CreateCellStyle();
PriceForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
PriceForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.BottomBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.LeftBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.RightBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN;
PriceForeignCS.TopBorderColor = HSSFColor.BLACK.index;
PriceForeignCS.SetFont(SimpleF);
HSSFCellStyle ReportCS1 = hssfworkbook.CreateCellStyle();
ReportCS1.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
ReportCS1.BottomBorderColor = HSSFColor.BLACK.index;
ReportCS1.SetFont(HeaderF1);
HSSFCellStyle ReportCS2 = hssfworkbook.CreateCellStyle();
ReportCS2.SetFont(HeaderF1);
HSSFCellStyle SummaryWithoutBorderBelCS = hssfworkbook.CreateCellStyle();
SummaryWithoutBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
SummaryWithoutBorderBelCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithoutBorderForeignCS = hssfworkbook.CreateCellStyle();
SummaryWithoutBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWithoutBorderForeignCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWeightCS = hssfworkbook.CreateCellStyle();
SummaryWeightCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWeightCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithBorderBelCS = hssfworkbook.CreateCellStyle();
SummaryWithBorderBelCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
SummaryWithBorderBelCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.BottomBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.LeftBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.RightBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderBelCS.TopBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderBelCS.WrapText = true;
SummaryWithBorderBelCS.SetFont(HeaderF2);
HSSFCellStyle SummaryWithBorderForeignCS = hssfworkbook.CreateCellStyle();
SummaryWithBorderForeignCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.00");
SummaryWithBorderForeignCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.BottomBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.LeftBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.RightBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SummaryWithBorderForeignCS.TopBorderColor = HSSFColor.BLACK.index;
SummaryWithBorderForeignCS.WrapText = true;
SummaryWithBorderForeignCS.SetFont(HeaderF2);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
//SimpleHeaderCS.WrapText = true;
SimpleHeaderCS.SetFont(HeaderF3);
#endregion Create fonts and styles
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Маркетинг");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
sheet1.SetColumnWidth(0, 71 * 256);
sheet1.SetColumnWidth(1, 13 * 256);
sheet1.SetColumnWidth(2, 13 * 256);
sheet1.SetColumnWidth(3, 14 * 256);
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ЗОВ");
sheet2.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet2.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet2.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet2.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet2.SetMargin(HSSFSheet.BottomMargin, (double).20);
sheet2.SetColumnWidth(0, 13 * 256);
sheet2.SetColumnWidth(1, 50 * 256);
sheet2.SetColumnWidth(2, 13 * 256);
sheet2.SetColumnWidth(3, 14 * 256);
sheet2.SetColumnWidth(4, 14 * 256);
HSSFCell Cell1;
if (AllMarketingWeightDT.Rows.Count > 0)
{
//Профиль
pos += 2;
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue("Фасады, кг");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue("Декор, кг");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue("Вес общий, т");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < AllMarketingWeightDT.Rows.Count; i++)
{
Cell1 = sheet1.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(AllMarketingWeightDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(Convert.ToDouble(AllMarketingWeightDT.Rows[i]["FrontsWeight"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(Convert.ToDouble(AllMarketingWeightDT.Rows[i]["DecorWeight"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(Convert.ToDouble(AllMarketingWeightDT.Rows[i]["TotalWeight"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (AllZOVWeightDT.Rows.Count > 0)
{
//Профиль
pos += 2;
Cell1 = sheet2.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue("Дата");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue("Фасады, кг");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue("Декор, кг");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue("Вес общий, т");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < AllZOVWeightDT.Rows.Count; i++)
{
Cell1 = sheet2.CreateRow(pos).CreateCell(0);
Cell1.SetCellValue(AllZOVWeightDT.Rows[i]["date"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(1);
Cell1.SetCellValue(AllZOVWeightDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(2);
Cell1.SetCellValue(Convert.ToDouble(AllZOVWeightDT.Rows[i]["FrontsWeight"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(3);
Cell1.SetCellValue(Convert.ToDouble(AllZOVWeightDT.Rows[i]["DecorWeight"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet2.CreateRow(pos).CreateCell(4);
Cell1.SetCellValue(Convert.ToDouble(AllZOVWeightDT.Rows[i]["TotalWeight"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos++;
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
ClearReport();
}
public void Report(string FileName)
{
DivideByFactory();
int pos = 0;
//Export to excel
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
#region Create fonts and styles
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFFont HeaderF2 = hssfworkbook.CreateFont();
HeaderF2.FontHeightInPoints = 10;
HeaderF2.Boldweight = 10 * 256;
HeaderF2.FontName = "Calibri";
HSSFFont HeaderF3 = hssfworkbook.CreateFont();
HeaderF3.FontHeightInPoints = 9;
HeaderF3.Boldweight = 9 * 256;
HeaderF3.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountDecCS = hssfworkbook.CreateCellStyle();
CountDecCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.000");
CountDecCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountDecCS.BottomBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountDecCS.LeftBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountDecCS.RightBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountDecCS.TopBorderColor = HSSFColor.BLACK.index;
CountDecCS.SetFont(SimpleF);
HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle();
CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS.BottomBorderColor = HSSFColor.BLACK.index;
CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS.LeftBorderColor = HSSFColor.BLACK.index;
CountCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS.RightBorderColor = HSSFColor.BLACK.index;
CountCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS.TopBorderColor = HSSFColor.BLACK.index;
CountCS.SetFont(SimpleF);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
//SimpleHeaderCS.WrapText = true;
SimpleHeaderCS.SetFont(HeaderF3);
#endregion Create fonts and styles
HSSFCell Cell1;
if (FrontsProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsProfilDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (FrontsTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsTPSDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["PackingDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
ClearReport();
}