public byte[] GetActiveCoinsReport() { List <TransactionModel> reportData = new TransactionService().RetrieveTransactionByStateAndCycle(TransactionState.Delivered, null) .Where(Tra => Tra.SpentDate.HasValue == false).OrderBy(Tra => Tra.Recipient.DisplayName).ThenBy(Tra => Tra.GritCoin.CoinNumber).ToList(); using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; sheet.Name = "Coins"; sheet.Range[1, 1].Text = "Owner"; sheet.Range[1, 2].Text = "Coin #"; for (int i = 1; i <= reportData.Count(); i++) { sheet.Range[i + 1, 1].Text = reportData[i - 1].Recipient.DisplayName; sheet.Range[i + 1, 2].Text = reportData[i - 1].GritCoin.CoinNumber.Value.ToString(); } sheet.AutofitColumn(1); sheet.AutofitColumn(2); IStyle rowStyle = workbook.Styles.Add("HeaderRowStyle"); rowStyle.Font.Bold = true; sheet.SetDefaultRowStyle(1, 1, rowStyle); MemoryStream memstr = new MemoryStream(); workbook.SaveAs(memstr); workbook.Close(); return(memstr.ToArray()); } }
public void StyleExcel_Sales(IWorkbook workbook, IWorksheet sheet) { IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Color = Syncfusion.Drawing.Color.FromArgb(69, 90, 100); pageHeader.Font.RGBColor = Syncfusion.Drawing.Color.White; pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Font.Color = ExcelKnownColors.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 12; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet["A1"].Text = "Báo cáo Doanh số"; sheet["A1"].CellStyle = pageHeader; sheet["A2"].Text = "Mã báo cáo"; sheet["A2"].CellStyle.Font.Bold = false; sheet["A2"].CellStyle.Font.Size = 12; sheet["A3"].Text = "Tháng"; sheet["A3"].CellStyle.Font.Bold = false; sheet["A3"].CellStyle.Font.Size = 12; sheet["A4"].Text = "Nhân viên"; sheet["A4"].CellStyle.Font.Bold = false; sheet["A4"].CellStyle.Font.Size = 12; sheet["A1:E1"].Merge(); sheet["A5"].Text = "STT"; sheet["B5"].Text = "Hiệu xe"; sheet["C5"].Text = "Số lượt sửa"; sheet["D5"].Text = "Thành tiền"; sheet["E5"].Text = "Tỉ lệ"; sheet["A5:E5"].CellStyle = tableHeader; sheet.AutofitColumn(1); sheet.UsedRange.AutofitColumns(); }
public static void AutoAlign(this IWorksheet worksheet, int startColumn, int columns) { for (int column = startColumn; column <= startColumn + columns - 1; column++) { worksheet.AutofitColumn(column); } }
public byte[] GenerateExcelReport(DateTime fromDate, DateTime toDate, string state) { List <ReportData> reportTransactions = GetReportTransactions(fromDate, toDate, state); using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; sheet.Name = "Transactions"; sheet.Range[1, 1].Text = "Awarder"; sheet.Range[1, 2].Text = "Count"; sheet.Range[1, 3].Text = "Recipient"; sheet.Range[1, 4].Text = "Reason"; for (int i = 1; i <= reportTransactions.Count(); i++) { sheet.Range[i + 1, 1].Text = reportTransactions[i - 1].Sender; sheet.Range[i + 1, 2].Text = reportTransactions[i - 1].Count.ToString(); sheet.Range[i + 1, 3].Text = reportTransactions[i - 1].Receiver; sheet.Range[i + 1, 4].Text = reportTransactions[i - 1].Comment; } sheet.AutofitColumn(1); sheet.AutofitColumn(2); sheet.AutofitColumn(3); sheet.AutofitColumn(4); IStyle rowStyle = workbook.Styles.Add("HeaderRowStyle"); rowStyle.Font.Bold = true; sheet.SetDefaultRowStyle(1, 1, rowStyle); MemoryStream memstr = new MemoryStream(); workbook.SaveAs(memstr); workbook.Close(); return(memstr.ToArray()); } }
public static void autofitColunas(IWorkbook wb, string caminho, Dictionary <string, int> linhas) { IWorksheet planilha = wb.Worksheets[0]; planilha.AutofitColumn(1); planilha.AutofitColumn(2); planilha.AutofitColumn(7); planilha.AutofitColumn(8); planilha.AutofitColumn(9); planilha.AutofitColumn(10); planilha.AutofitColumn(11); planilha.AutofitColumn(12); planilha.Range["B" + linhas["primeiraLinha"] + ":H" + linhas["linhaIrpj"]].AutofitColumns(); wb.SaveAs(caminho); }
public void StyleExcel_ThongTinHangXe(IWorkbook workbook, IWorksheet sheet) { IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Color = Syncfusion.Drawing.Color.FromArgb(69, 90, 100); pageHeader.Font.RGBColor = Syncfusion.Drawing.Color.White; pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Font.Color = ExcelKnownColors.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 12; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet["A1"].Text = "Thông tin hãng xe"; sheet["A1"].CellStyle = pageHeader; sheet["A1:B1"].Merge(); sheet["A4"].Text = "ID"; sheet["B4"].Text = "Hãng xe"; sheet["A4:B4"].CellStyle = tableHeader; sheet.AutofitColumn(1); sheet.UsedRange.AutofitColumns(); }
public static byte[] Export(InputData data) { SqlConnection con = new SqlConnection(); con.ConnectionString = ConfigurationManager.ConnectionStrings["TransDB"].ConnectionString; con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "test.sp_GetTranslation"; cmd.Parameters.Add("lang", SqlDbType.NVarChar, 10).Value = data.Lang.ToUpper(); DataSet ds = new DataSet(); SqlDataAdapter adpt = new SqlDataAdapter(); adpt.SelectCommand = cmd; adpt.Fill(ds); var excelEngine = new ExcelEngine(); var application = excelEngine.Excel; IWorkbook Workbook = application.Workbooks.Create(new[] { data.Lang }); Workbook.Version = ExcelVersion.Excel2016; IWorksheet sheet = Workbook.Worksheets[0]; sheet.ImportDataTable(ds.Tables[0], true, 1, 1); for (var i = 1; i <= sheet.Columns.Length; i++) { sheet.AutofitColumn(i); } using (var memorystream = new MemoryStream()) { Workbook.SaveAs(memorystream, ExcelSaveType.SaveAsXLS); Workbook.Close(); return(memorystream.ToArray()); } }
public void StyleExcel_LichSuKinhDoanh(IWorkbook workbook, IWorksheet sheet) { IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Color = Syncfusion.Drawing.Color.FromArgb(69, 90, 100); pageHeader.Font.RGBColor = Syncfusion.Drawing.Color.White; pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Font.Color = ExcelKnownColors.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 12; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet["A1"].Text = "Lịch sử kinh doanh"; sheet["A1"].CellStyle = pageHeader; sheet["A1:E1"].Merge(); sheet["A2"].Text = "Tháng"; sheet["A3"].Text = "Năm"; sheet["A4"].Text = "Mã hóa đơn"; sheet["B4"].Text = "Tên khách hàng"; sheet["C4"].Text = "Biển số xe"; sheet["D4"].Text = "Ngày thanh toán"; sheet["E4"].Text = "Doanh thu"; sheet["A4:E4"].CellStyle = tableHeader; sheet.AutofitColumn(1); sheet.UsedRange.AutofitColumns(); }
public void StyleExcel_LichSuNhapHang(IWorkbook workbook, IWorksheet sheet) { IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Color = Syncfusion.Drawing.Color.FromArgb(69, 90, 100); pageHeader.Font.RGBColor = Syncfusion.Drawing.Color.White; pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Font.Color = ExcelKnownColors.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 12; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet["A1"].Text = "Lịch sử nhập hàng"; sheet["A1"].CellStyle = pageHeader; sheet["A1:E1"].Merge(); sheet["A2"].Text = "Tháng"; sheet["A3"].Text = "Năm"; sheet["A4"].Text = "Tên vật tư"; sheet["B4"].Text = "Số lượng"; sheet["C4"].Text = "Đơn giá"; sheet["D4"].Text = "Ngày nhập"; sheet["E4"].Text = "Tổng chi phí"; sheet["A4:E4"].CellStyle = tableHeader; sheet.AutofitColumn(1); sheet.UsedRange.AutofitColumns(); }
public void StyleExcel_DanhSachXe(IWorkbook workbook, IWorksheet sheet) { IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Color = Syncfusion.Drawing.Color.FromArgb(69, 90, 100); pageHeader.Font.RGBColor = Syncfusion.Drawing.Color.White; pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Font.Color = ExcelKnownColors.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 12; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet["A1"].CellStyle = pageHeader; sheet["A1:F1"].Merge(); sheet["A2"].Text = "ID"; sheet["B2"].Text = "Biển số"; sheet["C2"].Text = "Hiệu xe"; sheet["D2"].Text = "Chủ xe"; sheet["E2"].Text = "Tiền nợ"; sheet["F2"].Text = "Ngày tiếp nhận"; sheet["A2:F2"].CellStyle = tableHeader; sheet.AutofitColumn(1); sheet.UsedRange.AutofitColumns(); }
public void StyleExcel_DanhSachNhaCungCap(IWorkbook workbook, IWorksheet sheet) { IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); pageHeader.Color = Syncfusion.Drawing.Color.FromArgb(69, 90, 100); pageHeader.Font.RGBColor = Syncfusion.Drawing.Color.White; pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Font.Color = ExcelKnownColors.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 12; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet["A1"].Text = "Danh sách nhà cung cấp"; sheet["A1"].CellStyle = pageHeader; sheet["A1:D1"].Merge(); sheet["A2"].Text = "ID"; sheet["B2"].Text = "Tên nhà cung cấp"; sheet["C2"].Text = "Số điện thoại"; sheet["D2"].Text = "Email"; sheet["A2:D2"].CellStyle = tableHeader; sheet.AutofitColumn(1); sheet.UsedRange.AutofitColumns(); }
public static void FormatWorksheet(IWorksheet worksheet, string title = "") { var col = 1; while (!string.IsNullOrWhiteSpace(worksheet[1, col].Value)) { worksheet[1, col].CellStyle.Font.Bold = true; worksheet.AutofitColumn(col); col++; } var row = 1; while (!string.IsNullOrWhiteSpace(worksheet[row, 1].Value)) { row++; } row++; row++; worksheet[row, 1].Value = "Information classification of this document is: RESTRICTED - Please refer R&S Security Manual for classification requirements."; worksheet[row, 1].CellStyle.Font.Size = 8; worksheet[row, 1].CellStyle.Font.Bold = true; worksheet[row, 1].CellStyle.Font.Color = ExcelKnownColors.Red; row++; worksheet[row, 1].Value = "Upon removal of this document from R&S information centre, it becomes uncontrolled."; worksheet[row, 1].CellStyle.Font.Size = 8; worksheet[row, 1].CellStyle.Font.Bold = true; worksheet[row, 1].CellStyle.Font.Color = ExcelKnownColors.Red; if (!string.IsNullOrWhiteSpace(title)) { worksheet.InsertRow(1, 2); worksheet[1, 1].Value = title; worksheet[1, 1].CellStyle.Font.Size = 16; worksheet[1, 1].CellStyle.Font.Bold = true; } }
// сохраняем в Excel private void SaveExcel_m(string PathToSave_file) { try { FileInfo fi = new FileInfo(PathToSave_file); ExcelEngine excelEngine = new ExcelEngine(); IApplication _excel = excelEngine.Excel; // Тип файла _excel.DefaultVersion = ExcelVersion.Excel97to2003; // .xls if (fi.Extension == ".xlsx") { _excel.DefaultVersion = ExcelVersion.Excel2010; // .xlsx } // Создаем книгу IWorkbook wBook = _excel.Workbooks.Create(1); // Создаем лист IWorksheet wSheet = wBook.Worksheets[0]; IStyle style = wBook.Styles.Add("FillColor"); style.ColorIndex = ExcelKnownColors.Grey_25_percent; // заполняем заголовок (1-я строка) for (int i = 0; i < dataGridView_m.Columns.Count; i++) { wSheet.Range[1, i + 1].Text = dataGridView_m.Columns[i].HeaderText; wSheet.Range[1, i + 1].CellStyle = style; wSheet.Range[1, i + 1].BorderAround(ExcelLineStyle.Thin, Color.Black); } IStyle style2 = wBook.Styles.Add("FillColor2"); style2.ColorIndex = ExcelKnownColors.Light_yellow; // Заполняем данными (начинаем заполянть со 2-й строки) for (int i = 0; i < dataGridView_m.Rows.Count; i++) { for (int j = 0; j < dataGridView_m.Columns.Count; j++) { if (j == 0) { wSheet.Range[i + 2, j + 1].Value = dataGridView_m.Rows[i].Cells[j].ToolTipText + (!string.IsNullOrEmpty(dataGridView_m.Rows[i].Cells[j].Value.ToString()) ? dataGridView_m.Rows[i].Cells[j].Value.ToString() : ""); } else { wSheet.Range[i + 2, j + 1].Value = dataGridView_m.Rows[i].Cells[j].Value.ToString(); } wSheet.Range[i + 2, j + 1].BorderAround(ExcelLineStyle.Thin, Color.Black); } } // выравниваем колонки for (int i = 0; i < dataGridView_m.Columns.Count; i++) { wSheet.AutofitColumn(i + 1); } // создаем график IChartShape chart = wSheet.Charts.Add(); chart.DataRange = wSheet.Range[1, 1, 2, dataGridView_m.Columns.Count]; chart.ChartType = ExcelChartType.Column_Clustered; chart.IsSeriesInRows = true; chart.TopRow = dataGridView_m.Rows.Count + 3; chart.LeftColumn = 1; chart.RightColumn = 20; chart.BottomRow = dataGridView_m.Rows.Count + 30; chart.ChartTitle = ""; // Сохраняем файл Excel _excel.Save(PathToSave_file); filename_load = PathToSave_file; } catch (Exception ee) { MessageBox.Show(ee.ToString(), "Ошибка сохранения файла Excel", MessageBoxButtons.OK, MessageBoxIcon.Error); } if (filename_load != "") { mk_saveExcel_ToolStripMenuItem.Enabled = true; } }
// сохраняем в Excel private void SaveExcel(string PathToSave_file) { try { FileInfo fi = new FileInfo(PathToSave_file); ExcelEngine excelEngine = new ExcelEngine(); IApplication _excel = excelEngine.Excel; // Тип файла _excel.DefaultVersion = ExcelVersion.Excel97to2003; // .xls if (fi.Extension == ".xlsx") { _excel.DefaultVersion = ExcelVersion.Excel2010; // .xlsx } // Создаем книгу IWorkbook wBook = _excel.Workbooks.Create(1); // Создаем лист IWorksheet wSheet = wBook.Worksheets[0]; IStyle style = wBook.Styles.Add("FillColor"); style.ColorIndex = ExcelKnownColors.Grey_25_percent; #region Шапка wSheet.Range[1, 1].Text = label_head_1_1_2.Text; wSheet.Range[1, 1, 2, 1].Merge(); wSheet.Range[1, 1, 2, 1].CellStyle = style; wSheet.Range[1, 1, 2, 1].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[1, 2].Text = label_head_2_2_1.Text; wSheet.Range[1, 2, 1, 3].Merge(); wSheet.Range[1, 2, 1, 3].CellStyle = style; wSheet.Range[1, 2, 1, 3].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[1, 4].Text = label_head_3_2_1.Text; wSheet.Range[1, 4, 1, 5].Merge(); wSheet.Range[1, 4, 1, 5].CellStyle = style; wSheet.Range[1, 4, 1, 5].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[1, 6].Text = label_head_4_3_1.Text; wSheet.Range[1, 6, 1, 8].Merge(); wSheet.Range[1, 6, 1, 8].CellStyle = style; wSheet.Range[1, 6, 1, 8].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[2, 2].Text = label_head_5_1_1.Text; wSheet.Range[2, 2].CellStyle = style; wSheet.Range[2, 2].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[2, 3].Text = label_head_6_1_1.Text; wSheet.Range[2, 3].CellStyle = style; wSheet.Range[2, 3].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[2, 4].Text = label_head_7_1_1.Text; wSheet.Range[2, 4].CellStyle = style; wSheet.Range[2, 4].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[2, 5].Text = label_head_8_1_1.Text; wSheet.Range[2, 5].CellStyle = style; wSheet.Range[2, 5].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[2, 6].Text = label_head_9_1_1.Text; wSheet.Range[2, 6].CellStyle = style; wSheet.Range[2, 6].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[2, 7].Text = label_head_10_1_1.Text; wSheet.Range[2, 7].CellStyle = style; wSheet.Range[2, 7].BorderAround(ExcelLineStyle.Thin, Color.Black); wSheet.Range[2, 8].Text = label_head_11_1_1.Text; wSheet.Range[2, 8].CellStyle = style; wSheet.Range[2, 8].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion IStyle style2 = wBook.Styles.Add("FillColor2"); style2.ColorIndex = ExcelKnownColors.Light_yellow; #region cnhjrb int i = 3; setRowExcel(wSheet, style, style2, i, label_col1_1.Text, "", "", "", "", "", "", ""); i++; setRowExcel(wSheet, style, style2, i, label_col1_2.Text, ToStr(numericUpDown_s_n_yk.Value), label_s_n_yk_prc.Text, ToStr(numericUpDown_s_k_yk.Value), label_s_k_yk_prc.Text, label_s_i_yk.Text, label_s_i_yk_prc_n.Text, label_s_i_yk_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_3.Text, ToStr(numericUpDown_s_n_dk.Value), label_s_n_dk_prc.Text, ToStr(numericUpDown_s_k_dk.Value), label_s_k_dk_prc.Text, label_s_i_dk.Text, label_s_i_dk_prc_n.Text, label_s_i_dk_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_4.Text, ToStr(numericUpDown_s_n_rk.Value), label_s_n_rk_prc.Text, ToStr(numericUpDown_s_k_rk.Value), label_s_k_rk_prc.Text, label_s_i_rk.Text, label_s_i_rk_prc_n.Text, label_s_i_rk_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_5.Text, ToStr(numericUpDown_s_n_fss.Value), label_s_n_fss_prc.Text, ToStr(numericUpDown_s_k_fss.Value), label_s_k_fss_prc.Text, label_s_i_fss.Text, label_s_i_fss_prc_n.Text, label_s_i_fss_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_6.Text, ToStr(numericUpDown_s_n_cfp.Value), label_s_n_cfp_prc.Text, ToStr(numericUpDown_s_k_cfp.Value), label_s_k_cfp_prc.Text, label_s_i_cfp.Text, label_s_i_cfp_prc_n.Text, label_s_i_cfp_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_7.Text, label_ss_all_n_r.Text, label_ss_all_n_prc.Text, label_ss_all_k_r.Text, label_ss_all_k_prc.Text, label_ss_all_i_r.Text, label_ss_all_i_prc_n.Text, label_ss_all_i_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_8.Text, "", "", "", "", "", "", ""); i++; setRowExcel(wSheet, style, style2, i, label_col1_9.Text, ToStr(numericUpDown_z_n_dk.Value), label_z_n_dk_prc.Text, ToStr(numericUpDown_z_k_dk.Value), label_z_k_dk_prc.Text, label_z_i_dk.Text, label_z_i_dk_prc_n.Text, label_z_i_dk_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_10.Text, ToStr(numericUpDown_z_n_kk.Value), label_z_n_kk_prc.Text, ToStr(numericUpDown_z_k_kk.Value), label_z_k_kk_prc.Text, label_z_i_kk.Text, label_z_i_kk_prc_n.Text, label_z_i_kk_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_11.Text, ToStr(numericUpDown_z_n_kz.Value), label_z_n_kz_prc.Text, ToStr(numericUpDown_z_k_kz.Value), label_z_k_kz_prc.Text, label_z_i_kz.Text, label_z_i_kz_prc_n.Text, label_z_i_kz_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_12.Text, ToStr(numericUpDown_z_n_p.Value), label_z_n_p_prc.Text, ToStr(numericUpDown_z_k_p.Value), label_z_k_p_prc.Text, label_z_i_p.Text, label_z_i_p_prc_n.Text, label_z_i_p_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_13.Text, label_zs_all_n_r.Text, label_zs_all_n_prc.Text, label_zs_all_k_r.Text, label_zs_all_k_prc.Text, label_zs_all_i_r.Text, label_zs_all_i_prc_n.Text, label_zs_all_i_prc_all.Text); i++; setRowExcel(wSheet, style, style2, i, label_col1_14.Text, label_all_n_r.Text, label_all_n_prc.Text, label_all_k_r.Text, label_all_k_prc.Text, label_all_i_r.Text, label_all_i_prc_n.Text, label_all_i_prc_all.Text); i += 2; setRowExcel(wSheet, style, style2, i, label1.Text, textBox_ss_ib_n.Text, textBox_ss_ib_k.Text, textBox_ss_ib_i.Text); i++; setRowExcel(wSheet, style, style2, i, label2.Text, textBox_sk_zk_n.Text, textBox_sk_zk_k.Text, textBox_sk_zk_i.Text); i++; setRowExcel(wSheet, style, style2, i, label3.Text, textBox_ib_sk_n.Text, textBox_ib_sk_k.Text, textBox_ib_sk_i.Text); #endregion for (i = 1; i <= 8; i++) { wSheet.AutofitColumn(i); } // Сохраняем файл Excel _excel.Save(PathToSave_file); filename_load = PathToSave_file; } catch (Exception ee) { MessageBox.Show(ee.ToString(), "Ошибка сохранения файла Excel", MessageBoxButtons.OK, MessageBoxIcon.Error); } if (filename_load != "") { saveExcelToolStripMenuItem.Enabled = true; } }
public static void CreateSurveyExcelSyncfusion2(ExcelVersion version, bool withFilter, DataTable table, string dir) { ArrayList messages = new ArrayList(); string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version))); DateTime begin = DateTime.Now; //LsiLogger.Trace("Begin of CreateSurveyExcelSyncfusion(...)"); ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = version; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; DateTime beginColumnNames = DateTime.Now; worksheet.Range["A1"].Text = "Uid"; worksheet.Range["B1"].Text = "Suid"; worksheet.Range["C1"].Text = "Survey"; worksheet.Range["D1"].Text = "Location"; worksheet.Range["E1"].Text = "Date / Time Name"; worksheet.Range["F1"].Text = "Prompt 1"; worksheet.Range["G1"].Text = "Prompt 2"; worksheet.Range["H1"].Text = "Prompt 3"; worksheet.Range["I1"].Text = "Prompt 4"; worksheet.Range["J1"].Text = "Prompt 5"; worksheet.Range["K1"].Text = "Duration (sec)"; worksheet.Range["L1"].Text = "Expired"; DateTime endColumnNames = DateTime.Now; messages.Add(string.Format("ColumnNames:\t{0}", (endColumnNames - beginColumnNames))); DateTime beginDateTimeFormat = DateTime.Now; IRanges dateTimeColl = worksheet.CreateRangesCollection(); dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]); //dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt"; dateTimeColl.NumberFormat = "mm/dd/yyyy hh:mm"; DateTime endDateTimeFormat = DateTime.Now; messages.Add(string.Format("DateTimeFormat:\t{0}", (endDateTimeFormat - beginDateTimeFormat))); //Header DateTime beginHeaderFormat = DateTime.Now; IRanges header = worksheet.CreateRangesCollection(); header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]); header.HorizontalAlignment = ExcelHAlign.HAlignCenter; header.CellStyle.Font.Bold = true; header.CellStyle.Font.Size = 12; header.RowHeight = 36.75; header.CellStyle.Color = Color.FromArgb(192, 192, 192); header.CellStyle.Locked = true; header.VerticalAlignment = ExcelVAlign.VAlignCenter; header.CellStyle.Font.FontName = "Arial"; header.CellStyle.Font.RGBColor = Color.Black; header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; DateTime endHeaderFormat = DateTime.Now; messages.Add(string.Format("HeaderFormat:\t{0}\n", (endHeaderFormat - beginHeaderFormat))); //Body DateTime beginBodyFormat = DateTime.Now; for (int i = 0; i < table.Rows.Count + 1; i++) { IRanges data = worksheet.CreateRangesCollection(); data.Add(worksheet.Range[2 + i, 1, 2 + i, table.Columns.Count]); //IRanges data = worksheet.CreateRangesCollection(); //data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]); DateTime beginLineStyle = DateTime.Now; data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; DateTime endLineStyle = DateTime.Now; messages.Add(string.Format("\tLineStyle:\t{0}", (endLineStyle - beginLineStyle))); DateTime beginDiagonalDown = DateTime.Now; data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; DateTime endDiagonalDown = DateTime.Now; messages.Add(string.Format("\tDiagonalDown:\t{0}", (endDiagonalDown - beginDiagonalDown))); DateTime beginDiagonalUp = DateTime.Now; data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; DateTime endDiagonalUp = DateTime.Now; messages.Add(string.Format("\tDiagonalUp:\t{0}", (endDiagonalUp - beginDiagonalUp))); IFont font = data.CellStyle.Font; DateTime beginFontName = DateTime.Now; //data.CellStyle.Font.FontName = "Arial"; font.FontName = "Arial"; DateTime endFontName = DateTime.Now; messages.Add(string.Format("\tFontName:\t{0}", (endFontName - beginFontName))); DateTime beginFontSize = DateTime.Now; //data.CellStyle.Font.Size = 10; font.Size = 10; DateTime endFontSize = DateTime.Now; messages.Add(string.Format("\tFontSize:\t{0}", (endFontSize - beginFontSize))); DateTime beginFontRGBColor = DateTime.Now; //data.CellStyle.Font.RGBColor = Color.Black; font.Color = ExcelKnownColors.Black; DateTime endFontRGBColor = DateTime.Now; messages.Add(string.Format("\tFontRGBColor:\t{0}", (endFontRGBColor - beginFontRGBColor))); DateTime beginVerticalAlignment = DateTime.Now; data.VerticalAlignment = ExcelVAlign.VAlignCenter; DateTime endVerticalAlignment = DateTime.Now; messages.Add(string.Format("\tVerticalAlignment:\t{0}", (endVerticalAlignment - beginVerticalAlignment))); DateTime beginFillPattern = DateTime.Now; data.CellStyle.FillPattern = ExcelPattern.Solid; DateTime endFillPattern = DateTime.Now; messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern))); DateTime beginRowHeight = DateTime.Now; //data.RowHeight = 31.50; worksheet.SetRowHeight(i + 2, 31.5); DateTime endRowHeight = DateTime.Now; messages.Add(string.Format("\tRowHeight:\t{0}", (endRowHeight - beginRowHeight))); DateTime beginColor = DateTime.Now; data.CellStyle.Color = Color.FromArgb(255, 255, 204); DateTime endColor = DateTime.Now; messages.Add(string.Format("\tColor:\t{0}", (endColor - beginColor))); } DateTime endBodyFormat = DateTime.Now; messages.Add("\t---------------------------"); messages.Add(string.Format("BodyFormat:\t{0}\n", (endBodyFormat - beginBodyFormat))); if (withFilter) { DateTime beginAutoFilters = DateTime.Now; IRanges rangesOne = worksheet.CreateRangesCollection(); rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]); worksheet.AutoFilters.FilterRange = rangesOne; DateTime endAutoFilters = DateTime.Now; messages.Add(string.Format("AutoFilters:\t{0}", (endAutoFilters - beginAutoFilters))); } DateTime beginImport = DateTime.Now; worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true); DateTime endImport = DateTime.Now; messages.Add(string.Format("Import:\t{0}", (endImport - beginImport))); DateTime beginAutofitColumn = DateTime.Now; for (int i = 1; i <= table.Columns.Count; i++) { worksheet.AutofitColumn(i); //10000-7sec. } DateTime endAutofitColumn = DateTime.Now; messages.Add(string.Format("AutofitColumn:\t{0}", (endAutofitColumn - beginAutofitColumn))); MemoryStream ms = new MemoryStream(); DateTime beginSaveAsMemoryStream = DateTime.Now; workbook.SaveAs(ms); DateTime endSaveAsMemoryStream = DateTime.Now; messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream))); //workbook.Close(); //excelEngine.ThrowNotSavedOnDestroy = false; //excelEngine.Dispose(); DateTime beginSaveFile = DateTime.Now; byte[] xlsData = ms.ToArray(); SaveFile(xlsData, filePath); DateTime endSaveFile = DateTime.Now; messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile))); //LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)"); DateTime end = DateTime.Now; messages.Add(string.Format("Total time:\t{0}", (end - begin))); LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion2(...) - {0},{1}", Path.GetFileName(filePath), (end - begin))); StringBuilder sb = new StringBuilder(); sb.AppendLine(""); foreach (string message in messages) { sb.AppendLine(message); } LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion2 steps\n{0}", sb)); }
// сохраняем в Excel private void SaveExcel(string PathToSave_file) { try { FileInfo fi = new FileInfo(PathToSave_file); ExcelEngine excelEngine = new ExcelEngine(); IApplication _excel = excelEngine.Excel; // Тип файла _excel.DefaultVersion = ExcelVersion.Excel97to2003; // .xls if (fi.Extension == ".xlsx") { _excel.DefaultVersion = ExcelVersion.Excel2010; // .xlsx } // Создаем книгу IWorkbook wBook = _excel.Workbooks.Create(1); // Создаем лист IWorksheet wSheet = wBook.Worksheets[0]; IStyle style = wBook.Styles.Add("FillColor"); style.ColorIndex = ExcelKnownColors.Grey_25_percent; int i = 1; setCellExcel(wSheet, style, i, 1, label_H.Text); i++; setCellExcel(wSheet, style, i, 1, label_size.Text); i++; setCellExcel(wSheet, style, i, 1, label_profit.Text); i++; setCellExcel(wSheet, style, i, 1, label_risk.Text); i++; setCellExcel(wSheet, style, i, 1, label_pr.Text); i++; setCellExcel(wSheet, style, i, 1, label_pr_str.Text); IStyle style2 = wBook.Styles.Add("FillColor2"); style2.ColorIndex = ExcelKnownColors.Light_yellow; i = 1; setCellExcel(wSheet, null, i, 2, numericUpDown_H.Value.ToString()); i++; setCellExcel(wSheet, null, i, 2, numericUpDown_size.Value.ToString()); i++; setCellExcel(wSheet, null, i, 2, numericUpDown_profit.Value.ToString()); i++; setCellExcel(wSheet, style2, i, 2, textBox_risk.Text); i++; setCellExcel(wSheet, style2, i, 2, textBox_pr.Text); i++; setCellExcel(wSheet, style2, i, 2, textBox_pr_str.Text); wSheet.AutofitColumn(1); wSheet.AutofitColumn(2); // Сохраняем файл Excel _excel.Save(PathToSave_file); filename_load = PathToSave_file; } catch (Exception ee) { MessageBox.Show(ee.ToString(), "Ошибка сохранения файла Excel", MessageBoxButtons.OK, MessageBoxIcon.Error); } if (filename_load != "") { saveExcelToolStripMenuItem.Enabled = true; } }
private void btnCreate_Click(object sender, System.EventArgs e) { #region Workbook Initialize //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; //Set the Default version as Excel 97to2003 if (this.excel2003RadioBtn.Checked) { application.DefaultVersion = ExcelVersion.Excel97to2003; fileName = "DataValidation.xls"; } //Set the Default version as Excel 2007 else if (this.excel2007RadioBtn.Checked) { application.DefaultVersion = ExcelVersion.Excel2007; fileName = "DataValidation.xlsx"; } //Set the Default version as Excel 2010 else if (this.excel2010RadioBtn.Checked) { application.DefaultVersion = ExcelVersion.Excel2010; fileName = "DataValidation.xlsx"; } //Set the Default version as Excel 2013 else if (this.excel2013RadioBtn.Checked) { application.DefaultVersion = ExcelVersion.Excel2013; fileName = "DataValidation.xlsx"; } //Set the Default version as Xml //A new workbook is created.[Equivalent to creating a new workbook in MS Excel] //The new workbook will have 3 worksheets IWorkbook workbook = application.Workbooks.Create(3); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; #endregion #region DataValidation #region DataValidation List //Data validation to list the values in the first cell IDataValidation validation = worksheet.Range["C7"].DataValidation; worksheet.Range["B7"].Text = "Select an item from the validation list"; validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" }; validation.PromptBoxText = "Data Validation list"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true; worksheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; worksheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; worksheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; #endregion #region DataValidation For Numbers // Data Validation for Numbers IDataValidation validation1 = worksheet.Range["C9"].DataValidation; worksheet.Range["B9"].Text = "Enter a Number to validate"; validation1.AllowType = ExcelDataType.Integer; validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation1.FirstFormula = "0"; validation1.SecondFormula = "10"; validation1.ShowErrorBox = true; validation1.ErrorBoxText = "Enter Value between 0 to 10"; validation1.ErrorBoxTitle = "ERROR"; validation1.PromptBoxText = "Data Validation using Condition for Numbers"; validation1.ShowPromptBox = true; worksheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; worksheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; worksheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; #endregion #region DataValidation for Date // Data Validation for Date IDataValidation validation2 = worksheet.Range["C11"].DataValidation; worksheet.Range["B11"].Text = "Enter the Date to validate"; validation2.AllowType = ExcelDataType.Date; validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation2.FirstDateTime = new DateTime(2003, 5, 10); validation2.SecondDateTime = new DateTime(2004, 5, 10); validation2.ShowErrorBox = true; validation2.ErrorBoxText = "Enter Value between 10/5/2003 to 10/5/2004"; validation2.ErrorBoxTitle = "ERROR"; validation2.PromptBoxText = "Data Validation using Condition for Date"; validation2.ShowPromptBox = true; worksheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; worksheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; worksheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; #endregion #region DataValidation For Text // Data Validation for TextLength IDataValidation validation3 = worksheet.Range["C13"].DataValidation; worksheet.Range["B13"].Text = "Enter the Text to validate"; validation3.AllowType = ExcelDataType.TextLength; validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation3.FirstFormula = "1"; validation3.SecondFormula = "6"; validation3.ShowErrorBox = true; validation3.ErrorBoxText = "Retype text length to 6 character"; validation3.ErrorBoxTitle = "ERROR"; validation3.PromptBoxText = "Data Validation using Condition for TextLength"; validation3.ShowPromptBox = true; worksheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; worksheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; worksheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; #endregion #region DataValidation For Time // Data Validation for Time IDataValidation validation4 = worksheet.Range["C15"].DataValidation; worksheet.Range["B15"].Text = "Enter the Time to validate"; validation4.AllowType = ExcelDataType.Time; validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation4.FirstFormula = "10"; validation4.SecondFormula = "12"; validation4.ShowErrorBox = true; validation4.ErrorBoxText = "Enter the Correct time between 10 to 12 "; validation4.ErrorBoxTitle = "ERROR"; validation4.PromptBoxText = "Data Validation using Condition for Time"; validation4.ShowPromptBox = true; worksheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; worksheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; worksheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; worksheet.Range["B2:C2"].Merge(); worksheet.Range["B2"].Text = "Simple Data validation"; worksheet.Range["B5"].Text = "Validation criteria"; worksheet.Range["C5"].Text = "Validation"; worksheet.Range["B5"].CellStyle.Font.Bold = true; worksheet.Range["C5"].CellStyle.Font.Bold = true; worksheet.Range["B2"].CellStyle.Font.Bold = true; worksheet.Range["B2"].CellStyle.Font.Size = 16; worksheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; #endregion worksheet.AutofitColumn(2); worksheet.UsedRange.AutofitRows(); #endregion #region Workbook Save and Close workbook.SaveAs(fileName); //Close the workbook. workbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); #endregion #region View the Workbook //Message box confirmation to view the created spreadsheet. if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] #if NETCORE System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName) { UseShellExecute = true }; process.Start(); #else Process.Start(fileName); #endif //Exit this.Close(); } else { // Exit this.Close(); } #endregion }
public static void CreateSurveyExcelSyncfusion0(DataTable table, string dir) { DateTime begin = DateTime.Now; ArrayList messages = new ArrayList(); string filePath = Path.Combine(dir, FileName.Replace(NameKey, "Syncfusion")); ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2010; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //GlobalStyles DateTime beginDateTimeFormat = DateTime.Now; IRanges dateTimeColl = worksheet.CreateRangesCollection(); dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]); //dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt"; dateTimeColl.NumberFormat = "mm/dd/yyyy hh:m am/pm"; messages.Add(string.Format("DateTimeFormat:\t{0}", (DateTime.Now - beginDateTimeFormat))); //Header DateTime beginHeaderFormat = DateTime.Now; IRanges header = worksheet.CreateRangesCollection(); header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]); header.VerticalAlignment = ExcelVAlign.VAlignCenter; header.HorizontalAlignment = ExcelHAlign.HAlignCenter; header.CellStyle.Font.Bold = true; header.CellStyle.Font.Size = 12; header.CellStyle.Font.FontName = "Arial"; header.CellStyle.Font.RGBColor = Color.Black; header.CellStyle.Color = Color.FromArgb(192, 192, 192); header.CellStyle.Locked = true; header.RowHeight = 36.75; header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; messages.Add(string.Format("HeaderFormat:\t{0}\n", (DateTime.Now - beginHeaderFormat))); //Body DateTime beginBodyFormat = DateTime.Now; IRanges data = worksheet.CreateRangesCollection(); data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]); data.CellStyle.Font.Size = 10; data.RowHeight = 31.50; data.CellStyle.Color = Color.FromArgb(255, 255, 204); data.VerticalAlignment = ExcelVAlign.VAlignCenter; data.CellStyle.Font.FontName = "Arial"; data.CellStyle.Font.RGBColor = Color.Black; data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin; data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; messages.Add(string.Format("BodyFormat:\t{0}\n", (DateTime.Now - beginBodyFormat))); DateTime beginAutoFilters = DateTime.Now; IRanges rangesOne = worksheet.CreateRangesCollection(); rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]); worksheet.AutoFilters.FilterRange = rangesOne; messages.Add(string.Format("AutoFilters:\t{0}", (DateTime.Now - beginAutoFilters))); DateTime beginImport = DateTime.Now; worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true); messages.Add(string.Format("Import:\t{0}", (DateTime.Now - beginImport))); DateTime beginAutofitColumn = DateTime.Now; for (int i = 1; i <= table.Columns.Count; i++) { worksheet.AutofitColumn(i); } messages.Add(string.Format("AutofitColumn:\t{0}", (DateTime.Now - beginAutofitColumn))); MemoryStream ms = new MemoryStream(); DateTime beginSaveAsMemoryStream = DateTime.Now; workbook.SaveAs(ms); DateTime endSaveAsMemoryStream = DateTime.Now; messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream))); //workbook.Close(); //excelEngine.ThrowNotSavedOnDestroy = false; //excelEngine.Dispose(); DateTime beginSaveFile = DateTime.Now; byte[] xlsData = ms.ToArray(); SaveFile(xlsData, filePath); DateTime endSaveFile = DateTime.Now; messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile))); //LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)"); DateTime end = DateTime.Now; messages.Add(string.Format("Total time:\t{0}", (end - begin))); LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion(...) - {0},{1}", Path.GetFileName(filePath), (end - begin))); StringBuilder sb = new StringBuilder(); sb.AppendLine(""); foreach (string message in messages) { sb.AppendLine(message); } LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion steps\n{0}", sb)); }
public async Task ExportToExcel(string GridModel, string gridId, int id, string process, int?userId) { ExcelExport exp = new ExcelExport(); GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel); //Clear if there are any filter columns //syncfusion bug in exporting while in filter mode obj.FilterSettings.FilteredColumns.Clear(); grid = gridId; count = 0; if (gridId == "Audits") { var audits = await _prepareService.GetAudits(); if (userId.HasValue) { audits = audits.Where(a => a.AuditorId == userId.Value || a.Inspection.InspectionSteps.Any(s => s.InspectorId == userId.Value)); } var auditManage = AuditMapper.ToAuditManageViewModel(audits); //var dataSource = auditManage.Audits.ToList(); //auditViewModel = dataSource; //var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-"); //obj.ServerExcelQueryCellInfo = QueryCellInfo; //exp.Export(obj, dataSource, "Audits " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron"); using (ExcelEngine excelEngine = new ExcelEngine()) { // Set the default application version as Excel 2016. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //var group1 = auditManage.Audits.ToList(); var allAudits = auditManage.Audits.ToList(); int i = 0; //Create a workbook with a worksheet. IWorkbook workbook = excelEngine.Excel.Workbooks.Create(audits.Select(a => a.Survey).Distinct().Count()); foreach (var survey in audits.Select(a => a.Survey).Distinct()) { IWorksheet worksheet = workbook.Worksheets[i]; DataTable tbl = new DataTable(); //fill columns tbl.Columns.Add(LocalizedStrings.GetString("AuditDate"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Schedule"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Team"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Auditor"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Auditee"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("AuditStatus"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("StandardAuditee"), typeof(string)); foreach (var surveyItem in survey.SurveyItems) { tbl.Columns.Add(surveyItem.Query, typeof(int)); } tbl.Columns.Add("Score", typeof(int)); tbl.Columns.Add(LocalizedStrings.GetString("Comment"), typeof(string)); //fill rows foreach (var audit in auditManage.Audits.Where(a => a.SurveyId == survey.Id)) { DataRow dr = null; dr = tbl.NewRow(); dr[LocalizedStrings.GetString("AuditDate")] = audit.EndDate.Value.ToShortDateString(); dr[LocalizedStrings.GetString("Schedule")] = audit.EndDate.Value.ToShortTimeString().Replace(":", "H"); dr[LocalizedStrings.GetString("Team")] = string.Join(",", audit.AuditorTeams); dr[LocalizedStrings.GetString("Auditor")] = audit.AuditorName; dr[LocalizedStrings.GetString("Auditee")] = audit.AuditeeName; dr[LocalizedStrings.GetString("AuditStatus")] = audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.HasValue ? audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.Value ? "Titulaire" : "Intérimaire" : ""; dr[LocalizedStrings.GetString("StandardAuditee")] = audit.ProcessName; int scoreTemp = 0; foreach (var surveyItem in survey.SurveyItems) { dr[surveyItem.Query] = audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.HasValue ? audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value ? 1 : 0 : -1; if (audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value == true) { scoreTemp++; } } dr["Score"] = scoreTemp; tbl.Rows.Add(dr); } worksheet.ImportDataTable(tbl, true, 2, 1); worksheet.Name = survey.Name; worksheet.UsedRange.WrapText = true; worksheet.Columns[0].AutofitColumns(); worksheet.SetColumnWidth(1, 10); IListObject table = worksheet.ListObjects.Create("tbl_" + survey.Name.Replace(" ", "_"), worksheet.UsedRange); table.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9; foreach (var surveyItem in survey.SurveyItems) { //Apply conditional formats for IsOK questionnaire items IConditionalFormats condition = worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].ConditionalFormats; IConditionalFormat condition1 = condition.AddCondition(); condition1.FormatType = ExcelCFType.CellValue; condition1.Operator = ExcelComparisonOperator.Equal; condition1.FirstFormula = "1"; condition1.BackColor = ExcelKnownColors.Green; IConditionalFormat condition2 = condition.AddCondition(); condition2.FormatType = ExcelCFType.CellValue; condition2.Operator = ExcelComparisonOperator.Equal; condition2.FirstFormula = "0"; condition2.BackColor = ExcelKnownColors.Red; worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].RowHeight = 80; worksheet.AutofitColumn(table.Columns.IndexOf(s => s.Name == surveyItem.Query)); } i++; } //worksheet.ImportData(group1, 2, 1, true); var path = Server.MapPath("~/App_Data/" + LocalizedStrings.GetString("Audits") + ".xlsx"); workbook.SaveAs(path, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010); workbook.Close(); excelEngine.Dispose(); } } if (gridId == "AuditItems") { var audits = await _prepareService.GetAudits(id); var audit = AuditMapper.ToAuditViewModel(audits.FirstOrDefault()); var dataSource = audit.AuditItems.ToList(); auditItemViewModel = dataSource; var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-"); obj.ServerExcelQueryCellInfo = QueryCellInfo; exp.Export(obj, dataSource, LocalizedStrings.GetString("Audit") + " " + process + " " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron"); } }
public static void CreateSurveyExcelSyncfusion3(ExcelVersion version, bool withFilter, DataTable table, string dir) { ArrayList messages = new ArrayList(); string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version))); DateTime begin = DateTime.Now; ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = version; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; //Header IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247)); headerStyle.Color = Color.FromArgb(239, 243, 247); //headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; //headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Font.Bold = true; headerStyle.Font.Size = 12; headerStyle.Color = Color.FromArgb(192, 192, 192); headerStyle.Locked = true; headerStyle.Font.FontName = "Arial"; headerStyle.Font.Color = ExcelKnownColors.Black; headerStyle.Borders.LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; headerStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; headerStyle.EndUpdate(); worksheet.SetDefaultRowStyle(1, headerStyle); IRanges header = worksheet.CreateRangesCollection(); header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]); header.HorizontalAlignment = ExcelHAlign.HAlignCenter; header.RowHeight = 36.75; header.VerticalAlignment = ExcelVAlign.VAlignCenter; //Body IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); workbook.SetPaletteColor(10, Color.FromArgb(255, 255, 204)); bodyStyle.Color = Color.FromArgb(255, 255, 204); bodyStyle.Font.Size = 10; bodyStyle.Font.Color = ExcelKnownColors.Black; bodyStyle.Font.FontName = "Arial"; bodyStyle.Font.Bold = false; bodyStyle.Font.Color = ExcelKnownColors.Black; bodyStyle.Borders.LineStyle = ExcelLineStyle.Thin; bodyStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; bodyStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; //bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; //bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; bodyStyle.EndUpdate(); worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle); IRanges data = worksheet.CreateRangesCollection(); data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]); data.RowHeight = 31.50; //IStyle bodyStyle = worksheet.CreateRangesCollection().CellStyle; //DateTime beginFillPattern = DateTime.Now; //data.CellStyle.FillPattern = ExcelPattern.Solid; //DateTime endFillPattern = DateTime.Now; //messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern))); //worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle); IRanges rangesOne = worksheet.CreateRangesCollection(); rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]); worksheet.AutoFilters.FilterRange = rangesOne; worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true); IRanges dateTimeColl = worksheet.CreateRangesCollection(); dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]); dateTimeColl.NumberFormat = "MM/DD/YYYY h:mm am/pm"; for (int i = 1; i <= table.Columns.Count; i++) { worksheet.AutofitColumn(i); } worksheet.SetColumnWidth(5, 18.00); MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); workbook.Close(); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); byte[] xlsData = ms.ToArray(); SaveFile(xlsData, filePath); DateTime end = DateTime.Now; messages.Add(string.Format("Total time:\t{0}", (end - begin))); LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion3(...) - {0},{1}", Path.GetFileName(filePath), (end - begin))); StringBuilder sb = new StringBuilder(); sb.AppendLine(""); foreach (string message in messages) { sb.AppendLine(message); } LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion3 steps\n{0}", sb)); }
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e) { StorageFile storageFile; if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))) { FileSavePicker savePicker = new FileSavePicker(); savePicker.SuggestedStartLocation = PickerLocationId.Desktop; savePicker.SuggestedFileName = "DataValidationSample"; if (rdBtn2003.IsChecked.Value) { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xls" }); } else { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xlsx", }); } storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; if (rdBtn2003.IsChecked.Value) { storageFile = await local.CreateFileAsync("DataValidationSample.xls", CreationCollisionOption.ReplaceExisting); } else { storageFile = await local.CreateFileAsync("DataValidationSample.xlsx", CreationCollisionOption.ReplaceExisting); } } if (storageFile == null) { return; } //Instantiate excel Engine ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; if (rdBtn2003.IsChecked.Value) { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2013; } IWorkbook workbook = application.Workbooks.Create(1); IWorksheet sheet = workbook.Worksheets[0]; //Data validation to list the values in the first cell IDataValidation validation = sheet.Range["C7"].DataValidation; sheet.Range["B7"].Text = "Select an item from the validation list"; validation.ListOfValues = new string[] { "PDF", "XlsIO", "DocIO" }; validation.PromptBoxText = "Data Validation list"; validation.IsPromptBoxVisible = true; validation.ShowPromptBox = true; sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Numbers IDataValidation validation1 = sheet.Range["C9"].DataValidation; sheet.Range["B9"].Text = "Enter a Number to validate"; validation1.AllowType = ExcelDataType.Integer; validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation1.FirstFormula = "0"; validation1.SecondFormula = "10"; validation1.ShowErrorBox = true; validation1.ErrorBoxText = "Value must be between 0 and 10"; validation1.ErrorBoxTitle = "ERROR"; validation1.PromptBoxText = "Value must be between 0 and 10"; validation1.ShowPromptBox = true; sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Date IDataValidation validation2 = sheet.Range["C11"].DataValidation; sheet.Range["B11"].Text = "Enter the Date to validate"; validation2.AllowType = ExcelDataType.Date; validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation2.FirstDateTime = new DateTime(2012, 1, 1); validation2.SecondDateTime = new DateTime(2012, 12, 31); validation2.ShowErrorBox = true; validation2.ErrorBoxText = "Value must be from 01/1/2012 to 31/12/2012"; validation2.ErrorBoxTitle = "ERROR"; validation2.PromptBoxText = "Value must be from 01/1/2012 to 31/12/2012"; validation2.ShowPromptBox = true; sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for TextLength IDataValidation validation3 = sheet.Range["C13"].DataValidation; sheet.Range["B13"].Text = "Enter the Text to validate"; validation3.AllowType = ExcelDataType.TextLength; validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation3.FirstFormula = "1"; validation3.SecondFormula = "6"; validation3.ShowErrorBox = true; validation3.ErrorBoxText = "Maximum 6 characters are allowed"; validation3.ErrorBoxTitle = "ERROR"; validation3.PromptBoxText = "Maximum 6 characters are allowed"; validation3.ShowPromptBox = true; sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; // Data Validation for Time IDataValidation validation4 = sheet.Range["C15"].DataValidation; sheet.Range["B15"].Text = "Enter the Time to validate"; validation4.AllowType = ExcelDataType.Time; validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between; validation4.FirstFormula = "10"; validation4.SecondFormula = "12"; validation4.ShowErrorBox = true; validation4.ErrorBoxText = "Time must be between 10 and 12"; validation4.ErrorBoxTitle = "ERROR"; validation4.PromptBoxText = "Time must be between 10 and 12"; validation4.ShowPromptBox = true; sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["B2:C2"].Merge(); sheet.Range["B2"].Text = "Simple Data validation"; sheet.Range["B5"].Text = "Validation criteria"; sheet.Range["C5"].Text = "Validation"; sheet.Range["B5"].CellStyle.Font.Bold = true; sheet.Range["C5"].CellStyle.Font.Bold = true; sheet.Range["B2"].CellStyle.Font.Bold = true; sheet.Range["B2"].CellStyle.Font.Size = 16; sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.AutofitColumn(2); sheet["B7"].ColumnWidth = 40.3; sheet.UsedRange.AutofitRows(); await workbook.SaveAsAsync(storageFile); workbook.Close(); excelEngine.Dispose(); MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully."); UICommand yesCmd = new UICommand("Yes"); msgDialog.Commands.Add(yesCmd); UICommand noCmd = new UICommand("No"); msgDialog.Commands.Add(noCmd); IUICommand cmd = await msgDialog.ShowAsync(); if (cmd == yesCmd) { // Launch the saved file bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile); } }
public void StyleExcel_Inventory(IWorkbook workbook, IWorksheet sheet) { // THIẾT LẬP CÁI FILE EXCEL NÓ RA SAO IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle"); IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle"); // này là tạo style 1 cái là header 1 cái là header của cái table pageHeader.Color = Syncfusion.Drawing.Color.FromArgb(69, 90, 100); pageHeader.Font.RGBColor = Syncfusion.Drawing.Color.White; pageHeader.Font.FontName = "Calibri"; pageHeader.Font.Size = 18; pageHeader.Font.Bold = true; pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; // thiết lập cho header (copy) tableHeader.Font.Color = ExcelKnownColors.Black; tableHeader.Font.Bold = true; tableHeader.Font.Size = 12; tableHeader.Font.FontName = "Calibri"; tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter; tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter; tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; // cho table (copy) //Apply style to the header sheet["A1"].Text = "Báo cáo tồn"; sheet["A1"].CellStyle = pageHeader; sheet["A2"].Text = "Mã báo cáo"; sheet["A2"].CellStyle.Font.Bold = false; sheet["A2"].CellStyle.Font.Size = 12; sheet["A3"].Text = "Tháng"; sheet["A3"].CellStyle.Font.Bold = false; sheet["A3"].CellStyle.Font.Size = 12; sheet["A4"].Text = "Nhân viên"; sheet["A4"].CellStyle.Font.Bold = false; sheet["A4"].CellStyle.Font.Size = 12; // này là gán dữ liệu vào mấy cái ô sheet["A1:E1"].Merge(); // này là hợp nhất mấy cái ô từ đâu đến đâu sheet["A5"].Text = "STT"; sheet["B5"].Text = "Vật tư phụ tùng"; sheet["C5"].Text = "Tồn đầu"; sheet["D5"].Text = "Phát sinh"; sheet["E5"].Text = "Tồn cuối"; sheet["A5:E5"].CellStyle = tableHeader; // này cũng là gán chữ sheet.AutofitColumn(1); sheet.UsedRange.AutofitColumns(); // copy }