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
}
