public static void ImportArrayToExcel(string fileName, object[] array) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Xlsx; //Reads input Excel stream as a workbook IWorkbook workbook = application.Workbooks.Open(fileName);//正在打开的excel不能被访问写操作 IWorksheet sheet = workbook.Worksheets[0]; ////Preparing first array with different data types //object[] expenseArray = new object[14] //{"Paul Pogba", 469.00d, 263.00d, 131.00d, 139.00d, 474.00d, 253.00d, 467.00d, 142.00d, 417.00d, 324.00d, 328.00d, 497.00d, "=SUM(B11:M11)"}; ////Inserting a new row by formatting as a previous row. //sheet.InsertRow(firstRow, firstRow, ExcelInsertOptions.FormatAsBefore); //Import Peter's expenses and fill it horizontally sheet.ImportArray(array, sheet.Rows.Length + 1, 1, false);//最后一行追加 ////Preparing second array with double data type //double[] expensesOnDec = new double[6] //{179.00d, 298.00d, 484.00d, 145.00d, 20.00d, 497.00d}; ////Modify the December month's expenses and import it vertically //sheet.ImportArray(expensesOnDec, 6, 13, true); //Save the file in the given path Stream excelStream = File.Create(fileName); workbook.SaveAs(excelStream); excelStream.Dispose(); } }
public void PrintInventoryReport(InventoryReport inventoryReport) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; // Copy StyleExcel_Inventory(workbook, worksheet); // này tự m tạo 1 cái hàm khác như trên worksheet["B2"].Text = inventoryReport.IdReport.ToString(); worksheet["B3"].Text = inventoryReport.ReportDate.ToString(); worksheet["B4"].Text = inventoryReport.UserName; //gán dữ liệu vô mấy cái ô trên int i = 6; foreach (var item in inventoryReport.ListInventory) { Object[] list = new object[] { item.STT.ToString(), item.Supplies_Name, item.TonDau, item.PhatSinh , item.TonCuoi }; worksheet.InsertRow(i, 1, ExcelInsertOptions.FormatDefault); worksheet.ImportArray(list, i, 1, false); i++; } // tạo từng dòng rồi load cái list vô worksheet.Columns[1].ColumnWidth = 30; worksheet.Columns[2].ColumnWidth = 20; worksheet.Columns[3].ColumnWidth = 20; worksheet.Columns[4].ColumnWidth = 20; // này là set chiều rộng column SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.ShowDialog(); if (!string.IsNullOrEmpty(saveFileDialog1.FileName)) { Stream excelStream; application.Application.IgnoreSheetNameException = false; if (File.Exists(Path.GetFullPath(saveFileDialog1.FileName))) { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName)); } else { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName + ".xlsx")); } workbook.SaveAs(excelStream); excelStream.Dispose(); } // này là save. copy y chang vô } }
public void PrintSalesReport(SalesReport salesReport) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; StyleExcel_Sales(workbook, worksheet); worksheet["B2"].Text = salesReport.IdReport.ToString(); worksheet["B3"].Text = salesReport.ReportDate.ToString(); worksheet["B4"].Text = salesReport.UserName; int i = 6; foreach (var item in salesReport.ListSales) { Object[] list = new object[] { item.STT.ToString(), item.CarBrand_Name, item.AmountOfTurn, item.TotalMoney , item.Rate }; worksheet.InsertRow(i, 1, ExcelInsertOptions.FormatDefault); worksheet.ImportArray(list, i, 1, false); i++; } worksheet["D" + (i + 1)].Text = "Tổng doanh thu: " + salesReport.TotalMoney; worksheet["D" + (i + 1) + ":" + "E" + (i + 1)].Merge(); worksheet.Columns[1].ColumnWidth = 30; worksheet.Columns[2].ColumnWidth = 20; worksheet.Columns[3].ColumnWidth = 20; worksheet.Columns[4].ColumnWidth = 20; SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.ShowDialog(); if (!string.IsNullOrEmpty(saveFileDialog1.FileName)) { Stream excelStream; application.Application.IgnoreSheetNameException = false; if (File.Exists(Path.GetFullPath(saveFileDialog1.FileName))) { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName)); } else { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName + ".xlsx")); } workbook.SaveAs(excelStream); excelStream.Dispose(); } } }
static void Main(string[] args) { try { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //Reads input Excel stream as a workbook IWorkbook workbook = application.Workbooks.Open(File.OpenRead(Path.GetFullPath(@"../../../Expenses.xlsx"))); IWorksheet sheet = workbook.Worksheets[0]; //Preparing first array with different data types object[] expenseArray = new object[14] { "Paul Pogba", 469.00d, 263.00d, 131.00d, 139.00d, 474.00d, 253.00d, 467.00d, 142.00d, 417.00d, 324.00d, 328.00d, 497.00d, "=SUM(B11:M11)" }; //Inserting a new row by formatting as a previous row. sheet.InsertRow(11, 1, ExcelInsertOptions.FormatAsBefore); //Import Peter's expenses and fill it horizontally sheet.ImportArray(expenseArray, 11, 1, false); //Preparing second array with double data type double[] expensesOnDec = new double[6] { 179.00d, 298.00d, 484.00d, 145.00d, 20.00d, 497.00d }; //Modify the December month's expenses and import it vertically sheet.ImportArray(expensesOnDec, 6, 13, true); //Save the file in the given path Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx")); workbook.SaveAs(excelStream); excelStream.Dispose(); } } catch (Exception e) { Console.WriteLine("Unexpected Exception:" + e.Message); } }
public void XuatDanhSachXe(ObservableCollection <ListCar> ListCar, string TrangThai) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; StyleExcel_DanhSachXe(workbook, worksheet); worksheet["A1"].Text = "Danh sách xe (" + TrangThai + ")"; int i = 3; foreach (var item in ListCar) { Object[] list = new object[] { item.CarReception.Reception_Id , item.CarReception.LicensePlate , item.CarReception.CAR_BRAND.CarBrand_Name , item.CarReception.CUSTOMER.Customer_Name , item.CarReception.Debt.ToString(), item.CarReception.ReceptionDate.ToString("dd/MM/yyyy") }; worksheet.InsertRow(i, 1, ExcelInsertOptions.FormatDefault); worksheet.ImportArray(list, i, 1, false); i++; } // tạo từng dòng rồi load cái list vô worksheet.Columns[1].ColumnWidth = 20; worksheet.Columns[2].ColumnWidth = 20; worksheet.Columns[3].ColumnWidth = 20; worksheet.Columns[4].ColumnWidth = 20; // này là set chiều rộng column SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.ShowDialog(); if (!string.IsNullOrEmpty(saveFileDialog1.FileName)) { Stream excelStream; application.Application.IgnoreSheetNameException = false; if (File.Exists(Path.GetFullPath(saveFileDialog1.FileName))) { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName)); } else { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName + ".xlsx")); } workbook.SaveAs(excelStream); excelStream.Dispose(); } // này là save. copy y chang vô } }
public void XuatLichSuNhapHang(ObservableCollection <ImportTemp> ListImport) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; StyleExcel_LichSuNhapHang(workbook, worksheet); worksheet["B2"].Text = ListImport[0].ImportGoods_Date.Month.ToString(); worksheet["B3"].Text = ListImport[0].ImportGoods_Date.Year.ToString(); int i = 5; foreach (var item in ListImport) { Object[] list = new object[] { item.Supplies_Name , item.ImportInfo.Amount.ToString() , item.ImportInfo.Price.ToString() , item.ImportGoods_Date.Date.ToString("dd/MM/yyyy") , item.ImportInfo.TotalMoney.ToString() }; worksheet.InsertRow(i, 1, ExcelInsertOptions.FormatDefault); worksheet.ImportArray(list, i, 1, false); i++; } // tạo từng dòng rồi load cái list vô worksheet.Columns[1].ColumnWidth = 20; worksheet.Columns[2].ColumnWidth = 20; worksheet.Columns[3].ColumnWidth = 20; worksheet.Columns[4].ColumnWidth = 20; // này là set chiều rộng column SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.ShowDialog(); if (!string.IsNullOrEmpty(saveFileDialog1.FileName)) { Stream excelStream; application.Application.IgnoreSheetNameException = false; if (File.Exists(Path.GetFullPath(saveFileDialog1.FileName))) { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName)); } else { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName + ".xlsx")); } workbook.SaveAs(excelStream); excelStream.Dispose(); } // này là save. copy y chang vô } }
public void XuatDanhSachNhaCungCap(ObservableCollection <SUPPLIER> ListSupplier) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; StyleExcel_DanhSachNhaCungCap(workbook, worksheet); int i = 3; foreach (var item in ListSupplier) { Object[] list = new object[] { item.Supplier_Id.ToString() , item.Supplier_Name , item.Supplier_Phone , item.Supplier_Email }; worksheet.InsertRow(i, 1, ExcelInsertOptions.FormatDefault); worksheet.ImportArray(list, i, 1, false); i++; } // tạo từng dòng rồi load cái list vô worksheet.Columns[1].ColumnWidth = 20; worksheet.Columns[2].ColumnWidth = 20; worksheet.Columns[3].ColumnWidth = 30; // này là set chiều rộng column SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.ShowDialog(); if (!string.IsNullOrEmpty(saveFileDialog1.FileName)) { Stream excelStream; application.Application.IgnoreSheetNameException = false; if (File.Exists(Path.GetFullPath(saveFileDialog1.FileName))) { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName)); } else { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName + ".xlsx")); } workbook.SaveAs(excelStream); excelStream.Dispose(); } // này là save. copy y chang vô } }
public void Print_DanhSachVatTu(ObservableCollection <SUPPLIES> ListSupplies) { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; StyleExcel_DanhSachVatTu(workbook, worksheet); int i = 5; foreach (var item in ListSupplies) { Object[] list = new object[] { item.Supplies_Id.ToString(), item.Supplies_Name, item.Supplies_Price, item.Supplies_Amount }; worksheet.InsertRow(i, 1, ExcelInsertOptions.FormatDefault); worksheet.ImportArray(list, i, 1, false); i++; } worksheet.Columns[0].ColumnWidth = 10; worksheet.Columns[1].ColumnWidth = 40; worksheet.Columns[2].ColumnWidth = 20; worksheet.Columns[3].ColumnWidth = 20; SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveFileDialog1.FilterIndex = 2; saveFileDialog1.RestoreDirectory = true; saveFileDialog1.ShowDialog(); if (!string.IsNullOrEmpty(saveFileDialog1.FileName)) { Stream excelStream; application.Application.IgnoreSheetNameException = false; if (File.Exists(Path.GetFullPath(saveFileDialog1.FileName))) { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName)); } else { excelStream = File.Create(Path.GetFullPath(saveFileDialog1.FileName + ".xlsx")); } workbook.SaveAs(excelStream); excelStream.Dispose(); } } }
private void btnExcel_Click(object sender, EventArgs e) { using (SaveFileDialog saveFile = new SaveFileDialog()) { saveFile.Filter = "Excel |*.xlsx;"; if (saveFile.ShowDialog() == DialogResult.OK) { using (ExcelEngine excelEngine = new ExcelEngine()) { //Initialize Application IApplication application = excelEngine.Excel; //Set default version for application application.DefaultVersion = ExcelVersion.Excel2013; //Create a new workbook IWorkbook workbook = application.Workbooks.Create(1); //Accessing first worksheet in the workbook IWorksheet worksheet = workbook.Worksheets[0]; string[] header = typeof(FieldResult).GetProperties() .Select(p => Attribute.IsDefined(p, typeof(DescriptionAttribute)) ? (Attribute.GetCustomAttribute(p, typeof(DescriptionAttribute)) as DescriptionAttribute).Description : p.Name ).ToArray(); header = header.Where((p, idx) => idx >= 0).ToArray(); //Import data from DataGridView control worksheet.ImportArray(header, 1, 1, false); worksheet.ImportData(fieldResults, 2, 1, false); MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); //Save the workbook using (FileStream sw = new FileStream(saveFile.FileName, FileMode.Create, FileAccess.Write)) { stream.Position = 0; stream.CopyTo(sw); } } } } }
public ActionResult Generate(FileGenViewModel model) { LFile document = docRepo.LFiles.Where(p => p.LFile_ID == model.DocID).FirstOrDefault(); List <Struct> structs = structRepo.Structs.Where(s => s.LFile_ID == model.DocID).ToList(); List <Document> document_out = new List <Document>(); // creating the document output list List <string> supra_duplicated; //getting filter values List <Elements> filter = model.FiltValues; List <VPercent> filtPerc = new List <VPercent>(); if (filter != null) { foreach (var fl in filter) { if (Convert.ToInt32(fl.Percent) < 100) { VPercent val = new VPercent { Name = fl.Name, Value = fl.Val, Counter = (int)Math.Truncate(Convert.ToDecimal((Convert.ToInt32(fl.Percent) * 0.01) * (model.NDocs * model.NBatch))) }; filtPerc.Add(val); } } } int itnum = 0; Level2 = 0; //iterating over batch number for (int l = 0; l < model.NBatch; l++) { supra_duplicated = new List <string>(); for (int i = 0; i < model.NDocs; i++) { // iterating over the number of documents int order_line = 0; itnum++; Document new_doc = new Document(); List <Dictionary <string, string> > doc = new List <Dictionary <string, string> >(); new_doc.Doc = doc; List <string> duplicated = new List <string>(); foreach (var st in structs) { // checking the structs List <StructField> structfields = structFieldRepo.StructFields.Where(s => s.StructID == st.ID).OrderBy(k => k.Field_Order).ToList(); Dictionary <string, string> str_formed = new Dictionary <string, string>(); foreach (var m in structfields) { str_formed.Add(m.Field.Field_Name, "");// filling the created structure with the document Struc-Fields } ; if (st.Order_In_Doc == 1) // if structure is document header { if (str_formed.ContainsKey("RECORD TYPE")) { str_formed["RECORD TYPE"] = "H"; } if (model.FiltValues != null) { foreach (var par in model.FiltValues) { // searching in the params from input if (par.Type == "text") { if (str_formed.ContainsKey(par.Name)) { str_formed[par.Name] = par.Val; } } else { int value1 = Convert.ToInt32(par.Val); DataField namefilter = datafieldRepo.DataFields.Where(m => m.ID == value1).FirstOrDefault(); if ((namefilter != null) && (str_formed.ContainsKey(namefilter.Field.Field_Name))) { // if param is in header is assigned the incoming value str_formed[namefilter.Field.Field_Name] = par.Name; } } } // FillFromDBLinked(ref str_formed); } FillFromDB(ref str_formed, itnum, ref duplicated); UpdCounters(ref filtPerc); UpdFilter(ref filter, ref filtPerc, ref supra_duplicated); new_doc.Doc.Add(str_formed);// adding the header to the document } else if ((st.Order_In_Doc < document.Num_Struct) && (st.Order_In_Doc > 1) && st.Multiple) {// if struct is detail if (model.NDets == 0) { model.NDets = 1; } var detnumb = model.NDets; Random rnd = new Random(); if (model.Max) { detnumb = rnd.Next(1, model.NDets); } for (int j = 0; j < detnumb; j++) // iterating over the number of details { Dictionary <string, string> new_detail = new Dictionary <string, string>(str_formed); if (new_detail.ContainsKey("RECORD TYPE")) { new_detail["RECORD TYPE"] = "D"; } if (new_detail.ContainsKey("ORDER LINE NO")) { order_line++; new_detail["ORDER LINE NO"] = order_line.ToString(); } if (new_doc.Doc.Count() > 0) { // if there is already a header var header = new_doc.Doc[0]; foreach (var word in str_formed) { // checking header and filling detail with header values if (header.ContainsKey(word.Key) && (new_detail[word.Key] == "")) { new_detail[word.Key] = header[word.Key]; } } } if (model.FiltValues != null) { foreach (var par in model.FiltValues) { // searching in the params from input if (str_formed.ContainsKey(par.Name)) { // if param in detail is assigned the incoming value new_detail[par.Name] = par.Val; } } } // FillFromDBLinked(ref new_detail); FillFromDB(ref new_detail, itnum, ref supra_duplicated); new_doc.Doc.Add(new_detail);// adding the new detail to the document } } else { // if struct is Comment Dictionary <string, string> new_comment = new Dictionary <string, string>(str_formed); if (new_comment.ContainsKey("RECORD TYPE")) { new_comment["RECORD TYPE"] = "C"; } if (new_comment.ContainsKey("ORDER LINE NO")) { new_comment["ORDER LINE NO"] = order_line.ToString(); } if (new_doc.Doc.Count() > 0) { // if there is already a header var header = new_doc.Doc[0]; foreach (var word in str_formed) { // checking header and filling comment with header values if (header.ContainsKey(word.Key) && (new_comment[word.Key] == "")) { new_comment[word.Key] = header[word.Key]; } } } if (new_doc.Doc.Count() > 1) { // if there is already a detail var detail = new_doc.Doc[1]; foreach (var word in str_formed) { // checking header and filling comment with header values if (detail.ContainsKey(word.Key) && (new_comment[word.Key] == "")) { new_comment[word.Key] = detail[word.Key]; } } } if (model.FiltValues != null) { foreach (var par in model.FiltValues) { // searching in the params from input if (str_formed.ContainsKey(par.Name)) { // if param in detail is assigned the incoming value new_comment[par.Name] = par.Val; } } } // FillFromDBLinked(ref new_comment); FillFromDB(ref new_comment, itnum, ref duplicated); new_doc.Doc.Add(new_comment);// adding the new detail to the document } } document_out.Add(new_doc); } supra_duplicated = null; } // end of document iteration // generating excel int counter = 0; //excel generation 2 using (ExcelEngine excelEngine = new ExcelEngine()) { //Set the default application version as Excel 2016. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013; //Create a workbook with a worksheet. IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); //Access first worksheet from the workbook instance. IWorksheet worksheet = workbook.Worksheets[0]; int rownum = 0; int detnum = 0; foreach (var docm in document_out) { counter++; string cell = ""; foreach (var row in docm.Doc) { string[] list = new string[row.Count()]; int aux = 0; if ((row.ContainsKey("RECORD TYPE") && (row["RECORD TYPE"] != "D") && (detnum > 0))) { detnum = 0; } if (detnum < 1) { foreach (var field in row) { list[aux] = field.Key; aux++; } rownum++; worksheet.ImportArray(list, rownum, 1, false); aux = 0; } if ((row.ContainsKey("RECORD TYPE") && (row["RECORD TYPE"] == "D"))) { detnum++; } else { detnum = 0; } foreach (var field in row) { list[aux] = field.Value; aux++; } rownum++; cell = "M" + rownum; if (list.Count() > 12) { string test_elem = list[12]; if (test_elem.Substring(0, 1) == "0") { string mask = ""; for (int k = 0; k < test_elem.Length; k++) { mask += "0"; } worksheet.Range[cell].NumberFormat = mask; } else { worksheet.Range[cell].NumberFormat = "0"; } } worksheet.ImportArray(list, rownum, 1, false); worksheet.AutofitRow(rownum); } //rownum ++2; } string fileName = ""; if (model.FileName != null) { fileName = "C:\\tkfile\\" + model.FileName + counter + ".xlsx"; } else { fileName = "C:\\tkfile\\" + counter + ".xlsx"; } workbook.SaveAs(fileName); workbook.Close(); excelEngine.Dispose(); } //end of excel generation 2 return(PartialView("_GenViewSuccPartial")); }