private void CreateExcelFile(ViewModelSearchKT_DONGSUA_TAUTHUYEN Searchmodel)
{
using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME))
{
helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
helper.CurrentSheetName = "Sheet1";
helper.CurrentPosition = new CellRef("A5");
helper.InsertRange("header_1");
helper.CurrentPosition = new CellRef("A6");
helper.InsertRange("header_2");
CellRangeTemplate row_tinhthanhpho = helper.CreateCellRangeTemplate("row_tinhthanhpho", new List <string> {
"stt_lama", "tinhthanhpho", "c1", "c2", "c3", "c4", "c5", "c6"
});
CellRangeTemplate row_coso = helper.CreateCellRangeTemplate("row_coso", new List <string> {
"stt_num", "tencoso", "c7", "c8", "c9", "c10", "c11", "c12"
});
CellRangeTemplate row_11 = helper.CreateCellRangeTemplate("row_11", new List <string> {
"str", "c_11", "c_12", "c_13", "c_14", "c_15", "c_16", "c_17"
});
int k = 7;
List <String> _lstMA_TTP = new List <string>();
IEnumerable <List <object> > _lstTinhTP = this.getTinhThanhPho(Searchmodel, _lstMA_TTP);
for (int i = 0; i < _lstTinhTP.Count(); i++)
{
//insert Tinh thanh pho
helper.CurrentPosition = new CellRef("A" + (k).ToString());
helper.InsertRange(row_tinhthanhpho, _lstTinhTP.ToArray()[i]);
k = k + 1;
//insert Co so
List <int> _lstCoSoID = new List <int>();
IEnumerable <List <object> > _lstCoSo = this.getCoSoByTinhTP(_lstMA_TTP[i], _lstCoSoID);
for (int j = 0; j < _lstCoSo.Count(); j++)
{
helper.CurrentPosition = new CellRef("A" + (k).ToString());
helper.InsertRange(row_coso, _lstCoSo.ToArray()[j]);
k = k + 1;
//Insert co so detail
helper.CurrentPosition = new CellRef("A" + (k).ToString());
IEnumerable <List <object> > lstDetail = this.getDetailCoSo(_lstCoSoID[j]);
helper.InsertRange(row_11, lstDetail);
k = k + lstDetail.Count();
}
helper.CurrentPosition = new CellRef("A" + (k).ToString());
helper.InsertRange("row_empty");
k = k + 1;
}
helper.DeleteSheet("Sheet3");
helper.CurrentSheetName = "Sheet1";
}
}
private void CreateExcelFile()
{
using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME))
{
helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
helper.CurrentSheetName = "DanhMuc";
//helper.CurrentPosition = new CellRef("A2");
//helper.InsertRange("row_empty");
//fill danh mục chuẩn:
Dictionary <string, IEnumerable <List <object> > > dictDanhMuc = this.getAllDanhMucChuan();
CellRangeTemplate row_tinhthanhpho = helper.CreateCellRangeTemplate("TinhThanhPho", new List <string> {
"tinhthanhpho1"
});
helper.CurrentPosition = new CellRef("A3");
helper.InsertRange(row_tinhthanhpho, dictDanhMuc["DTINHTP"]);
CellRangeTemplate row_nhomtau = helper.CreateCellRangeTemplate("NhomTau", new List <string> {
"nhomtau1"
});
helper.CurrentPosition = new CellRef("B4");
helper.InsertRange(row_nhomtau, dictDanhMuc["DNHOM_TAU"]);
CellRangeTemplate row_nghe = helper.CreateCellRangeTemplate("Nghe", new List <string> {
"nghe1"
});
helper.CurrentPosition = new CellRef("C4");
helper.InsertRange(row_nghe, dictDanhMuc["DM_NHOMNGHE"]);
helper.DeleteSheet("Sheet2");
helper.CurrentSheetName = "KT_CPUE";
}
}
public ActionResult ExportExcel(ViewModelSearchNT_TT_THITRUONG SearchModel)
{
try
{
using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME))
{
helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
helper.CurrentSheetName = "Sheet1";
helper.CurrentPosition = new CellRef("A5");
helper.InsertRange("header_1");
helper.CurrentPosition = new CellRef("A6");
helper.InsertRange("header_2");
CellRangeTemplate row_tinhthanhpho = helper.CreateCellRangeTemplate("row_tinhthanhpho", new List <string> {
"stt_lama", "tinhthanhpho", "c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9", "c10", "c11", "c12"
});
// CellRangeTemplate row_coso = helper.CreateCellRangeTemplate("row_coso", new List<string> { "stt_num", "tencoso", "c13", "c14", "c15", "c16", "c17", "c18", "c19", "c20", "c21", "c22", "c23", "c24" });
CellRangeTemplate row_11 = helper.CreateCellRangeTemplate("row_11", new List <string> {
"str", "c_23", "c_24", "c_25", "c_26", "c_27", "c_28", "c_29", "c_30", "c_31", "c_32", "c_33", "c_34", "c_35"
});
int k = 7;
List <String> _lstMA_TTP = new List <string>();
IEnumerable <List <object> > _lstTinhTP = this.getTinhThanhPho(SearchModel, _lstMA_TTP);
for (int i = 0; i < _lstTinhTP.Count(); i++)
{
//insert Tinh thanh pho
helper.CurrentPosition = new CellRef("A" + (k).ToString());
helper.InsertRange(row_tinhthanhpho, _lstTinhTP.ToArray()[i]);
k = k + 1;
//insert Co so
List <int> _lstCoSoID = new List <int>();
IEnumerable <List <object> > _lstCoSo = this.gettt_thitruongByTinhTP(_lstMA_TTP[i], _lstCoSoID);
for (int j = 0; j < _lstCoSo.Count(); j++)
{
// helper.CurrentPosition = new CellRef("A" + (k).ToString());
//helper.InsertRange(row_coso, _lstCoSo.ToArray()[j]);
//k = k + 1;
//Insert co so detail
helper.CurrentPosition = new CellRef("A" + (k).ToString());
IEnumerable <List <object> > lstDetail = this.getDetailtt_thitruong(SearchModel, _lstCoSoID[j]);
helper.InsertRange(row_11, lstDetail);
k = k + lstDetail.Count();
}
}
helper.DeleteSheet("Sheet3");
helper.CurrentSheetName = "Sheet1";
}
ViewBag.MSG_EXPORT = "Xuất file Excel thành công!";
// return File(GENERATED_FILE_NAME, "application/vnd.ms-excel", "NT_TT_THITRUONGExcel" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls");
}
catch (Exception ex)
{
ViewBag.MSG_EXPORT = "Xuất file Excel không thành công!";
}
return(RedirectToAction("Index"));
}
private void export(string tableName)
{
using (SQLiteConnection connection = createConnection())
{
List <string> columns = fetchColumns(connection, tableName);
string appFolder = Path.GetDirectoryName(Application.ExecutablePath);
string templateFileName = Path.Combine(appFolder, string.Format(@"resources\{0}.xlsx", tableName));
string exportFileName = string.Format("{0}.xlsx", tableName);
if (File.Exists(templateFileName))
{
using (ExcelHelper helper = new ExcelHelper(templateFileName, exportFileName))
{
helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
helper.CurrentSheetName = "Sheet1";
helper.InsertRange("header");
CellRangeTemplate rowTemplate = helper.CreateCellRangeTemplate("row", columns);
helper.InsertRange(rowTemplate, fetchData(connection, tableName, columns));
helper.DeleteSheet("Templates");
}
MessageBox.Show("Exported!");
}
else
{
MessageBox.Show(String.Format("Template xlsx not found ('{0}')!\nAutocreating it, please check it and retry!", templateFileName));
string basicTempateFileName = Path.Combine(appFolder, @"resources\BasicTemplate.xlsx");
using (ExcelHelper helper = new ExcelHelper(basicTempateFileName, templateFileName))
{
helper.Direction = ExcelHelper.DirectionType.LEFT_TO_RIGHT;
helper.CurrentSheetName = "Templates";
CellRangeTemplate template = helper.CreateCellRangeTemplate("header_row", new List <string>()
{
"header", "row"
});
var result =
from item in columns
select new List <object>()
{
item, string.Format("<{0}>", item)
};
helper.InsertRange(template, result);
var names = new string[] { "header", "row" };
foreach (string name in names)
{
CellRangeRef range = helper.FindDefinedNameRange(name);
//extending it to the length of the columns
CellRef end = range.End;
end.OffsetIt(new CellRef(0, columns.Count - 1));
range.End = end;
range.SheetName = "Templates";
helper.SetDefinedNameRange(name, range);
}
helper.DeleteSheet("_Templates");
}
}
}
}
