复制sheet和写xls
来源：互联网 发布：java两个日期相差年份 编辑：程序博客网 时间：2024/05/16 04:48
#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);
}
}
/// <summary>
/// 加于2011-05-20
/// </summary>
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; }
}
/// <summary>
/// 加于2011-05-20
/// </summary>
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);
}
}
/// <summary>
/// 戴 加于2011-05-20
/// </summary>
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,CTN FROM 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;
}
}
#endregion
- 复制sheet和写xls
- Delphi操作EXCEL复制Sheet到另一个XLS文件中
- [初学笔记] 把数据写进xls，分在不同的sheet，并加入列头和行头，转换为mat格式
- 复制sheet
- java之poi读和写xls
- NPOI 将一个sheet指定区域复制到另一个sheet，复制数据，样式和备注
- 使用PL/SQL的UTL_FILE写xls和csv格式文件
- jsp 导出xls,创建多个sheet
- Python写xls笔记
- Excel中Sheet复制
- python excel sheet复制
- PHPExcel sheet复制
- 自己写的POIUtil，主要解决从不同的HSSFWorkbook复制sheet
- fork()和写时复制
- vba 排序和复制指定区域到新的xls文件中
- NPOI中如何复制Sheet
- NPOI 复制Sheet（转载）
- delphi 操作excel复制sheet
- FileUpLoad显示图片
- Weka以及Matlab，LibSVM接口
- 初来乍到
- RAC环境下创建物理DATAGUARD
- js 限制输入
- 复制sheet和写xls
- C#开发wince与设备连接后的文档传送
- Oracle数据库的启动-nomount状态深入解析
- 理解 Android 上的安全性
- DD-WRT 最新版！V24 SP2
- 我今年二十五六岁
- VMware, RedHat, VirtualBOX, Ubuntu以及Fedora的安装和配置
- 将DataTable导出CSV
- 6款强大的jQuery插件 创建和加强网站布局
