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