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