public static Workbook ToExcel <T>(this IEnumerable <T> src) where T : new() { var myWb = new Workbook(); myWb.AddStyles(); var myWs = myWb.Worksheets.Add("one"); var headerRow = myWs.Table.Rows.Add(); var cols = MereUtils.CacheCheck <T>().SelectMereColumns.ToList(); foreach (var field in cols) { var myCol = new WorksheetColumn(150); myWs.Table.Columns.Add(myCol); headerRow.Cells.Add(field.DisplayName, DataType.String, "HeaderStyle"); } foreach (var rec in src) { var newRow = myWs.Table.Rows.Add(); newRow.AutoFitHeight = false; foreach (var field in cols) { var v = field.GetBase(rec); var val = v == null ? "" : v.ToString(); newRow.Cells.Add(val, DataType.String, (myWs.Table.Rows.IndexOf(newRow) % 2) == 0 ? "s31" : "s31Odd"); } } return(myWb); }
private void GenerateShopListWorksheet(WorksheetCollection sheets) { Worksheet sheet = sheets.Add("ShopList"); sheet.Names.Add(new WorksheetNamedRange("Print_Area", "=Sheet1!R1C1:R24C6", false)); //TODO:Add RTL //sheet.Table.Options. sheet.Table.DefaultRowHeight = 14.25F; sheet.Table.DefaultColumnWidth = 54F; //sheet.Table.ExpandedColumnCount = 6; //sheet.Table.ExpandedRowCount = 24; sheet.Table.FullColumns = 1; sheet.Table.FullRows = 1; WorksheetColumn column0 = sheet.Table.Columns.Add(); column0.Index = 5; column0.Width = 177; GenerateHeaders(sheet); GenerateDataRows(sheet); // ----------------------------------------------- GenerateFooterRow(sheet); // ----------------------------------------------- // Options // ----------------------------------------------- sheet.Options.Selected = true; sheet.Options.ProtectObjects = false; sheet.Options.ProtectScenarios = false; sheet.Options.PageSetup.Header.Margin = 0.3F; sheet.Options.PageSetup.Footer.Margin = 0.3F; sheet.Options.PageSetup.PageMargins.Bottom = 0.75F; sheet.Options.PageSetup.PageMargins.Left = 0.7F; sheet.Options.PageSetup.PageMargins.Right = 0.7F; sheet.Options.PageSetup.PageMargins.Top = 0.75F; sheet.Options.Print.PaperSizeIndex = 9; sheet.Options.Print.HorizontalResolution = 1200; sheet.Options.Print.VerticalResolution = 1200; sheet.Options.Print.ValidPrinterInfo = true; }
private void GenerarEncabezadoDatosEnHoja(Worksheet sheet) { WorksheetColumn column0 = sheet.Table.Columns.Add(); column0.Width = 75; column0.StyleID = "s62"; WorksheetColumn column1 = sheet.Table.Columns.Add(); column1.Width = 75; column1.StyleID = "s62"; WorksheetColumn column2 = sheet.Table.Columns.Add(); column2.Width = 75; column2.StyleID = "s62"; WorksheetColumn column3 = sheet.Table.Columns.Add(); column3.Width = 75; column3.StyleID = "s62"; WorksheetColumn column4 = sheet.Table.Columns.Add(); column4.Width = 75; column4.StyleID = "s62"; WorksheetRow Row0 = sheet.Table.Rows.Add(); Row0.AutoFitHeight = false; WorksheetRow Row1 = sheet.Table.Rows.Add(); Row1.Height = 27; Row1.AutoFitHeight = false; Row1.Cells.Add("FARMACIA AGRICOLA >> TIERRA SANA >>", DataType.String, "s65"); WorksheetRow Row2 = sheet.Table.Rows.Add(); Row2.AutoFitHeight = true; Row2.Cells.Add("SUCURSAL", DataType.String, "s63"); WorksheetCell cell; cell = Row2.Cells.Add(); cell.Data.Type = DataType.String; cell.Data.Text = "SUCURSAL NUMERO N° 1"; string date = Convert.ToString(DateTime.Now); WorksheetRow Row3 = sheet.Table.Rows.Add(); Row3.AutoFitHeight = true; Row3.Cells.Add("FECHA", DataType.String, "s63"); cell = Row3.Cells.Add(); cell.Data.Type = DataType.String; cell.Data.Text = date.ToString(); WorksheetRow Row4 = sheet.Table.Rows.Add(); Row4.AutoFitHeight = true; Row4.Cells.Add("MONEDA", DataType.String, "s63"); cell = Row4.Cells.Add(); cell.Data.Type = DataType.String; cell.Data.Text = "SOLES"; WorksheetRow Row8 = sheet.Table.Rows.Add(); Row8.AutoFitHeight = true; Row8.Height = 20; Row8.Cells.Add("N°", DataType.String, "s84"); Row8.Cells.Add("CODIGO", DataType.String, "s84"); Row8.Cells.Add("NOMBRE PRODUCTO", DataType.String, "s84"); Row8.Cells.Add("PRECIO COMPRA", DataType.String, "s84"); Row8.Cells.Add("PRECIO PRODUCTO", DataType.String, "s84"); Row8.Cells.Add("STOCK", DataType.String, "s84"); Row8.Cells.Add("STOCK PROMEDIO", DataType.String, "s84"); Row8.Cells.Add("STOCK MINIMO", DataType.String, "s84"); Row8.Cells.Add("CATEGORIA", DataType.String, "s84"); Row8.Cells.Add("UNIDAD", DataType.String, "s84"); Row8.Cells.Add("ALMACEN", DataType.String, "s84"); //WorksheetRow Row10 = sheet.Table.Rows.Add(); //Row10.AutoFitHeight = true; //Row10.Cells.Add("MONEDA s87", DataType.String, "s87"); //cell = Row10.Cells.Add(); //cell.Data.Type = DataType.String; //cell.Data.Text = "SOLES s87"; //WorksheetRow Row11 = sheet.Table.Rows.Add(); //Row11.AutoFitHeight = true; //Row11.Cells.Add("MONEDA s88", DataType.String, "s88"); //cell = Row11.Cells.Add(); //cell.Data.Type = DataType.String; //cell.Data.Text = "SOLES s88"; //WorksheetRow Row12 = sheet.Table.Rows.Add(); //Row12.AutoFitHeight = true; //Row12.Cells.Add("MONEDA s89", DataType.String, "s89"); //cell = Row12.Cells.Add(); //cell.Data.Type = DataType.String; //cell.Data.Text = "2"; //WorksheetRow Row9 = sheet.Table.Rows.Add(); //Row9.AutoFitHeight = true; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; //cell = Row9.Cells.Add(); //cell.StyleID = "s85"; }
private string GenerateWorksheet(WorksheetCollection sheets, DateTime dttungay, DateTime dtdenngay, DataTable dtnv, ProgressBar progressBar) { Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); tblhoadonbanBL ctrhd = new tblhoadonbanBL(); Worksheet sheet = sheets.Add("TKDoanhThu"); sheet.Table.DefaultRowHeight = 15F; sheet.Table.ExpandedColumnCount = 10; if (dtnv.Rows.Count > 1) { try { DataTable dttemp = ctrhd.TKDoanhThu(dttungay.ToString(), dtdenngay.AddDays(1).ToString()); sheet.Table.ExpandedRowCount = 8 + dttemp.Rows.Count; } catch (Exception ex) { return(ex.Message); } } if (dtnv.Rows.Count == 1) { try { DataTable dttemp = ctrhd.TKDoanhThubyIDNV(dttungay.ToString(), dtdenngay.AddDays(1).ToString(), dtnv.Rows[0]["taikhoan"].ToString().Trim()); sheet.Table.ExpandedRowCount = 8 + dttemp.Rows.Count; } catch (Exception ex) { return(ex.Message); } } sheet.Table.FullColumns = 1; sheet.Table.FullRows = 1; WorksheetColumn column0 = sheet.Table.Columns.Add(); column0.Width = 24; column0.StyleID = "s62"; WorksheetColumn column1 = sheet.Table.Columns.Add(); column1.Width = 65; column1.StyleID = "s62"; WorksheetColumn column2 = sheet.Table.Columns.Add(); column2.Width = 78; column2.StyleID = "s62"; WorksheetColumn column3 = sheet.Table.Columns.Add(); column3.Width = 93; column3.StyleID = "s62"; sheet.Table.Columns.Add(132); sheet.Table.Columns.Add(95); sheet.Table.Columns.Add(76); sheet.Table.Columns.Add(75); sheet.Table.Columns.Add(80); sheet.Table.Columns.Add(144); // ----------------------------------------------- #region Add header Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); WorksheetRow Row0 = sheet.Table.Rows.Add(); Row0.Height = 23; Row0.AutoFitHeight = false; WorksheetCell cell; cell = Row0.Cells.Add(); cell.StyleID = "s90"; cell.Data.Type = DataType.String; cell.Data.Text = "THỐNG KÊ DOANH THU"; cell.MergeAcross = 8; // ----------------------------------------------- WorksheetRow Row1 = sheet.Table.Rows.Add(); Row1.AutoFitHeight = false; cell = Row1.Cells.Add(); cell.StyleID = "s91"; cell.Data.Type = DataType.String; cell.Data.Text = "(Từ ngày " + dttungay.ToString("dd/MM/yyyy") + " đến ngày " + dtdenngay.ToString("dd/MM/yyyy") + ")"; cell.MergeAcross = 8; // ----------------------------------------------- WorksheetRow Row2 = sheet.Table.Rows.Add(); Row2.AutoFitHeight = false; cell = Row2.Cells.Add(); cell.StyleID = "s96"; cell.Data.Type = DataType.String; cell.Data.Text = "Đơn vị tính: VNÐ"; cell.MergeAcross = 8; // ----------------------------------------------- WorksheetRow Row3 = sheet.Table.Rows.Add(); Row3.AutoFitHeight = false; Row3.Cells.Add("Stt", DataType.String, "s64"); Row3.Cells.Add("Nhân viên", DataType.String, "s64"); Row3.Cells.Add("Doanh thu", DataType.String, "s64"); Row3.Cells.Add("Ngày xuất HĐ", DataType.String, "s64"); Row3.Cells.Add("Tên khách hàng", DataType.String, "s64"); Row3.Cells.Add("Tiền vốn/HĐ", DataType.String, "s64"); Row3.Cells.Add("Doanh thu/HĐ", DataType.String, "s64"); Row3.Cells.Add("Lợi nhuận/HĐ", DataType.String, "s64"); Row3.Cells.Add("Chiết khấu/HĐ", DataType.String, "s64"); #endregion // ----------------------------------------------- #region Add Content int rowcount = 0; for (int i = 0; i < dtnv.Rows.Count; i++) { Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); string sidnv = ""; try { sidnv = dtnv.Rows[i]["taikhoan"].ToString().Trim(); } catch { } string shoten = ""; try { shoten = dtnv.Rows[i]["hoten"].ToString().Trim(); } catch { shoten = "-/-"; } DataTable dt = new DataTable(); Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); dt = ctrhd.TKDoanhThubyIDNV(dttungay.ToString(), dtdenngay.AddDays(1).ToString(), sidnv); if (i == 0) { rowcount = dt.Rows.Count; } if (i > 0) { if (rowcount > 0) { rowcount = rowcount + dt.Rows.Count; } if (rowcount == 0) { rowcount = dt.Rows.Count; } } for (int j = 0; j < dt.Rows.Count; j++) { WorksheetRow row = sheet.Table.Rows.Add(); if (j == 0) { row.Cells.Add((j + 1).ToString().Trim(), DataType.Number, "s65"); cell = row.Cells.Add(); cell.StyleID = "s92"; cell.Data.Type = DataType.String; cell.Data.Text = shoten; cell.MergeDown = dt.Rows.Count - 1; cell = row.Cells.Add(); cell.StyleID = "m66637128"; cell.Data.Type = DataType.Number; cell.MergeDown = dt.Rows.Count - 1; if (i == 0) { cell.Formula = "=SUM(RC[4]:R[" + (dt.Rows.Count - 1).ToString().Trim() + "]C[4])"; } if (i == 1) { if (dt.Rows.Count > 1) { cell.Formula = "=SUM(RC[4]:R[" + (rowcount - 1 - i).ToString().Trim() + "]C[4])"; } else { cell.Formula = "=SUM(RC[4]:R[0]C[4])"; } } if (i > 1) { if (dt.Rows.Count > 1) { cell.Formula = "=SUM(RC[4]:R[" + (rowcount - 1 - i).ToString().Trim() + "]C[4])"; } else { cell.Formula = "=SUM(RC[4]:R[0]C[4])"; } } DateTime dttemp = Convert.ToDateTime(dt.Rows[j]["ngaytao"].ToString().Trim()); row.Cells.Add(dttemp.ToString("dd/MM/yyyy HH:mm:ss").Trim(), DataType.String, "s67"); row.Cells.Add(dt.Rows[j]["tenkh"].ToString().Trim(), DataType.String, "s67"); row.Cells.Add(dt.Rows[j]["gianhap"].ToString().Trim(), DataType.Number, "s66"); row.Cells.Add(dt.Rows[j]["tongtien"].ToString().Trim(), DataType.Number, "s66"); cell = row.Cells.Add(); cell.StyleID = "s66"; cell.Data.Type = DataType.Number; cell.Formula = "=RC[-1]-RC[-2]-RC[1]"; row.Cells.Add(dt.Rows[j]["chietkhau"].ToString().Trim(), DataType.Number, "s66"); } else { row.Cells.Add((j + 1).ToString().Trim(), DataType.Number, "s65"); cell = row.Cells.Add(); cell.StyleID = "s67"; cell.Data.Type = DataType.String; DateTime dttemp = Convert.ToDateTime(dt.Rows[j]["ngaytao"].ToString().Trim()); cell.Data.Text = dttemp.ToString("dd/MM/yyyy HH:mm:ss").Trim(); cell.Index = 4; row.Cells.Add(dt.Rows[j]["tenkh"].ToString().Trim(), DataType.String, "s67"); row.Cells.Add(dt.Rows[j]["gianhap"].ToString().Trim(), DataType.Number, "s66"); row.Cells.Add(dt.Rows[j]["tongtien"].ToString().Trim(), DataType.Number, "s66"); cell = row.Cells.Add(); cell.StyleID = "s66"; cell.Data.Type = DataType.Number; cell.Formula = "=RC[-1]-RC[-2]-RC[1]"; row.Cells.Add(dt.Rows[j]["chietkhau"].ToString().Trim(), DataType.Number, "s66"); } } } #endregion // ----------------------------------------------- #region Add Tính toán Sum Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); WorksheetRow Row7 = sheet.Table.Rows.Add(); Row7.AutoFitHeight = false; cell = Row7.Cells.Add(); cell.StyleID = "m66637088"; cell.Data.Type = DataType.String; cell.Data.Text = "Tổng doanh thu"; cell.MergeAcross = 7; cell = Row7.Cells.Add(); cell.StyleID = "s68"; cell.Data.Type = DataType.Number; cell.Formula = "=SUM(R[-" + rowcount.ToString().Trim() + "]C[-2]:R[-1]C[-2])"; // ----------------------------------------------- WorksheetRow Row8 = sheet.Table.Rows.Add(); Row8.AutoFitHeight = false; cell = Row8.Cells.Add(); cell.StyleID = "m66637108"; cell.Data.Type = DataType.String; cell.Data.Text = "Tổng lợi nhuận"; cell.MergeAcross = 7; cell = Row8.Cells.Add(); cell.StyleID = "s69"; cell.Data.Type = DataType.Number; cell.Formula = "=SUM(R[-" + (rowcount + 1).ToString().Trim() + "]C[-1]:R[-2]C[-1])"; #endregion // ----------------------------------------------- // Options // ----------------------------------------------- sheet.Options.Selected = true; sheet.Options.ProtectObjects = false; sheet.Options.ProtectScenarios = false; sheet.Options.PageSetup.Header.Margin = 0.3F; sheet.Options.PageSetup.Footer.Margin = 0.3F; sheet.Options.PageSetup.PageMargins.Bottom = 0.75F; sheet.Options.PageSetup.PageMargins.Left = 0.7F; sheet.Options.PageSetup.PageMargins.Right = 0.7F; sheet.Options.PageSetup.PageMargins.Top = 0.75F; sheet.Options.Print.HorizontalResolution = 300; sheet.Options.Print.VerticalResolution = 0; sheet.Options.Print.ValidPrinterInfo = true; return(""); }
public void CreateExcelReportForObj() { dcObj = new PartDataContext(); Warehouse w = string.IsNullOrEmpty(WarehouseId) ? null : dcObj.ActiveWarehouses.SingleOrDefault(wh => wh.WarehouseId == long.Parse(WarehouseId)); Dealer d = dcObj.Dealers.SingleOrDefault(dl => dl.DealerCode == DealerCode); if (d == null) return; string path = Path.Combine(HttpRuntime.AppDomainAppPath, PartMonthlyReport.GetMonthlyReportObjFilePath(w == null ? null : w.Code, DealerCode, Month, Year)); List<MonthlyReportObject> data = null; //for (int i = 0; i < 40; i++) { data = GetMonthlyReportObjects(w, d, Month, Year); } Workbook book = new Workbook(); Worksheet sheet = book.Worksheets.Add(string.Format("{0}.{1}", DealerCode, w == null ? "All" : w.Code)); #region Setting - styles // Some optional properties of the Document book.ExcelWorkbook.ActiveSheetIndex = 1; book.Properties.Author = "ThangLong"; book.Properties.Title = "Stock monthly report by components"; book.Properties.Created = DateTime.Now; // Add some styles to the Workbook WorksheetStyle style = book.Styles.Add("HeaderStyle"); //style.Font.FontName = "Tahoma"; //style.Font.Size = 13; style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous).Weight = 1; style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous).Weight = 1; style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous).Weight = 1; style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous).Weight = 1; style.Font.Bold = true; style.Alignment.Horizontal = StyleHorizontalAlignment.Center; style.Alignment.Vertical = StyleVerticalAlignment.Center; style.Font.Color = "White"; style.Interior.Color = "#555555"; style.Interior.Pattern = StyleInteriorPattern.Solid; style = book.Styles.Add("Group"); style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous); style.Alignment.Vertical = StyleVerticalAlignment.Center; style.Interior.Color = "#CCCCCC"; style.Interior.Pattern = StyleInteriorPattern.Solid; style = book.Styles.Add("GroupCenter"); style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous); style.Alignment.Vertical = StyleVerticalAlignment.Center; style.Alignment.Horizontal = StyleHorizontalAlignment.Center; style.Interior.Color = "#CCCCCC"; style.Interior.Pattern = StyleInteriorPattern.Solid; style = book.Styles.Add("DetailOdd"); style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous); style.Alignment.Vertical = StyleVerticalAlignment.Center; style = book.Styles.Add("DetailEven"); style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous); style.Alignment.Vertical = StyleVerticalAlignment.Center; style.Interior.Color = "#f1f5fa"; style.Interior.Pattern = StyleInteriorPattern.Solid; style = book.Styles.Add("DetailOddCenter"); style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous); style.Alignment.Horizontal = StyleHorizontalAlignment.Center; style.Alignment.Vertical = StyleVerticalAlignment.Center; style = book.Styles.Add("DetailEvenCenter"); style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous); style.Alignment.Horizontal = StyleHorizontalAlignment.Center; style.Alignment.Vertical = StyleVerticalAlignment.Center; style.Interior.Color = "#f1f5fa"; style.Interior.Pattern = StyleInteriorPattern.Solid; // Create the Default Style to use for everyone style = book.Styles.Add("Default"); style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous); style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous); style.Alignment.Vertical = StyleVerticalAlignment.Center; #endregion #region column settings WorksheetColumn col; col = new WorksheetColumn(30); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // plType col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // plCode col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // plName col = new WorksheetColumn(20); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // No col = new WorksheetColumn(30); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // pType col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // pCode col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // pEname col = new WorksheetColumn(100); sheet.Table.Columns.Add(col); col.AutoFitWidth = false; // pVname col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // Begin col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // In col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // InAmount col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // Out col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // OutAmount col = new WorksheetColumn(); sheet.Table.Columns.Add(col); col.AutoFitWidth = true; // Balance #endregion #region header rows WorksheetCell cell; WorksheetRow row = sheet.Table.Rows.Add(); cell = row.Cells.Add("Report for", DataType.String, "HeaderStyle"); cell.MergeAcross = 2; cell = row.Cells.Add("Part", DataType.String, "HeaderStyle"); cell.MergeAcross = 4; cell = row.Cells.Add("Begin", DataType.String, "HeaderStyle"); cell.MergeDown = 1; cell = row.Cells.Add("In", DataType.String, "HeaderStyle"); cell.MergeAcross = 1; cell = row.Cells.Add("Out", DataType.String, "HeaderStyle"); cell.MergeAcross = 1; cell = row.Cells.Add("Balance", DataType.String, "HeaderStyle"); cell.MergeDown = 1; row = sheet.Table.Rows.Add(); cell = row.Cells.Add("Type", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Code", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Name", DataType.String, "HeaderStyle"); cell = row.Cells.Add("No", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Type", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Code", DataType.String, "HeaderStyle"); cell = row.Cells.Add("English name", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Vietnamese name", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Quantity", DataType.String, "HeaderStyle"); cell.Index = 10; cell = row.Cells.Add("Amount", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Quantity", DataType.String, "HeaderStyle"); cell = row.Cells.Add("Amount", DataType.String, "HeaderStyle"); #endregion #region bind data string pOddStyle = w == null ? "Group" : "DetailOdd", pEvenStyle = w == null ? "Group" : "DetailOdd", pOddCenterStyle = w == null ? "GroupCenter" : "DetailOddCenter", EvenCenterStyle = w == null ? "GroupCenter" : "DetailOddCenter", dOddStyle = "DetailOdd", dEvenStyle = "DetailEven", dOddCenterStyle = "DetailOddCenter", dEvenCenterStyle = "DetailEvenCenter"; int pIndex = 0; data.ForEach(part => { pIndex++; bool odd = pIndex % 2 == 1; string pStyle = odd ? pOddStyle : pEvenStyle; string pcStyle = odd ? pOddCenterStyle : EvenCenterStyle; // group line row = sheet.Table.Rows.Add(); cell = row.Cells.Add(part.PlaceType, DataType.String, pcStyle); cell = row.Cells.Add(part.PlaceCode, DataType.String, pStyle); cell = row.Cells.Add(part.PlaceName, DataType.String, pStyle); cell = row.Cells.Add("", DataType.String, pcStyle); cell = row.Cells.Add("", DataType.String, pcStyle); cell = row.Cells.Add("", DataType.String, pStyle); cell = row.Cells.Add("", DataType.String, pStyle); cell = row.Cells.Add("", DataType.String, pStyle); cell = row.Cells.Add(part.BeginQuantity.ToString(), DataType.Number, pStyle); //cell.Index = 9; cell = row.Cells.Add(part.InQuantity.ToString(), DataType.Number, pStyle); cell = row.Cells.Add(part.InAmount.ToString(), DataType.Number, pStyle); cell = row.Cells.Add(part.OutQuantity.ToString(), DataType.Number, pStyle); cell = row.Cells.Add(part.OutAmount.ToString(), DataType.Number, pStyle); cell = row.Cells.Add(part.Balance.ToString(), DataType.Number, pStyle); // detail lines int index = 0; AddMonthlyReportObjDetailRows(ref index, sheet, part.PartList, dOddStyle, dEvenStyle, dOddCenterStyle, dEvenCenterStyle); AddMonthlyReportObjDetailRows(ref index, sheet, part.AccList, dOddStyle, dEvenStyle, dOddCenterStyle, dEvenCenterStyle); }); #endregion dcObj.Dispose(); dcObj = null; // Save the file book.Save(path); }
private void GenerateWorksheetSheet1(WorksheetCollection sheets, Order order) { Culture.InitializeCulture(); Worksheet sheet = sheets.Add(Resource.Admin_ViewOrder_ItemNum + order.OrderID); sheet.Table.DefaultRowHeight = 15F; sheet.Table.ExpandedColumnCount = 5; List <TaxValue> taxedItems = TaxServices.GetOrderTaxes(order.OrderID); sheet.Table.ExpandedRowCount = 40 + order.OrderItems.Count * 2 + taxedItems.Count; sheet.Table.FullColumns = 1; sheet.Table.FullRows = 1; WorksheetColumn column0 = sheet.Table.Columns.Add(); //column0.Width = 186; Commented By Evgeni to enlarge column width column0.Width = 250; column0.Span = 1; WorksheetColumn column1 = sheet.Table.Columns.Add(); column1.Index = 3; column1.Width = 156; sheet.Table.Columns.Add(120); sheet.Table.Columns.Add(89); // Order ID----------------------------------------------- WorksheetRow Row0 = sheet.Table.Rows.Add(); Row0.Height = 22; WorksheetCell cell; cell = Row0.Cells.Add(); cell.StyleID = "s132"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ItemNum + order.OrderID; cell.MergeAcross = 4; // Status ----------------------------------------------- WorksheetRow Row1 = sheet.Table.Rows.Add(); cell = Row1.Cells.Add(); cell.StyleID = "s133"; cell.Data.Type = DataType.String; cell.Data.Text = "(" + order.OrderStatus.StatusName + ")"; cell.MergeAcross = 4; // ----------------------------------------------- WorksheetRow Row2 = sheet.Table.Rows.Add(); cell = Row2.Cells.Add(); cell.StyleID = "s134"; cell.MergeAcross = 4; // Date ----------------------------------------------- WorksheetRow Row3 = sheet.Table.Rows.Add(); cell = Row3.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Date; cell = Row3.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Culture.ConvertDate(order.OrderDate); cell.MergeAcross = 3; // NUmber to status check ----------------------------------------------- WorksheetRow Row4 = sheet.Table.Rows.Add(); cell = Row4.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Number; cell = Row4.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = order.Number; cell.MergeAcross = 3; // Status comment ----------------------------------------------- WorksheetRow Row5 = sheet.Table.Rows.Add(); cell = Row5.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_StatusComment; cell = Row5.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = order.StatusComment; cell.MergeAcross = 3; // ----------------------------------------------- WorksheetRow Row6 = sheet.Table.Rows.Add(); cell = Row6.Cells.Add(); cell.StyleID = "s136"; cell.MergeAcross = 4; // Headers ----------------------------------------------- WorksheetRow Row7 = sheet.Table.Rows.Add(); Row7.Cells.Add(Resource.Admin_ViewOrder_Billing, DataType.String, "s70"); Row7.Cells.Add(Resource.Admin_ViewOrder_Shipping, DataType.String, "s70"); Row7.Cells.Add(Resource.Admin_ViewOrder_ShippingMethod, DataType.String, "s70"); // Names ----------------------------------------------- WorksheetRow Row8 = sheet.Table.Rows.Add(); Row8.Cells.Add(" " + Resource.Admin_ViewOrder_Name + order.BillingContact.Name, DataType.String, "s70"); Row8.Cells.Add(" " + Resource.Admin_ViewOrder_Name + order.ShippingContact.Name, DataType.String, "s70"); var shippingMethodName = order.ArchivedShippingName; if (order.OrderPickPoint != null) { shippingMethodName += order.OrderPickPoint.PickPointAddress.Replace("<br/>", " "); } Row8.Cells.Add(" " + shippingMethodName, DataType.String, "s70"); // Countries ----------------------------------------------- WorksheetRow Row9 = sheet.Table.Rows.Add(); Row9.Cells.Add(" " + Resource.Admin_ViewOrder_Country + order.BillingContact.Country, DataType.String, "s70"); Row9.Cells.Add(" " + Resource.Admin_ViewOrder_Country + order.ShippingContact.Country, DataType.String, "s70"); Row9.Cells.Add(Resource.Admin_ViewOrder_PaymentType, DataType.String, "s70"); // Cities ----------------------------------------------- WorksheetRow Row10 = sheet.Table.Rows.Add(); Row10.Cells.Add(" " + Resource.Admin_ViewOrder_City + order.BillingContact.City, DataType.String, "s70"); Row10.Cells.Add(" " + Resource.Admin_ViewOrder_City + order.ShippingContact.City, DataType.String, "s70"); Row10.Cells.Add(" " + order.PaymentMethodName, DataType.String, "s70"); // Zones ----------------------------------------------- WorksheetRow Row11 = sheet.Table.Rows.Add(); Row11.Cells.Add(" " + Resource.Admin_ViewOrder_Zone + order.BillingContact.Zone, DataType.String, "s70"); Row11.Cells.Add(" " + Resource.Admin_ViewOrder_Zone + order.ShippingContact.Zone, DataType.String, "s70"); cell = Row11.Cells.Add(); cell.StyleID = "s70"; // Zips ----------------------------------------------- WorksheetRow Row12 = sheet.Table.Rows.Add(); Row12.Cells.Add(" " + Resource.Admin_ViewOrder_Zip + order.BillingContact.Zip, DataType.String, "s70"); Row12.Cells.Add(" " + Resource.Admin_ViewOrder_Zip + order.ShippingContact.Zip, DataType.String, "s70"); cell = Row12.Cells.Add(); cell.StyleID = "s70"; // Adresses ----------------------------------------------- WorksheetRow Row13 = sheet.Table.Rows.Add(); Row13.Cells.Add(" " + Resource.Admin_ViewOrder_Address + order.BillingContact.Address, DataType.String, "s70"); Row13.Cells.Add(" " + Resource.Admin_ViewOrder_Address + order.ShippingContact.Address, DataType.String, "s70"); cell = Row13.Cells.Add(); cell.StyleID = "s70"; // Added by Evgeni //Tel WorksheetRow Row14 = sheet.Table.Rows.Add(); Row14.Cells.Add(" " + Resource.Client_OrderConfirmation_MobilePhone + " :" + order.OrderCustomer.MobilePhone, DataType.String, "s70"); Row14.Cells.Add(" " + Resource.Client_OrderConfirmation_MobilePhone + " :" + order.OrderCustomer.MobilePhone, DataType.String, "s70"); cell = Row14.Cells.Add(); cell.StyleID = "s70"; // // ----------------------------------------------- WorksheetRow Row17 = sheet.Table.Rows.Add(); cell = Row17.Cells.Add(); cell.StyleID = "s134"; cell.MergeAcross = 4; // Orders ----------------------------------------------- WorksheetRow Row18 = sheet.Table.Rows.Add(); cell = Row18.Cells.Add(); cell.StyleID = "s136"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_OrderItem; cell.MergeAcross = 4; // ----------------------------------------------- WorksheetRow Row19 = sheet.Table.Rows.Add(); Row19.Height = 15; cell = Row19.Cells.Add(); cell.StyleID = "s137"; cell.MergeAcross = 4; // Order items table header ----------------------------------------------- WorksheetRow Row20 = sheet.Table.Rows.Add(); Row20.Height = 16; Row20.Cells.Add(Resource.Admin_ViewOrder_ItemName, DataType.String, "s89"); Row20.Cells.Add(Resource.Admin_ViewOrder_CustomOptions, DataType.String, "s90"); Row20.Cells.Add(Resource.Admin_ViewOrder_Price, DataType.String, "s90"); Row20.Cells.Add(Resource.Admin_ViewOrder_ItemAmount, DataType.String, "s90"); Row20.Cells.Add(Resource.Admin_ViewOrder_ItemCost, DataType.String, "s91"); // Order items ----------------------------------------------- foreach (OrderItem item in order.OrderItems) { WorksheetRow Row = sheet.Table.Rows.Add(); cell = Row.Cells.Add(); cell.StyleID = "m51494176"; cell.Data.Type = DataType.String; //Changed by Evgeni to isert ArtNo in order print cell.Data.Text = item.Name + " [" + item.ArtNo + "]"; // cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494196"; var html = new StringBuilder(); foreach (EvaluatedCustomOptions ev in item.SelectedOptions) { html.Append(string.Format("- {0}: {1} \n", ev.CustomOptionTitle, ev.OptionTitle)); } //Changed by Evgeni to isert ArtNo in order print cell.Data.Text = " " + item.ArtNo + " " + html.ToString(); // cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494216"; cell.Data.Type = DataType.String; bool ciscodebefore; string cpriceformat = null; string csign = null; Currency cur = CurrencyService.Currency(order.OrderCurrency.CurrencyCode); ciscodebefore = cur.IsCodeBefore; cpriceformat = cur.PriceFormat; csign = cur.Symbol; cell.Data.Text = CatalogService.GetStringPrice(item.Price, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode); cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494236"; cell.Data.Type = DataType.Number; cell.Data.Text = item.Amount.ToString(); cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494256"; cell.Data.Type = DataType.String; cell.Data.Text = CatalogService.GetStringPrice(item.Price * item.Amount, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode); cell.MergeDown = 1; // ----------------------------------------------- WorksheetRow RowSep = sheet.Table.Rows.Add(); RowSep.Height = 15; } // ----------------------------------------------- WorksheetRow Row27 = sheet.Table.Rows.Add(); Row27.Height = 15; cell = Row27.Cells.Add(); cell.StyleID = "s70"; cell = Row27.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ItemCost2; cell.Index = 4; decimal orderPrice = ((order.Sum - order.ShippingCost) * 100) / (100 - order.OrderDiscount); Row27.Cells.Add( CatalogService.GetStringPrice(order.Sum, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); // ----------------------------------------------- WorksheetRow Row28 = sheet.Table.Rows.Add(); cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ItemDiscount; cell.Index = 4; decimal discount = orderPrice * (order.OrderDiscount / 100); Row28.Cells.Add( "-" + CatalogService.GetStringPrice(discount, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); // ----------------------------------------------- WorksheetRow Row29 = sheet.Table.Rows.Add(); cell = Row29.Cells.Add(); cell.StyleID = "s70"; cell = Row29.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ShippingPrice; cell.Index = 4; Row29.Cells.Add( "+" + CatalogService.GetStringPrice(order.ShippingCost, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); // ----------------------------------------------- if (taxedItems.Count > 0) { foreach (TaxValue tax in taxedItems) { WorksheetRow Row = sheet.Table.Rows.Add(); cell = Row.Cells.Add(); cell.StyleID = "s70"; cell = Row.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = (tax.TaxShowInPrice ? Resource.Core_TaxServices_Include_Tax + " " : "") + tax.TaxName + ":"; cell.Index = 4; Row.Cells.Add( (tax.TaxShowInPrice ? "" : "+") + CatalogService.GetStringPrice(tax.TaxSum, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); } } else { WorksheetRow Row30a = sheet.Table.Rows.Add(); cell = Row30a.Cells.Add(); cell.StyleID = "s70"; cell = Row30a.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Taxes; cell.Index = 4; Row30a.Cells.Add( "+" + CatalogService.GetStringPrice(0, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); } // ----------------------------------------------- WorksheetRow Row30 = sheet.Table.Rows.Add(); cell = Row30.Cells.Add(); cell.StyleID = "s70"; cell = Row30.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_TotalPrice; cell.Index = 4; cell = Row30.Cells.Add( CatalogService.GetStringPrice(order.Sum, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s135"); // ----------------------------------------------- WorksheetRow Row31 = sheet.Table.Rows.Add(); cell = Row31.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Client_PrintOrder_YourComment; cell.MergeAcross = 4; // ----------------------------------------------- WorksheetRow Row32 = sheet.Table.Rows.Add(); cell = Row32.Cells.Add(); cell.StyleID = "s141"; cell.Data.Type = DataType.String; cell.Data.Text = order.CustomerComment; cell.MergeAcross = 4; cell.MergeDown = 1; // ----------------------------------------------- // Options // ----------------------------------------------- sheet.Options.Selected = true; sheet.Options.ProtectObjects = false; sheet.Options.ProtectScenarios = false; sheet.Options.PageSetup.Header.Margin = 0.3F; sheet.Options.PageSetup.Footer.Margin = 0.3F; sheet.Options.PageSetup.PageMargins.Bottom = 0.75F; sheet.Options.PageSetup.PageMargins.Left = 0.7F; sheet.Options.PageSetup.PageMargins.Right = 0.7F; sheet.Options.PageSetup.PageMargins.Top = 0.75F; sheet.Options.Print.PaperSizeIndex = 9; sheet.Options.Print.VerticalResolution = 0; sheet.Options.Print.ValidPrinterInfo = true; }
/// ////////////////////////////////////////////// public CResultAErreur Export(DataSet ds, IDestinationExport destination) { CResultAErreur result = CResultAErreur.True; if (!(destination is CDestinationExportFile)) { result.EmpileErreur(I.T("An Excel export can be done only in a file|171")); return(result); } string strNomFichier = ((CDestinationExportFile)destination).FileName; try { ds.EnforceConstraints = false; if (File.Exists(strNomFichier)) { File.Delete(strNomFichier); } Workbook book = new Workbook(); WorksheetStyle style = book.Styles.Add("SC2I_DATE"); style.NumberFormat = I.T("Short DATE|170"); //Nom de table->true : pour éviter 2 fois le même nom de table Hashtable tableTablesCrees = new Hashtable(); Hashtable tableColumnsToHide = new Hashtable(); Regex colKeyAuto = new Regex("^Key_"); if (m_bMasquerIdsAuto) { foreach (DataTable table in ds.Tables) { foreach (DataColumn col in table.PrimaryKey) { if (col.AutoIncrement || colKeyAuto.IsMatch(col.ColumnName)) { tableColumnsToHide[col] = true; } } } foreach (DataRelation rel in ds.Relations) { if (rel.ParentColumns.Length == 1 && tableColumnsToHide.Contains(rel.ParentColumns[0]) && rel.ChildColumns.Length == 1) { tableColumnsToHide[rel.ChildColumns[0]] = true; } } } foreach (DataTable table in ds.Tables) { Worksheet sheet = book.Worksheets.Add(table.TableName); //Création des colonnes WorksheetRow row = sheet.Table.Rows.Add( ); DataType[] typeCol = new DataType[table.Columns.Count]; int nCol = 0; foreach (DataColumn col in table.Columns) { if (!tableColumnsToHide.Contains(col)) { WorksheetColumn colSheet = sheet.Table.Columns.Add( ); DataType tp = GetExcelType(col.DataType); typeCol[nCol] = tp; nCol++; row.Cells.Add(ConvertName(col.ColumnName)); if (tp == DataType.DateTime) { colSheet.StyleID = "SC2I_DATE"; } } } if (!m_bOnlyStructure) { //Insertion des données foreach (DataRow rowNet in table.Rows) { row = sheet.Table.Rows.Add(); nCol = 0; foreach (DataColumn col in table.Columns) { if (!tableColumnsToHide.Contains(col)) { object val = rowNet[col]; if (val == DBNull.Value) { row.Cells.Add(); } else { string strVal = ""; if (typeCol[nCol] == DataType.DateTime) { if (val is CDateTimeEx) { val = ((CDateTimeEx)val).DateTimeValue; } strVal = ((DateTime)val).ToString("yyyy-MM-ddTHH:mm:ss"); } else { strVal = val.ToString(); } row.Cells.Add(strVal, typeCol[nCol], null); } nCol++; } } } } } book.Save(strNomFichier); } catch (Exception e) { result.EmpileErreur(new CErreurException(e)); return(result); } finally { ds.EnforceConstraints = true; } return(result); }
//public static Workbook GenerateExcel(List<ExcelColumn> columns, DataSet data, bool generateHeader) //{ // Workbook book = new Workbook(); // Worksheet sheet = book.Worksheets.Add("Assortment"); // WorksheetRow row; // GenerateStyles(book.Styles); // foreach (var name in columns) // { // WorksheetColumn column = new WorksheetColumn(); // column.AutoFitWidth = true; // sheet.Table.Columns.Add(column); // } // int rowIndex = GenerateHeaders(sheet, generateHeader, columns); // Dictionary<int, Dictionary<string, string>> excelRows = new Dictionary<int, Dictionary<string, string>>(); // foreach (var productRow in data.Tables[0].AsEnumerable()) // { // rowIndex++; // var product = productRow; // Dictionary<string, string> excelvalues = new Dictionary<string, string>(); // foreach (var att in data.Tables[0].AsEnumerable()) // { // if (excelvalues.ContainsKey(att.Field<string>)) // excelvalues[att.Name.LocalName] = att.Value; // else // excelvalues.Add(att.Name.LocalName, att.Value); // } // foreach (XElement el in product.Elements()) // { // if (columns.Any(x => x.ValueFieldName == el.Name.LocalName)) // { // if (excelvalues.ContainsKey(el.Name.LocalName)) // excelvalues[el.Name.LocalName] = el.Value; // else // excelvalues.Add(el.Name.LocalName, el.Value); // } // foreach (XAttribute att in el.Attributes()) // { // if (columns.Any(x => x.ValueFieldName == att.Name.LocalName)) // { // if (excelvalues.ContainsKey(att.Name.LocalName)) // excelvalues[att.Name.LocalName] = att.Value; // else // excelvalues.Add(att.Name.LocalName, att.Value); // } // } // } // excelRows.Add(rowIndex, excelvalues); // } // foreach (var val in excelRows.Keys) // { // row = sheet.Table.Rows.Add(); // foreach (var key in columns) // { // row.Cells.Add(excelRows[val][key.ColumnName]); // } // } // return book; //} public static Workbook GenerateExcel(int connectorID, List <ExcelColumn> columns, bool generateHeader) { AssortmentServiceSoapClient client = new AssortmentServiceSoapClient(); var assortmentSource = new XDocument(client.GetAssortment(1, null, true)); Workbook book = new Workbook(); Worksheet sheet = book.Worksheets.Add("Assortment"); WorksheetRow row; GenerateStyles(book.Styles); foreach (var name in columns) { WorksheetColumn column = new WorksheetColumn(); column.AutoFitWidth = true; sheet.Table.Columns.Add(column); } int rowIndex = GenerateHeaders(sheet, generateHeader, columns); Dictionary <int, Dictionary <string, string> > excelRows = new Dictionary <int, Dictionary <string, string> >(); foreach (var productRow in assortmentSource.Root.Elements("Product")) { rowIndex++; var product = productRow; Dictionary <string, string> excelvalues = new Dictionary <string, string>(); foreach (XAttribute att in product.Attributes()) { if (columns.Any(x => x.ValueFieldName == att.Name.LocalName)) { if (excelvalues.ContainsKey(att.Name.LocalName)) { excelvalues[att.Name.LocalName] = att.Value; } else { excelvalues.Add(att.Name.LocalName, att.Value); } } } foreach (XElement el in product.Elements()) { if (columns.Any(x => x.ValueFieldName == el.Name.LocalName)) { if (excelvalues.ContainsKey(el.Name.LocalName)) { excelvalues[el.Name.LocalName] = el.Value; } else { excelvalues.Add(el.Name.LocalName, el.Value); } } foreach (XAttribute att in el.Attributes()) { if (columns.Any(x => x.ValueFieldName == att.Name.LocalName)) { if (excelvalues.ContainsKey(att.Name.LocalName)) { excelvalues[att.Name.LocalName] = att.Value; } else { excelvalues.Add(att.Name.LocalName, att.Value); } } } } excelRows.Add(rowIndex, excelvalues); } foreach (var val in excelRows.Keys) { row = sheet.Table.Rows.Add(); foreach (var key in columns) { row.Cells.Add(excelRows[val][key.ColumnName]); } } return(book); }
private void GenerateWorksheetSheet1(WorksheetCollection sheets, Order order) { Culture.InitializeCulture(); Worksheet sheet = sheets.Add(Resource.Admin_ViewOrder_ItemNum + order.OrderID); sheet.Table.DefaultRowHeight = 15F; sheet.Table.ExpandedColumnCount = 5; List <OrderTax> taxedItems = TaxServices.GetOrderTaxes(order.OrderID); sheet.Table.ExpandedRowCount = 42 + order.OrderItems.Count * 2 + taxedItems.Count; sheet.Table.FullColumns = 1; sheet.Table.FullRows = 1; WorksheetColumn column0 = sheet.Table.Columns.Add(); column0.Width = 186; column0.Span = 1; WorksheetColumn column1 = sheet.Table.Columns.Add(); column1.Index = 3; column1.Width = 156; sheet.Table.Columns.Add(120); sheet.Table.Columns.Add(89); // Order ID----------------------------------------------- WorksheetRow Row0 = sheet.Table.Rows.Add(); Row0.Height = 22; WorksheetCell cell; cell = Row0.Cells.Add(); cell.StyleID = "s132"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ItemNum + order.OrderID; cell.MergeAcross = 4; // Status ----------------------------------------------- WorksheetRow Row1 = sheet.Table.Rows.Add(); cell = Row1.Cells.Add(); cell.StyleID = "s133"; cell.Data.Type = DataType.String; cell.Data.Text = "(" + order.OrderStatus.StatusName + ")"; cell.MergeAcross = 4; // ----------------------------------------------- WorksheetRow Row2 = sheet.Table.Rows.Add(); cell = Row2.Cells.Add(); cell.StyleID = "s134"; cell.MergeAcross = 4; // Date ----------------------------------------------- WorksheetRow Row3 = sheet.Table.Rows.Add(); cell = Row3.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Date; cell = Row3.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Culture.ConvertDate(order.OrderDate); cell.MergeAcross = 3; // NUmber to status check ----------------------------------------------- WorksheetRow Row4 = sheet.Table.Rows.Add(); cell = Row4.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Number; cell = Row4.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = order.Number; cell.MergeAcross = 3; // Status comment ----------------------------------------------- WorksheetRow Row5 = sheet.Table.Rows.Add(); cell = Row5.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_StatusComment; cell = Row5.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = order.StatusComment; cell.MergeAcross = 3; WorksheetRow RowEmail = sheet.Table.Rows.Add(); cell = RowEmail.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewCustomer_Email; cell = RowEmail.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = order.OrderCustomer.Email; cell.MergeAcross = 3; WorksheetRow RowPhone = sheet.Table.Rows.Add(); cell = RowPhone.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_CommonSettings_Phone; cell = RowPhone.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = order.OrderCustomer.MobilePhone; cell.MergeAcross = 3; // ----------------------------------------------- WorksheetRow Row6 = sheet.Table.Rows.Add(); cell = Row6.Cells.Add(); cell.StyleID = "s136"; cell.MergeAcross = 4; // Headers ----------------------------------------------- WorksheetRow Row7 = sheet.Table.Rows.Add(); Row7.Cells.Add(Resource.Admin_ViewOrder_Billing, DataType.String, "s70"); Row7.Cells.Add(Resource.Admin_ViewOrder_Shipping, DataType.String, "s70"); Row7.Cells.Add(Resource.Admin_ViewOrder_ShippingMethod, DataType.String, "s70"); // Names ----------------------------------------------- WorksheetRow Row8 = sheet.Table.Rows.Add(); Row8.Cells.Add("���� " + Resource.Admin_ViewOrder_Name + order.BillingContact.Name, DataType.String, "s70"); Row8.Cells.Add("���� " + Resource.Admin_ViewOrder_Name + order.ShippingContact.Name, DataType.String, "s70"); var shippingMethodName = order.ArchivedShippingName; if (order.OrderPickPoint != null) { shippingMethodName += order.OrderPickPoint.PickPointAddress.Replace("<br/>", " "); } Row8.Cells.Add("���� " + shippingMethodName, DataType.String, "s70"); // Countries ----------------------------------------------- WorksheetRow Row9 = sheet.Table.Rows.Add(); Row9.Cells.Add("�����" + Resource.Admin_ViewOrder_Country + order.BillingContact.Country, DataType.String, "s70"); Row9.Cells.Add("�����" + Resource.Admin_ViewOrder_Country + order.ShippingContact.Country, DataType.String, "s70"); Row9.Cells.Add(Resource.Admin_ViewOrder_PaymentType, DataType.String, "s70"); // Cities ----------------------------------------------- WorksheetRow Row10 = sheet.Table.Rows.Add(); Row10.Cells.Add("�����" + Resource.Admin_ViewOrder_City + order.BillingContact.City, DataType.String, "s70"); Row10.Cells.Add("�����" + Resource.Admin_ViewOrder_City + order.ShippingContact.City, DataType.String, "s70"); Row10.Cells.Add("���� " + order.PaymentMethodName, DataType.String, "s70"); // Zones ----------------------------------------------- WorksheetRow Row11 = sheet.Table.Rows.Add(); Row11.Cells.Add("�����" + Resource.Admin_ViewOrder_Zone + order.BillingContact.Zone, DataType.String, "s70"); Row11.Cells.Add("�����" + Resource.Admin_ViewOrder_Zone + order.ShippingContact.Zone, DataType.String, "s70"); cell = Row11.Cells.Add(); cell.StyleID = "s70"; // Zips ----------------------------------------------- WorksheetRow Row12 = sheet.Table.Rows.Add(); Row12.Cells.Add("�����" + Resource.Admin_ViewOrder_Zip + order.BillingContact.Zip, DataType.String, "s70"); Row12.Cells.Add("�����" + Resource.Admin_ViewOrder_Zip + order.ShippingContact.Zip, DataType.String, "s70"); cell = Row12.Cells.Add(); cell.StyleID = "s70"; // Adresses ----------------------------------------------- WorksheetRow Row13 = sheet.Table.Rows.Add(); Row13.Cells.Add("�����" + Resource.Admin_ViewOrder_Address + order.BillingContact.Address, DataType.String, "s70"); Row13.Cells.Add("�����" + Resource.Admin_ViewOrder_Address + order.ShippingContact.Address, DataType.String, "s70"); cell = Row13.Cells.Add(); cell.StyleID = "s70"; // ----------------------------------------------- WorksheetRow Row17 = sheet.Table.Rows.Add(); cell = Row17.Cells.Add(); cell.StyleID = "s134"; cell.MergeAcross = 4; // Orders ----------------------------------------------- WorksheetRow Row18 = sheet.Table.Rows.Add(); cell = Row18.Cells.Add(); cell.StyleID = "s136"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_OrderItem; cell.MergeAcross = 4; // ----------------------------------------------- WorksheetRow Row19 = sheet.Table.Rows.Add(); Row19.Height = 15; cell = Row19.Cells.Add(); cell.StyleID = "s137"; cell.MergeAcross = 4; // Order items table header ----------------------------------------------- WorksheetRow Row20 = sheet.Table.Rows.Add(); Row20.Height = 16; Row20.Cells.Add(Resource.Admin_ViewOrder_ItemName, DataType.String, "s89"); Row20.Cells.Add(Resource.Admin_ViewOrder_CustomOptions, DataType.String, "s90"); Row20.Cells.Add(Resource.Admin_ViewOrder_Price, DataType.String, "s90"); Row20.Cells.Add(Resource.Admin_ViewOrder_ItemAmount, DataType.String, "s90"); Row20.Cells.Add(Resource.Admin_ViewOrder_ItemCost, DataType.String, "s91"); // Order items ----------------------------------------------- foreach (OrderItem item in order.OrderItems) { WorksheetRow Row = sheet.Table.Rows.Add(); cell = Row.Cells.Add(); cell.StyleID = "m51494176"; cell.Data.Type = DataType.String; cell.Data.Text = item.ArtNo + ", " + item.Name; cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494196"; var html = new StringBuilder(); if (!string.IsNullOrEmpty(item.Color)) { html.Append(Configuration.SettingsCatalog.ColorsHeader + ": " + item.Color + " \n"); } if (!string.IsNullOrEmpty(item.Size)) { html.Append(Configuration.SettingsCatalog.SizesHeader + ": " + item.Size + " \n"); } foreach (EvaluatedCustomOptions ev in item.SelectedOptions) { html.Append(string.Format("- {0}: {1} \n", ev.CustomOptionTitle, ev.OptionTitle)); } cell.Data.Text = html.ToString(); cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494216"; cell.Data.Type = DataType.String; cell.Data.Text = CatalogService.GetStringPrice(item.Price, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode); cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494236"; cell.Data.Type = DataType.String; cell.Data.Text = item.Amount.ToString(); cell.MergeDown = 1; cell = Row.Cells.Add(); cell.StyleID = "m51494256"; cell.Data.Type = DataType.String; cell.Data.Text = CatalogService.GetStringPrice(item.Price * item.Amount, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode); cell.MergeDown = 1; // ----------------------------------------------- WorksheetRow RowSep = sheet.Table.Rows.Add(); RowSep.Height = 15; } // ----------------------------------------------- WorksheetRow Row27 = sheet.Table.Rows.Add(); Row27.Height = 15; cell = Row27.Cells.Add(); cell.StyleID = "s70"; cell = Row27.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ItemCost2; cell.Index = 4; var ordCurrency = order.OrderCurrency; float productPrice = order.OrderItems.Sum(item => item.Amount * item.Price); Row27.Cells.Add( CatalogService.GetStringPrice(productPrice, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode), DataType.String, "s70"); //----------------------------------------------- if (order.Coupon != null) { WorksheetRow Row28 = sheet.Table.Rows.Add(); cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Coupon; cell.Index = 4; var productsWithCoupon = order.OrderItems.Where(item => item.IsCouponApplied).Sum(item => item.Price * item.Amount); switch (order.Coupon.Type) { case CouponType.Fixed: Row28.Cells.Add(String.Format("-{0} ({1})", CatalogService.GetStringPrice(order.Coupon.Value, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode), order.Coupon.Code), DataType.String, "s70"); break; case CouponType.Percent: Row28.Cells.Add(String.Format("-{0} ({1}%) ({2})", CatalogService.GetStringPrice(productsWithCoupon * order.Coupon.Value / 100, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode), CatalogService.FormatPriceInvariant(order.Coupon.Value), order.Coupon.Code), DataType.String, "s70"); break; } } // ----------------------------------------------- float totalDiscount = order.OrderDiscount; if (totalDiscount > 0) { WorksheetRow Row28 = sheet.Table.Rows.Add(); cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ItemDiscount; cell.Index = 4; Row28.Cells.Add( "-" + CatalogService.GetStringDiscountPercent(productPrice, totalDiscount, ordCurrency.CurrencyValue, ordCurrency.CurrencySymbol, ordCurrency.IsCodeBefore, CurrencyService.CurrentCurrency.PriceFormat, false), DataType.String, "s70"); } // ------------------------------------------------------- if (order.Certificate != null) { WorksheetRow Row28 = sheet.Table.Rows.Add(); cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell = Row28.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ItemDiscount; cell.Index = 4; Row28.Cells.Add( "-" + string.Format("-{0}", CatalogService.GetStringPrice(order.Certificate.Price, ordCurrency.CurrencyValue, ordCurrency.CurrencyCode)), DataType.String, "s70"); } // ----------------------------------------------- float bonusPrice = order.BonusCost; if (bonusPrice > 0) { WorksheetRow Row28_5 = sheet.Table.Rows.Add(); cell = Row28_5.Cells.Add(); cell.StyleID = "s70"; cell = Row28_5.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Bonuses; cell.Index = 4; Row28_5.Cells.Add("-" + CatalogService.GetStringPrice(bonusPrice), DataType.String, "s70"); } // ----------------------------------------------- WorksheetRow Row29 = sheet.Table.Rows.Add(); cell = Row29.Cells.Add(); cell.StyleID = "s70"; cell = Row29.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_ShippingPrice; cell.Index = 4; Row29.Cells.Add( "+" + CatalogService.GetStringPrice(order.ShippingCost, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); if (taxedItems.Count > 0) { foreach (OrderTax tax in taxedItems) { WorksheetRow Row = sheet.Table.Rows.Add(); cell = Row.Cells.Add(); cell.StyleID = "s70"; cell = Row.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = (tax.TaxShowInPrice ? Resource.Core_TaxServices_Include_Tax + " " : "") + tax.TaxName + ":"; cell.Index = 4; Row.Cells.Add( (tax.TaxShowInPrice ? "" : "+") + CatalogService.GetStringPrice(tax.TaxSum, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); } } else { WorksheetRow Row30a = sheet.Table.Rows.Add(); cell = Row30a.Cells.Add(); cell.StyleID = "s70"; cell = Row30a.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_Taxes; cell.Index = 4; Row30a.Cells.Add( "+" + CatalogService.GetStringPrice(0, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); } // ----------------------------------------------- if (order.PaymentCost > 0) { WorksheetRow Row291 = sheet.Table.Rows.Add(); cell = Row291.Cells.Add(); cell.StyleID = "s70"; cell = Row291.Cells.Add(); cell.StyleID = "s70"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_PaymentExtracharge; cell.Index = 4; Row291.Cells.Add( "+" + CatalogService.GetStringPrice(order.PaymentCost, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s70"); } // ----------------------------------------------- WorksheetRow Row30 = sheet.Table.Rows.Add(); cell = Row30.Cells.Add(); cell.StyleID = "s70"; cell = Row30.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Admin_ViewOrder_TotalPrice; cell.Index = 4; cell = Row30.Cells.Add( CatalogService.GetStringPrice(order.Sum, order.OrderCurrency.CurrencyValue, order.OrderCurrency.CurrencyCode), DataType.String, "s135"); // ----------------------------------------------- WorksheetRow Row31 = sheet.Table.Rows.Add(); cell = Row31.Cells.Add(); cell.StyleID = "s135"; cell.Data.Type = DataType.String; cell.Data.Text = Resource.Client_PrintOrder_YourComment; cell.MergeAcross = 4; // ----------------------------------------------- WorksheetRow Row32 = sheet.Table.Rows.Add(); cell = Row32.Cells.Add(); cell.StyleID = "s141"; cell.Data.Type = DataType.String; cell.Data.Text = order.CustomerComment; cell.MergeAcross = 4; cell.MergeDown = 1; // ----------------------------------------------- // Options // ----------------------------------------------- sheet.Options.Selected = true; sheet.Options.ProtectObjects = false; sheet.Options.ProtectScenarios = false; sheet.Options.PageSetup.Header.Margin = 0.3F; sheet.Options.PageSetup.Footer.Margin = 0.3F; sheet.Options.PageSetup.PageMargins.Bottom = 0.75F; sheet.Options.PageSetup.PageMargins.Left = 0.7F; sheet.Options.PageSetup.PageMargins.Right = 0.7F; sheet.Options.PageSetup.PageMargins.Top = 0.75F; sheet.Options.Print.PaperSizeIndex = 9; sheet.Options.Print.VerticalResolution = 0; sheet.Options.Print.ValidPrinterInfo = true; }
private void GenerateWorksheet(WorksheetCollection sheets, DateTime dttungay, DateTime dtdenngay, DataTable dt, ProgressBar progressBar) { Worksheet sheet = sheets.Add("TKDoanhThu"); sheet.Table.DefaultRowHeight = 15F; sheet.Table.ExpandedColumnCount = 10; sheet.Table.ExpandedRowCount = 8 + dt.Rows.Count; sheet.Table.FullColumns = 1; sheet.Table.FullRows = 1; WorksheetColumn column0 = sheet.Table.Columns.Add(); column0.Width = 24; column0.StyleID = "s16"; WorksheetColumn column1 = sheet.Table.Columns.Add(); column1.Width = 96; column1.StyleID = "s16"; sheet.Table.Columns.Add(84); sheet.Table.Columns.Add(81); sheet.Table.Columns.Add(65); sheet.Table.Columns.Add(96); WorksheetColumn column6 = sheet.Table.Columns.Add(); column6.Width = 97; column6.Span = 1; WorksheetColumn column7 = sheet.Table.Columns.Add(); column7.Index = 9; column7.Width = 75; sheet.Table.Columns.Add(144); Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); // ----------------------------------------------- #region Add header WorksheetRow Row0 = sheet.Table.Rows.Add(); Row0.Height = 23; WorksheetCell cell; cell = Row0.Cells.Add(); cell.StyleID = "s110"; cell.Data.Type = DataType.String; cell.Data.Text = "TỔNG HỢP DOANH THU"; cell.MergeAcross = 8; Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); // ----------------------------------------------- WorksheetRow Row1 = sheet.Table.Rows.Add(); cell = Row1.Cells.Add(); cell.StyleID = "s111"; cell.Data.Type = DataType.String; cell.Data.Text = "(Từ ngày " + dttungay.ToString("dd/MM/yyyy") + " đến ngày " + dtdenngay.ToString("dd/MM/yyyy") + ")"; cell.MergeAcross = 8; Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); // ----------------------------------------------- WorksheetRow Row2 = sheet.Table.Rows.Add(); Row2.AutoFitHeight = false; cell = Row2.Cells.Add(); cell.StyleID = "s112"; cell.Data.Type = DataType.String; cell.Data.Text = "Đơn vị tính: VNĐ"; cell.MergeAcross = 8; Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); // ----------------------------------------------- WorksheetRow Row3 = sheet.Table.Rows.Add(); Row3.Cells.Add("Stt", DataType.String, "s19"); Row3.Cells.Add("Ngày xuất hóa đơn", DataType.String, "s19"); Row3.Cells.Add("Tên khách hàng", DataType.String, "s19"); Row3.Cells.Add("Tổng tiền nhập hàng/Hóa đơn", DataType.String, "s19"); Row3.Cells.Add("Tổng tiền/Hóa đơn", DataType.String, "s19"); Row3.Cells.Add("Lợi nhuận/Hóa đơn", DataType.String, "s19"); Row3.Cells.Add("Tiền đã thanh toán", DataType.String, "s19"); Row3.Cells.Add("Chiết khấu/Hóa đơn", DataType.String, "s19"); Row3.Cells.Add("Còn nợ/Hóa đơn", DataType.String, "s19"); Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); #endregion // ----------------------------------------------- #region Add Content for (int i = 0; i < dt.Rows.Count; i++) { Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); WorksheetRow row = sheet.Table.Rows.Add(); row.Cells.Add((i + 1).ToString(), DataType.Number, "s113"); DateTime dttemp = Convert.ToDateTime(dt.Rows[i]["ngaytao"].ToString().Trim()); row.Cells.Add(dttemp.ToString("dd/MM/yyyy HH:mm:ss").Trim(), DataType.String, "s114"); row.Cells.Add(dt.Rows[i]["tenkh"].ToString().Trim(), DataType.String, "s114"); row.Cells.Add(dt.Rows[i]["gianhap"].ToString().Trim(), DataType.Number, "s115"); row.Cells.Add(dt.Rows[i]["tongtien"].ToString().Trim(), DataType.Number, "s115"); cell = row.Cells.Add(); cell.StyleID = "s115"; cell.Data.Type = DataType.Number; cell.Formula = "=RC[-1]-RC[-2]-RC[2]"; row.Cells.Add(dt.Rows[i]["tiendathanhtoan"].ToString().Trim(), DataType.Number, "s115"); row.Cells.Add(dt.Rows[i]["chietkhau"].ToString().Trim(), DataType.Number, "s115"); cell = row.Cells.Add(); cell.StyleID = "s115"; cell.Data.Type = DataType.Number; cell.Formula = "=RC[-4]-RC[-2]-RC[-1]"; } #endregion // ----------------------------------------------- #region Add Tính toán (Sum) Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); WorksheetRow Row8 = sheet.Table.Rows.Add(); Row8.AutoFitHeight = false; cell = Row8.Cells.Add(); cell.StyleID = "m92103616"; cell.Data.Type = DataType.String; cell.Data.Text = "Tổng doanh thu"; cell.MergeAcross = 7; cell = Row8.Cells.Add(); cell.StyleID = "s116"; cell.Data.Type = DataType.Number; cell.Formula = "=SUM(R[-" + (dt.Rows.Count).ToString() + "]C[-4]:R[-1]C[-4])"; // ----------------------------------------------- Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); WorksheetRow Row9 = sheet.Table.Rows.Add(); Row9.AutoFitHeight = false; cell = Row9.Cells.Add(); cell.StyleID = "m92103676"; cell.Data.Type = DataType.String; cell.Data.Text = "Tổng lợi nhuận"; cell.MergeAcross = 7; cell = Row9.Cells.Add(); cell.StyleID = "s117"; cell.Data.Type = DataType.Number; cell.Formula = "=SUM(R[-" + (dt.Rows.Count + 1).ToString() + "]C[-3]:R[-2]C[-3])"; // ----------------------------------------------- Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); WorksheetRow Row10 = sheet.Table.Rows.Add(); Row10.AutoFitHeight = false; cell = Row10.Cells.Add(); cell.StyleID = "m92103636"; cell.Data.Type = DataType.String; cell.Data.Text = "Tổng tiền đã thanh toán"; cell.MergeAcross = 7; cell = Row10.Cells.Add(); cell.StyleID = "s118"; cell.Data.Type = DataType.Number; cell.Formula = "=SUM(R[-" + (dt.Rows.Count + 2).ToString() + "]C[-2]:R[-3]C[-2]) + SUM(R[-" + (dt.Rows.Count + 2).ToString() + "]C[-1]:R[-3]C[-1])"; // ----------------------------------------------- Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); WorksheetRow Row11 = sheet.Table.Rows.Add(); Row11.AutoFitHeight = false; cell = Row11.Cells.Add(); cell.StyleID = "m92103656"; cell.Data.Type = DataType.String; cell.Data.Text = "Tổng tiền còn nợ"; cell.MergeAcross = 7; cell = Row11.Cells.Add(); cell.StyleID = "s119"; cell.Data.Type = DataType.Number; cell.Formula = "=SUM(R[-" + (dt.Rows.Count + 3).ToString() + "]C:R[-4]C)"; #endregion // ----------------------------------------------- // Options // ----------------------------------------------- sheet.Options.Selected = true; sheet.Options.ProtectObjects = false; sheet.Options.ProtectScenarios = false; sheet.Options.PageSetup.Header.Margin = 0.3F; sheet.Options.PageSetup.Footer.Margin = 0.3F; sheet.Options.PageSetup.PageMargins.Bottom = 0.75F; sheet.Options.PageSetup.PageMargins.Left = 0.7F; sheet.Options.PageSetup.PageMargins.Right = 0.7F; sheet.Options.PageSetup.PageMargins.Top = 0.75F; sheet.Options.Print.HorizontalResolution = 300; sheet.Options.Print.VerticalResolution = 0; sheet.Options.Print.ValidPrinterInfo = true; Application.DoEvents(); progressBar.PerformStep(); Application.DoEvents(); }
/// <summary> /// Formatiere das Dokument /// </summary> /// <param name="sheets"></param> private void GenerateWorksheetAbhackeListe(WorksheetCollection sheets) { Worksheet sheet = sheets.Add(this._SheetName + " vom " + DateTime.Now.ToLongDateString()); this._Sheet = sheet; sheet.Table.StyleID = "ta1"; WorksheetColumn column0 = sheet.Table.Columns.Add(58); WorksheetColumn column1 = sheet.Table.Columns.Add(60); WorksheetColumn column2 = sheet.Table.Columns.Add(48); WorksheetColumn column3 = sheet.Table.Columns.Add(); column3.Width = 120; WorksheetColumn column44 = sheet.Table.Columns.Add(); column44.Width = 80; WorksheetColumn column4 = sheet.Table.Columns.Add(); column4.Width = 64; WorksheetColumn column7 = sheet.Table.Columns.Add(); column7.Width = 20; WorksheetColumn column8 = sheet.Table.Columns.Add(); column8.Width = 20; WorksheetColumn column9 = sheet.Table.Columns.Add(); column9.Width = 20; WorksheetColumn column10 = sheet.Table.Columns.Add(); column10.Width = 20; WorksheetColumn column11 = sheet.Table.Columns.Add(); column11.Width = 20; WorksheetColumn column12 = sheet.Table.Columns.Add(); column12.Width = 20; WorksheetColumn column13 = sheet.Table.Columns.Add(); column13.Width = 170; // Generierung Daten Zellen WorksheetRow Row0 = sheet.Table.Rows.Add(); Row0.Height = 18; Row0.AutoFitHeight = true; WorksheetCell cell; cell = Row0.Cells.Add(); cell.StyleID = "ce1"; cell.Data.Type = DataType.String; cell.Data.Text = "Ausweis Nummer"; cell.MergeDown = 1; cell = Row0.Cells.Add(); cell.StyleID = "ce1"; cell.Data.Type = DataType.String; cell.Data.Text = "Personen Haushalt"; cell.MergeDown = 1; cell = Row0.Cells.Add(); cell.StyleID = "ce1"; cell.Data.Type = DataType.String; cell.Data.Text = "Gruppe"; cell.MergeDown = 1; cell = Row0.Cells.Add(); cell.StyleID = "ce1"; cell.Data.Type = DataType.String; cell.Data.Text = "Name"; cell.MergeDown = 1; cell = Row0.Cells.Add(); cell.StyleID = "ce1"; cell.Data.Type = DataType.String; cell.Data.Text = "Ort"; cell.MergeDown = 1; cell = Row0.Cells.Add(); cell.StyleID = "ce6"; cell.Data.Type = DataType.String; cell.Data.Text = "Ausweis gültig bis"; cell.MergeDown = 1; cell = Row0.Cells.Add(); cell.StyleID = "ce8"; cell.Data.Type = DataType.String; cell.Data.Text = "Wochen"; cell.MergeAcross = 5; cell = Row0.Cells.Add(); cell.StyleID = "ce9"; cell.Data.Type = DataType.String; cell.Data.Text = "Bemerkungen"; cell.MergeDown = 1; cell = Row0.Cells.Add(); cell.Index = 1024; // ----------------------------------------------- WorksheetRow Row1 = sheet.Table.Rows.Add(); Row1.Height = 12; Row1.AutoFitHeight = false; // Hole Kalenderwochen, für die nächsten 6 Wochen aus Klasse: CalendarWeekDays, und // schreibe diese in das DataSheet for (int rows = 1; rows <= 6; rows++) { Row1.Cells.Add(); } CalendarWeekDays cal = new CalendarWeekDays(); List <int> weekList = new List <int>(); weekList = cal.NumberOfWeek(DateTime.Now); for (int week = 0; week <= 5; week++) { Row1.Cells.Add(weekList[week].ToString(), DataType.Number, "ce10"); } cell.Index = 1016; // ----------------------------------------------- // Optionen // ----------------------------------------------- sheet.Options.ProtectObjects = false; sheet.Options.ProtectScenarios = false; }
/// <summary> /// Sets the width of the columns in the exported document. /// /// </summary> /// /// <remarks> /// If the contents of the current cell are wider than the /// contents of previous cells in the same column, then the column's width /// is expanded to accomodate the widest cell. /// /// </remarks> private void SetColumnWidth(WorksheetColumn wsCol, WorksheetCell cell) { string str = cell.Value != null ? cell.Value.ToString() : string.Empty; if (str == string.Empty) return; double a = !str.Contains(Environment.NewLine) ? this.GetWidthOfText(str) : this.GetWidestLineLength(str); if (cell.CellFormat.Font.Height > 0 && cell.CellFormat.Font.Height != 240) a *= (double)cell.CellFormat.Font.Height / 240.0; if (cell.CellFormat.Font.Bold == ExcelDefaultableBoolean.True) a *= 1.1; double num = (double)(int)Math.Ceiling(a); if (num <= (double)wsCol.Width) return; wsCol.Width = (int)num; }