下面导出excel的方法中有三个表：可分为主表——>副表——>明细表，其中按照CUST_CODE分組主表数据，再进行分组循环副表，再循环副表找出明细表，三个表的数据组成一行填充DataTable的行，每一组主表下组成的Row形成一个DataTable，调用ExportByCMR010CHG2方法，传入List<DataTable> listDT, int[] widths（行宽），代码画Excel的页面
#region 获取以及处理数据
string sql = @"SELECT CUST_CODE, INV_NO, INV_AMT, CRNCY_CODE, MASTER_NO, HOUSE_NO, ORGN_CODE, DEST_CODE,QTY,GW,
(SELECT INCO_TERMS FROM AE_AWB WHERE AWB_NO = CM_INVM.HOUSE_NO AND ROWNUM = 1) AS TERMS
FROM CM_INVM WHERE 1=1";
//unionSQL是一个方法（用来拼接条件的单号）参数：sql语句，单号数组，字段名
sql = unionSQL(sql, ids, "INV_NO");
DataSet masterData = DbHelperOra.Query(sql);
//子表
string detailSQL = @"SELECT INV_NO, CHG_CODE, INV_AMT, CRNCY_CODE FROM CM_INVD where 1=1 ";
string awbNoArr = string.Join(",", masterData.Tables[0].AsEnumerable().Select(x => "'" + x.Field<string>("INV_NO") + "'").ToArray());
detailSQL = unionSQL(detailSQL, awbNoArr, "INV_NO");
DataSet detail = DbHelperOra.Query(detailSQL);
//明细表
string detailPOSQL = @"SELECT AWB_BL, PO_NO FROM TX_INVPO where 1=1 ";
string awbNoPOArr = string.Join(",", masterData.Tables[0].AsEnumerable().Select(x => "'" + x.Field<string>("HOUSE_NO") + "'").ToArray());
detailPOSQL = unionSQL(detailPOSQL, awbNoPOArr, "AWB_BL");
DataSet detailPO = DbHelperOra.Query(detailPOSQL);
List<System.Data.DataTable> listdate = new List<System.Data.DataTable>();
//dts按照CUST_CODE分組
var dts = from t in masterData.Tables[0].AsEnumerable()
group t by new { t1 = t["CUST_CODE"] } into m
select new
{
CUST_CODE = m.Key.t1
};
//循環分組CUST_CODE
foreach (var CUST_CODE in dts)
{
List<DataRow> listnewdata = new List<DataRow>();
System.Data.DataTable rowsTable = new DataTable();
//循環主單查詢出分組主單數據
foreach (DataRow item in masterData.Tables[0].Select(" CUST_CODE='" + CUST_CODE.CUST_CODE + "'"))
{
string str805CY = "", str819CY = "", str855CY = "", str893CY = "", strCustPO = "";
decimal f805 = 0, f819 = 0, f855 = 0, f893 = 0;
#region 循環副單查詢出當前主單INV_NO對應的數據
//循環副單查詢出當前主單INV_NO對應的數據
foreach (DataRow itemD in detail.Tables[0].Select(" INV_NO='" + item["INV_NO"] + "'"))
{
if (itemD != null)
{
if (itemD["CHG_CODE"]?.ToString() == "805")
{
f805 = Convert.ToDecimal(itemD["INV_AMT"]);
str805CY = Convert.ToString(itemD["CRNCY_CODE"]);
}
if (itemD["CHG_CODE"]?.ToString() == "819")
{
f819 = Convert.ToDecimal(itemD["INV_AMT"]);
str819CY = Convert.ToString(itemD["CRNCY_CODE"]);
}
if (itemD["CHG_CODE"]?.ToString() == "855")
{
f855 = Convert.ToDecimal(itemD["INV_AMT"]);
str855CY = Convert.ToString(itemD["CRNCY_CODE"]);
}
if (itemD["CHG_CODE"]?.ToString() == "893")
{
f893 = Convert.ToDecimal(itemD["INV_AMT"]);
str893CY = Convert.ToString(itemD["CRNCY_CODE"]);
}
}
}
var yuyu = item["INV_NO"].ToString();
#endregion
//循環明細查詢出當前主單HOUSE_NO對應的數據
foreach (DataRow itemPO in detailPO.Tables[0].Select(" AWB_BL='" + item["HOUSE_NO"] + "'"))
{
if (itemPO != null)
{
if (strCustPO != "")
{
strCustPO = strCustPO + "|" + itemPO["PO_NO"];
}
}
}
//DataSet der = new DataSet();
//der.CreateDataReader();
var masterD2 = masterData.Tables[0].Clone();
masterD2.Clear();
masterD2.ImportRow(item);
#region newdata
var newdata = masterD2.AsEnumerable().Select(xx=>new
{
TYPE = " ",
HAWB_NO = item["HOUSE_NO"],
SHIP_NO = strCustPO,
TERM = item["TERMS"],
FROM = item["ORGN_CODE"],
TO = item["DEST_CODE"],
MAWB_NO = item["MASTER_NO"],
INV_NO = item["INV_NO"],
AMT = item["INV_AMT"],
CRNCY1 = item["CRNCY_CODE"],
f805 = f805,
str805CY = str805CY,
f819 = f819,
str819CY = str819CY,
f855 = f855,
str855CY = str855CY,
f893 = f893,
str893CY = str893CY,
HC = "",
CRNCY3 = "",
DTD = "",
CRNCY4 = "",
FUEL = "",
CRNCY5 = "",
WAR = "",
CRNCY6 = "",
CARTAGE = "",
CRNCY7 = "",
EDI = "",
CRNCY8 = "",
OTHER = "",
CRNCY9 = "",
FREIGHT_TTL = "",
CRNCY10 = "",
TAX = "",
DTD_NOTAX = "",
CARTON = item["QTY"],
WEIGHT = item["GW"]
}).ToList();
#endregion
//listnewdata.AddRange(newdata);
var newTable = DTToJson.ToDataTable(newdata);
if (rowsTable.Rows.Count < 1)
{
rowsTable = DTToJson.ToDataTable(newdata);
rowsTable.Clear();
}
rowsTable.ImportRow(newTable.Rows[0]);
}
//一组行
//System.Data.DataTable MT = DTToJson.ToDataTable(listnewdata);
rowsTable.TableName = title;
listdate.Add(rowsTable);
}
List<int> widthlist = new List<int>();
for (var i = 0; i < 38; i++)
{
widthlist.Add(3000);
}
MemoryStream ms = NPOIHepler.ExportByCMR010CHG2(listdate, widthlist.ToArray());//调用前一个方法
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/ms-excel", title + ".xls");
#endregion
public static MemoryStream ExportByCMR010CHG2(List<DataTable> listDT, int[] widths)
{
//先创建一个流
MemoryStream ms = new MemoryStream();
if (listDT != null && listDT.Count != 0)
{
try
{
//新建一个excel
HSSFWorkbook workbook = new HSSFWorkbook();
//excel样式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
//创建一个sheet
ISheet sheet = workbook.CreateSheet(listDT[0].TableName);
//给指定sheet的内容设置每列宽度（index从0开始，width1000相当于excel设置的列宽3.81）
for (int i = 0; i < widths.Length; i++)
{
sheet.SetColumnWidth(i, widths[i]);
}
int lisynum = -1;
foreach (DataTable dt in listDT)
{
lisynum++;
//在sheet里创建行
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(lisynum);
row1.CreateCell(0).SetCellValue("TYPE");
row1.CreateCell(1).SetCellValue("HAWB NO");
row1.CreateCell(2).SetCellValue("AWBSHIP NO");
row1.CreateCell(3).SetCellValue("TERM");
row1.CreateCell(4).SetCellValue("FROM");
row1.CreateCell(5).SetCellValue("TO");
row1.CreateCell(6).SetCellValue("MAWB NO");
row1.CreateCell(7).SetCellValue("INV#");
row1.CreateCell(8).SetCellValue("AMT");
row1.CreateCell(9).SetCellValue("CRNCY");
row1.CreateCell(10).SetCellValue("805");
row1.CreateCell(11).SetCellValue("CRNCY");
row1.CreateCell(12).SetCellValue("819");
row1.CreateCell(13).SetCellValue("CRNCY");
row1.CreateCell(14).SetCellValue("855");
row1.CreateCell(15).SetCellValue("CRNCY");
row1.CreateCell(16).SetCellValue("893");
row1.CreateCell(17).SetCellValue("CRNCY");
row1.CreateCell(18).SetCellValue("HC");
row1.CreateCell(19).SetCellValue("CRNCY");
row1.CreateCell(20).SetCellValue("DTD");
row1.CreateCell(21).SetCellValue("CRNCY");
row1.CreateCell(22).SetCellValue("FUEL");
row1.CreateCell(23).SetCellValue("CRNCY");
row1.CreateCell(24).SetCellValue("WAR");
row1.CreateCell(25).SetCellValue("CRNCY");
row1.CreateCell(26).SetCellValue("CARTAGE");
row1.CreateCell(27).SetCellValue("CRNCY");
row1.CreateCell(28).SetCellValue("EDI");
row1.CreateCell(29).SetCellValue("CRNCY");
row1.CreateCell(30).SetCellValue("OTHER");
row1.CreateCell(31).SetCellValue("CRNCY");
row1.CreateCell(32).SetCellValue("FREIGHT TTL");
row1.CreateCell(33).SetCellValue("CRNCY");
row1.CreateCell(34).SetCellValue("TAX");
row1.CreateCell(35).SetCellValue("DTD NOTAX");
row1.CreateCell(36).SetCellValue("CARTON");
row1.CreateCell(37).SetCellValue("WEIGHT");
for (var r = 0; r < dt.Rows.Count; r++)
{
var row_r = sheet.CreateRow(r+ lisynum + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
row_r.CreateCell(i).SetCellValue(dt.Rows[r][i].ToString());
}
}
lisynum = lisynum+dt.Rows.Count;
}
//写入流
workbook.Write(ms);
ms.Flush();
return ms;
}
catch (Exception ex)
{
}
}
return null;
}
//unionSQL方法（用来拼接条件的单号）
public static string unionSQL(string sql, string ids, string fielId, string OrderBy = "")
{
//超过1000个CRNO使用union拼接
StringBuilder sqlStr = new StringBuilder();
var number = ids.Split(',').Length % 1000 == 0 ? ids.Split(',').Length / 1000 : ids.Split(',').Length / 1000 + 1;
for (var i = 0; i < number; i++)
{
sqlStr.Append(number > 1 ? " select * from (" : "");
string[] noArr = ids.Split(',').Skip(1000 * i).Take(1000).ToArray();
sqlStr.Append(sql + " AND " + fielId + " IN (" + string.Join(",", noArr, 0, noArr.Length) + ")");
sqlStr.Append(OrderBy);
sqlStr.Append(number > 1 ? ")" : "");
if (i != number - 1)
{
sqlStr.Append(Environment.NewLine + "union" + Environment.NewLine);
}
}
return sqlStr.ToString();
}
