先说说导出吧,相对来说 ,导入的操作相对简单一些;、
1.首先查出需要导出的数据,查询出来的数据添加到 list列表(因为本人数据有做权限设置,查询相对比较繁琐)
var fil = QueryPageFilter.Create("ZT_YWYYB").Equal("ID", id);
var yyb = ServiceAppContext.Instance.DataBaseClassHelper.GetQueryDic(fil);
List<string> list = new List<string>();
List<string> list2 = new List<string>();
var filter = QueryPageFilter.Create("ZT_YYZDB").Equal("SSYY", id).Equal("SFDCZD", true).Equal("SFSC", false);
var table = ServiceAppContext.Instance.DataBaseClassHelper.GetQueryResultN(filter);
foreach (var item in table)
{
list.Add(item["ZDMC"].ToString());
list2.Add(item["XSM"].ToString());
}
var filter2 = QueryPageFilter.Create(yyb["SSBM"].ToString()).Equal("SFSC", false).ReturnFields(list.ToArray());
var phg = ServiceAppContext.Instance.DataBaseClassHelper.GetQueryResultN(filter2);
2. list列表导出做成excel
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
IRow frow0 = sheet.CreateRow(0);
foreach (var item in list2)
{
for (int i = 0; i < list2.Count; i++)
{
frow0.CreateCell(i).SetCellValue(list2[i]);
}
}
for (var i = 0; i < phg.Length; i++)
{
IRow frow1 = sheet.CreateRow(i + 1);
var item = phg[i];
for (var j = 0; j < item.Count; j++)
{
var mc = list[j];
frow1.CreateCell(j).SetCellValue(item[mc].ToString());
}
}
3. 自定义文件位置,关闭文件流
string saveFileName = string.Format(local + "\\{0}模板.xlsx", yyb["YYBT"]);
try
{
using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs); //写入文件
workbook.Close(); //关闭
}