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