#region 下载
private static HSSFWorkbook hssfworkbook;
protected void btnDownload_Click(object sender, EventArgs e)
{
ComMethod.setMsg(0, "", lblMessage);
try
{
DownLoadExcel(Response);
}
catch (Exception ex)
{
ComMethod.setMsg(1, "生成报表发生异常：（" + ex.Message + "）", lblMessage);
}
}
///
/// 加于2011-05-20
///
public void DownLoadExcel(HttpResponse res)
{
string defaultFileName = DateTime.Now.ToString("yyyyMMdd") + "HEARD1ETDPINKING.xls";
try
{
if (GV1.Rows.Count > 0)
{
res.ContentType = "application/vnd.ms-excel";
res.AddHeader("Content-Disposition", string.Format("attachment;
filename={0}", System.Web.HttpUtility.UrlEncode(defaultFileName, System.Text.Encoding.UTF8)));
res.Clear();
writeToExcel();
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
res.BinaryWrite(file.GetBuffer());
res.End();
}
}
catch (Exception ex) { throw ex;
}
}
///
/// 加于2011-05-20
///
private void writeToExcel()
{
string orgfilepath = ConfigurationManager.AppSettings["HEARD1ETDPINKING"];
//获得模版的路径
string tarfilepath = orgfilepath.Replace("HEARD", "TEMP" + DateTime.Now.ToString("mmddyyssffff"));
if (!File.Exists(orgfilepath))
{
return;
}
#region 复制模版里的sheet
FileStream file = new FileStream(orgfilepath, FileMode.Open, FileAccess.Read);
hssfworkbook = new HSSFWorkbook(file);
DataTable table = (DataTable)ViewState["AATable"];
DataView myDataView = new DataView(table);
string[] strComuns = { "Item_Code" };
table = myDataView.ToTable(true, strComuns);
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xBook;
object oMiss = Missing.Value;
System.IO.File.Copy(orgfilepath, tarfilepath, false);
System.Globalization.CultureInfo currentCultureInfo = System.Globalization.CultureInfo.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
xBook = xApp.Workbooks.Open(tarfilepath, null, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss, oMiss);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Worksheets[1];
for (int j = 0;
j < table.Rows.Count-1;
j++)
{
sheet.Copy(sheet, Type.Missing);
}
xBook.Save();
xApp.Workbooks.Close();
xApp.Quit();
#endregion
FileStream newfile = new FileStream(tarfilepath, FileMode.Open, FileAccess.Read);
hssfworkbook = new HSSFWorkbook(newfile);
for (int i = 0;
i < table.Rows.Count;
i++)
{
InputTOExcel(table.Rows[i][0].ToString(), i);
}
}
///
/// 戴 加于2011-05-20
///
private void InputTOExcel(string itemCode, int index)
{
try
{
HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.GetSheetAt(index);
//获得模版的第一个sheet
hssfworkbook.SetSheetName(index, itemCode);
//设置sheet名称
string strwhere = ViewState["sqlwhere"].ToString();
string sqlAll = @"SELECT CONVERT(nvarchar(10),CONVERT(datetime,etd),120) as 'ETD',
Item_Code ,SKU,[Size],Color,QTY,Fabric,CTNFROM dbo.TBL_ShipmentData where 1=1 and item_code='" + itemCode + "' " + strwhere + " order by color,sku";
DataTable Table1 = db.getDataTable(sqlAll);
int CTNNum = Convert.ToInt32(Table1.Rows[0]["CTN"].ToString());
DataTable addTable = new DataTable();
addTable.Columns.Add("ETD");
addTable.Columns.Add("Item_Code");
addTable.Columns.Add("SKU");
addTable.Columns.Add("Size");
addTable.Columns.Add("Color");
addTable.Columns.Add("QTY");
addTable.Columns.Add("Fabric");
addTable.Columns.Add("CTN");
addTable.Columns.Add("BoxNum");
addTable.Columns.Add("DES");
addTable.Columns.Add("Count");
for (int i = 0;
i < Table1.Rows.Count;
i++)
{
string sku = Table1.Rows[i]["sku"].ToString();
string qty = Table1.Rows[i]["qty"].ToString();
string size = Table1.Rows[i]["Size"].ToString();
string color = Table1.Rows[i]["color"].ToString();
string Fabric = Table1.Rows[i]["Fabric"].ToString();
string CTN = Table1.Rows[i]["CTN"].ToString();
if (int.Parse(qty) > int.Parse(CTN))//当数量大于每箱数时
{
float box = float.Parse(qty) / float.Parse(CTN);
//获得箱数
int yuNum = int.Parse(qty) % int.Parse(CTN);
//获得余数
string zhi = box.ToString("f2");
string zheng = zhi.Substring(0, zhi.IndexOf("."));
int oneBox = Convert.ToInt32(zheng);
DataRow row = addTable.NewRow();
row["ETD"] = Table1.Rows[i]["ETD"].ToString();
row["Item_Code"] = Table1.Rows[i]["Item_Code"].ToString();
row["SKU"] = Table1.Rows[i]["SKU"].ToString();
row["Size"] = Table1.Rows[i]["Size"].ToString();
row["Color"] = Table1.Rows[i]["Color"].ToString();
row["QTY"] = Convert.ToInt32(Table1.Rows[i]["QTY"].ToString()) - yuNum;
row["Fabric"] = Table1.Rows[i]["Fabric"].ToString();
row["BoxNum"] = oneBox;
row["DES"] = "";
row["Count"] = Convert.ToInt32(row["QTY"]);
addTable.Rows.Add(row);
if (yuNum > 0)
{
DataRow rownex = addTable.NewRow();
rownex["ETD"] = Table1.Rows[i]["ETD"].ToString();
rownex["Item_Code"] = Table1.Rows[i]["Item_Code"].ToString();
rownex["SKU"] = Table1.Rows[i]["SKU"].ToString();
rownex["Size"] = Table1.Rows[i]["Size"].ToString();
rownex["Color"] = Table1.Rows[i]["Color"].ToString();
rownex["QTY"] = yuNum.ToString();
rownex["Fabric"] = Table1.Rows[i]["Fabric"].ToString();
rownex["BoxNum"] = "1";
rownex["DES"] = "";
rownex["Count"] = Convert.ToInt32(rownex["QTY"]) * 1;
addTable.Rows.Add(rownex);
}
}
else if (int.Parse(qty) == int.Parse(CTN))
{
DataRow row = addTable.NewRow();
row["ETD"] = Table1.Rows[i]["ETD"].ToString();
row["Item_Code"] = Table1.Rows[i]["Item_Code"].ToString();
row["SKU"] = Table1.Rows[i]["SKU"].ToString();
row["Size"] = Table1.Rows[i]["Size"].ToString();
row["Color"] = Table1.Rows[i]["Color"].ToString();
row["QTY"] = Table1.Rows[i]["QTY"].ToString();
row["Fabric"] = Table1.Rows[i]["Fabric"].ToString();
row["BoxNum"] = "1";
row["DES"] = "";
row["Count"] = Convert.ToInt32(row["QTY"]) * 1;
addTable.Rows.Add(row);
}
else if (int.Parse(qty) < int.Parse(CTN))//当数量小于每箱数时
{
DataRow row = addTable.NewRow();
row["ETD"] = Table1.Rows[i]["ETD"].ToString();
row["Item_Code"] = Table1.Rows[i]["Item_Code"].ToString();
row["SKU"] = Table1.Rows[i]["SKU"].ToString();
row["Size"] = Table1.Rows[i]["Size"].ToString();
row["Color"] = Table1.Rows[i]["Color"].ToString();
row["QTY"] = Table1.Rows[i]["QTY"].ToString();
row["Fabric"] = Table1.Rows[i]["Fabric"].ToString();
row["BoxNum"] = "1";
row["DES"] = "";
row["Count"] = Convert.ToInt32(row["QTY"]) * 1;
addTable.Rows.Add(row);
}
}
if (addTable.Rows.Count > 0)
{
int rowIndex = 0;
#region 样式
CellStyle styleAll = hssfworkbook.CreateCellStyle();
styleAll.BorderBottom = CellBorderType.THIN;
styleAll.BorderLeft = CellBorderType.THIN;
styleAll.BorderRight = CellBorderType.THIN;
styleAll.BorderTop = CellBorderType.THIN;
styleAll.Alignment = HorizontalAlignment.CENTER;
//设置居中
styleAll.VerticalAlignment = VerticalAlignment.CENTER;
//垂直居中
#endregion
#region 获得该品番的所有size
string sql = "select rtrim(ltrim(size)) as 'size' from TBL_ShipmentData where item_code='" + itemCode + "'";
DataTable table = db.getDataTable(sql);
DataView myDataView = new DataView(table);
string[] strComuns = { "size" };
DataTable sizeTable = myDataView.ToTable(true, strComuns);
Row row3 = excelSheet.GetRow(2);
//获得第三行 设置SIZE
for (int k = 0;
k < sizeTable.Rows.Count;
k++)
{
row3.GetCell(6 + k).SetCellValue(sizeTable.Rows[k][0].ToString());
}
#endregion
int totalBox = 0;
//获得总箱数
int totalQty = 0;
//获得总数量
int number = 0;
int numberBox = 0;
int numberIndex = 0;
string isMerge = "";
//判断是否合并
int MergeNum = 0;
//
int identity = 0;
int starIndex = 0;
//#region
for (int i = 0;
i < addTable.Rows.Count;
i++)
{
Row row = excelSheet.CreateRow(3 + i);
//创建行
//第一列
row.CreateCell(0).SetCellValue("DV");
row.GetCell(0).CellStyle = styleAll;
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = styleAll;
row.CreateCell(2).SetCellValue(itemCode);
//设置item_code
row.GetCell(2).CellStyle = styleAll;
row.CreateCell(3).SetCellValue(addTable.Rows[i]["Fabric"].ToString());
//设置面料
row.GetCell(3).CellStyle = styleAll;
string sku = addTable.Rows[i]["sku"].ToString();
row.CreateCell(4).SetCellValue(sku.Substring(6));
//设置sku
row.GetCell(4).CellStyle = styleAll;
row.CreateCell(5).SetCellValue(addTable.Rows[i]["color"].ToString());
//设置color
row.GetCell(5).CellStyle = styleAll;
#region 设置尺码的数量
string sizeNum = sku.Substring(6).Substring(0, 2);
//获得尺码
row.CreateCell(6);
row.CreateCell(7);
row.CreateCell(8);
row.CreateCell(9);
row.CreateCell(10);
row.CreateCell(11);
row.GetCell(6).CellStyle = styleAll;
row.GetCell(7).CellStyle = styleAll;
row.GetCell(8).CellStyle = styleAll;
row.GetCell(9).CellStyle = styleAll;
row.GetCell(10).CellStyle = styleAll;
row.GetCell(11).CellStyle = styleAll;
int num = 0;
int qty = Convert.ToInt32(addTable.Rows[i]["qty"].ToString());
if (sizeNum == "01")
{
row.GetCell(6).SetCellValue(qty.ToString());
num += qty;
}
else if (sizeNum == "02")
{
row.GetCell(7).SetCellValue(qty.ToString());
num += qty;
}
else if (sizeNum == "03")
{
row.GetCell(8).SetCellValue(qty.ToString());
num += qty;
}
else if (sizeNum == "04")
{
row.GetCell(9).SetCellValue(qty.ToString());
num += qty;
}
else if (sizeNum == "05")
{
row.GetCell(10).SetCellValue(qty.ToString());
num += qty;
}
else if (sizeNum == "06")
{
row.GetCell(11).SetCellValue(qty.ToString());
num += qty;
}
#endregion
if (Convert.ToInt32(addTable.Rows[i]["BoxNum"].ToString()) > 1)
{
row.CreateCell(12).SetCellValue(CTNNum.ToString());
}
else
{
row.CreateCell(12).SetCellValue(num.ToString());
}
row.GetCell(12).CellStyle = styleAll;
row.CreateCell(13).SetCellValue(addTable.Rows[i]["BoxNum"].ToString());
row.GetCell(13).CellStyle = styleAll;
row.CreateCell(14).SetCellValue(addTable.Rows[i]["Count"].ToString());
row.GetCell(14).CellStyle = styleAll;
rowIndex = 3 + i;
int RowBoxNum = Convert.ToInt32(addTable.Rows[i]["BoxNum"].ToString());
int rowCount = Convert.ToInt32(addTable.Rows[i]["Count"].ToString());
totalBox += RowBoxNum;
//总箱数
totalQty += rowCount;
//总数量
#region 设置条数合并
//if (i < addTable.Rows.Count - 1)
//{
//int nextqty = Convert.ToInt32(addTable.Rows[i + 1]["QTY"].ToString());
//if (MergeNum == 0)
//{
//MergeNum = num + nextqty;
//}
//else
//{
//MergeNum = MergeNum + nextqty;
//}
//}
//if (MergeNum > CTNNum)
//{
//MergeNum = 0;
//identity = 0;
//starIndex = 0;
//}
//else
//{
//identity += 1;
//if (starIndex == 0)
//{
//starIndex = rowIndex;
//ViewState["starIndex"] = starIndex;
//}
//isMerge = starIndex + "," + identity;
//ViewState["isMerge"] = isMerge;
//}
//if (MergeNum > 0)
//{
//totalBox -= 1;
//}
//if (starIndex > 0)
//{
//if (rowIndex > (starIndex + hecount))
//{
//string M = ViewState["isMerge"].ToString();
//string[] MS = M.Split(',');
//hecount = Convert.ToInt32(MS[1]);
//int heStart = Convert.ToInt32(MS[0]);
//if (isMerge.Trim().Length > 0)
//{
//excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(heStart, heStart + hecount, 13, 13));
//合并箱数
//excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(heStart, heStart + hecount, 1, 1));
//合并箱列表
//}
//ViewState["isMerge"] = "";
//starIndex = 0;
//}
//}
#endregion
#region 设置箱序列号
//if (numberBox == 0)
//{
//row.GetCell(1).SetCellValue("1");
//numberIndex = 2;
//numberBox = 1;
//}
//else
//{
//int boxnum = Convert.ToInt32(addTable.Rows[i]["BoxNum"].ToString());
//if (MergeNum <= 0)
//{
//number = numberIndex + boxnum;
//到哪个箱为止
//}
//if (boxnum > 1)//当箱数大于零时
//{
//row.GetCell(1).SetCellValue(numberIndex.ToString() + "-" + number.ToString());
//number += 1;
//}
//else
//{
//row.GetCell(1).SetCellValue(numberIndex.ToString());
//}
//numberIndex = number;
//}
#endregion
}
#region 合并
int totalCnt = 0;
int iCount = addTable.Rows.Count + 3;
for (int i = 3;
i < iCount;
i++)
{
int rowqty = Convert.ToInt32(excelSheet.GetRow(i).GetCell(14).ToString());
//获得数量
if (i < iCount - 1)
{
int nextrowqty = Convert.ToInt32(excelSheet.GetRow(i + 1).GetCell(12).ToString());
//获得数量
if (MergeNum == 0)
{
MergeNum = rowqty + nextrowqty;
}
else
{
MergeNum = MergeNum + nextrowqty;
}
}
if (MergeNum > CTNNum || i == iCount - 1)
{
MergeNum = 0;
identity = 0;
starIndex = 0;
int count = Convert.ToInt32(excelSheet.GetRow(i).GetCell(13).ToString());
//获得数量
totalCnt = totalCnt + count;
}
else
{
identity += 1;
if (starIndex == 0)
starIndex = i;
isMerge = starIndex + "," + identity;
string[] MS = isMerge.Split(',');
int hecount = Convert.ToInt32(MS[1]);
int heStart = Convert.ToInt32(MS[0]);
if (isMerge.Trim().Length > 0)
{
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(heStart, heStart + hecount, 13, 13));
//合并箱数
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(heStart, heStart + hecount, 1, 1));
//合并箱列表
if (starIndex == 0)
totalCnt++;
}
}
if (numberBox == 0)
{
excelSheet.GetRow(i).GetCell(1).SetCellValue("1");
numberIndex = 2;
numberBox = 1;
}
else
{
int boxnum = Convert.ToInt32(excelSheet.GetRow(i).GetCell(13).ToString());
//获得箱
if (MergeNum <= 0)
{
number = numberIndex + boxnum;
//到哪个箱为止
}
if (boxnum > 1)//当箱数大于零时
{
excelSheet.GetRow(i).GetCell(1).SetCellValue(numberIndex.ToString() + "-" + number.ToString());
number += 1;
}
else
{
excelSheet.GetRow(i).GetCell(1).SetCellValue(numberIndex.ToString());
}
numberIndex = number;
}
}
#endregion
#region 倒数第二行
rowIndex = rowIndex + 1;
Row rowsecond = excelSheet.CreateRow(rowIndex);
rowsecond.Height = 500;
rowsecond.CreateCell(0).SetCellValue("");
rowsecond.GetCell(0).CellStyle = styleAll;
rowsecond.CreateCell(1).SetCellValue("TOTAL");
rowsecond.GetCell(1).CellStyle = styleAll;
rowsecond.CreateCell(2).SetCellValue("");
rowsecond.GetCell(2).CellStyle = styleAll;
rowsecond.CreateCell(3).SetCellValue("");
rowsecond.GetCell(3).CellStyle = styleAll;
rowsecond.CreateCell(4).SetCellValue("");
rowsecond.GetCell(4).CellStyle = styleAll;
rowsecond.CreateCell(5).SetCellValue("");
rowsecond.GetCell(5).CellStyle = styleAll;
rowsecond.CreateCell(6).SetCellValue("");
rowsecond.GetCell(6).CellStyle = styleAll;
rowsecond.CreateCell(7).SetCellValue("");
rowsecond.GetCell(7).CellStyle = styleAll;
rowsecond.CreateCell(8).SetCellValue("");
rowsecond.GetCell(8).CellStyle = styleAll;
rowsecond.CreateCell(9).SetCellValue("");
rowsecond.GetCell(9).CellStyle = styleAll;
rowsecond.CreateCell(10).SetCellValue("");
rowsecond.GetCell(10).CellStyle = styleAll;
rowsecond.CreateCell(11).SetCellValue("");
rowsecond.GetCell(11).CellStyle = styleAll;
rowsecond.CreateCell(12).SetCellValue("");
rowsecond.GetCell(12).CellStyle = styleAll;
rowsecond.CreateCell(13).SetCellValue(totalCnt.ToString());
rowsecond.GetCell(13).CellStyle = styleAll;
rowsecond.CreateCell(14).SetCellValue(totalQty.ToString());
rowsecond.GetCell(14).CellStyle = styleAll;
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 1, 12));
#endregion
rowIndex = rowIndex + 1;
#region 最后一行
Row lastrow = excelSheet.CreateRow(rowIndex);
lastrow.Height = 500;
lastrow.CreateCell(0).SetCellValue("");
lastrow.GetCell(0).CellStyle = styleAll;
lastrow.CreateCell(1).SetCellValue("MEAS:");
lastrow.GetCell(1).CellStyle = styleAll;
lastrow.CreateCell(2).SetCellValue("");
lastrow.GetCell(2).CellStyle = styleAll;
lastrow.CreateCell(3).SetCellValue("");
lastrow.GetCell(3).CellStyle = styleAll;
lastrow.CreateCell(4).SetCellValue("");
lastrow.GetCell(4).CellStyle = styleAll;
lastrow.CreateCell(5).SetCellValue("");
lastrow.GetCell(5).CellStyle = styleAll;
lastrow.CreateCell(6).SetCellValue("G.W:KGS");
lastrow.GetCell(6).CellStyle = styleAll;
lastrow.CreateCell(7).SetCellValue("");
lastrow.GetCell(7).CellStyle = styleAll;
lastrow.CreateCell(8).SetCellValue("");
lastrow.GetCell(8).CellStyle = styleAll;
lastrow.CreateCell(9).SetCellValue("");
lastrow.GetCell(9).CellStyle = styleAll;
lastrow.CreateCell(10).SetCellValue("");
lastrow.GetCell(10).CellStyle = styleAll;
lastrow.CreateCell(11).SetCellValue("N.W: KGS");
lastrow.GetCell(11).CellStyle = styleAll;
lastrow.CreateCell(12).SetCellValue("");
lastrow.GetCell(12).CellStyle = styleAll;
lastrow.CreateCell(13).SetCellValue("");
lastrow.GetCell(13).CellStyle = styleAll;
lastrow.CreateCell(14).SetCellValue("");
lastrow.GetCell(14).CellStyle = styleAll;
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 1, 5));
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 6, 11));
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 12, 14));
#endregion
//合并DV行
excelSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, addTable.Rows.Count + 3, 0, 0));
}
}
catch (Exception ex)
{
string ss = ex.Message;
throw ex;
}
【复制sheet和写xls】}
#endregion
