发布时间：2018-10-19编辑：佚名阅读（2119）
public void OutputExcel(DataView dv, string str)
{
#region
GC.Collect();
//ExcelOperate excelOperate = new ExcelOperate();
Application excel;// = new Application();
//int rowIndex = 4;
//int colIndex = 1;
_Workbook xBk;
_Worksheet xSt;
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(Server.MapPath("xls_template") + "//标准工时模板.xls");
xSt = (_Worksheet)xBk.ActiveSheet;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[2, 9]).Merge(Missing.Value); //横向合并
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[2, 1]).Value2 = GetDept(Session["sa_kb"].ToString()) + " " + "标准工时";
//13838
xSt.Cells[3, 1] = "成品料号:" + Session["sa001"].ToString();
xSt.Cells[3, 3] = "型号:" + Session["sa02x"].ToString();
xSt.Cells[3, 8] = "版次/版本:" + Session["banchi"].ToString();
xSt.Cells[4, 1] = "半成品料号:" + Session["sa011"].ToString();
xSt.Cells[4, 3] = "订单:" + Session["sa_NO"].ToString();
xSt.Cells[4, 8] = "IE:" + Session["sa_IE"].ToString();
for (int i = 0; i < dv.Count; i++)
{
xSt.Cells[7 + i, 1] = dv[i].Row[0].ToString();
xSt.Cells[7 + i, 2] = dv[i].Row[1].ToString();
xSt.Cells[7 + i, 3] = dv[i].Row[3].ToString();
xSt.Cells[7 + i, 4] = dv[i].Row[4].ToString();
xSt.Cells[7 + i, 5] = dv[i].Row[5].ToString();
if (dv[i].Row[2].ToString() == "1")//若是有数据变更的则着色表示
{
SetBold(xSt, xSt.Cells[7 + i, 1], xSt.Cells[7 + i, 9], 1);
}
SetHAlignCenter(xSt, xSt.Cells[7 + i, 6], xSt.Cells[7 + i, 9]);//居中
}
int rowNum = 7;
DataView dvw = GetBiao();
for (int i = 0; i < dvw.Count; i++)
{
int rowAdd = Convert.ToInt32(dvw[i].Row[1].ToString());
xSt.get_Range(xSt.Cells[rowNum, 6], xSt.Cells[rowNum + rowAdd - 1, 6]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[rowNum, 6], xSt.Cells[rowNum + rowAdd - 1, 6]).Value2 = dvw[i].Row[2].ToString();
xSt.get_Range(xSt.Cells[rowNum, 7], xSt.Cells[rowNum + rowAdd - 1, 7]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[rowNum, 7], xSt.Cells[rowNum + rowAdd - 1, 7]).Value2 = dvw[i].Row[3].ToString();
xSt.get_Range(xSt.Cells[rowNum, 8], xSt.Cells[rowNum + rowAdd - 1, 8]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[rowNum, 8], xSt.Cells[rowNum + rowAdd - 1, 8]).Value2 = dvw[i].Row[4].ToString();
xSt.get_Range(xSt.Cells[rowNum, 9], xSt.Cells[rowNum + rowAdd - 1, 9]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[rowNum, 9], xSt.Cells[rowNum + rowAdd - 1, 9]).Value2 = dvw[i].Row[6].ToString();
rowNum = rowNum + rowAdd;
}
xSt.get_Range(xSt.Cells[(int)dv.Count + 7, 1], xSt.Cells[(int)dv.Count + 7, 2]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[(int)dv.Count + 7, 1], xSt.Cells[(int)dv.Count + 7, 2]).Value2 = "间接人员";
#region
int dvc = (int)dv.Count + 7;
xSt.Cells[dvc + 1, 1] = "1";
xSt.Cells[dvc + 2, 1] = "2";
xSt.Cells[dvc + 3, 1] = "3";
xSt.Cells[dvc + 1, 2] = "修改";
xSt.Cells[dvc + 2, 2] = "调机";
xSt.Cells[dvc + 3, 2] = "备注";
string[] str1 = GetSum("1").Split(';');
xSt.Cells[dvc + 1, 3] = str1[0];
xSt.Cells[dvc + 1, 4] = str1[1];
xSt.Cells[dvc + 1, 5] = str1[2];
xSt.Cells[dvc + 1, 6] = str1[3];
string[] str2 = GetSum("2").Split(';');
xSt.Cells[dvc + 2, 3] = str2[0];
xSt.Cells[dvc + 2, 4] = str2[1];
xSt.Cells[dvc + 2, 5] = str2[2];
xSt.Cells[dvc + 2, 6] = str2[3];
string[] str3 = GetSum("3").Split(';');
xSt.Cells[dvc + 3, 3] = str3[0];
xSt.Cells[dvc + 3, 4] = str3[1];
xSt.Cells[dvc + 3, 5] = str3[2];
xSt.Cells[dvc + 3, 6] = str3[3];
#endregion
int ExRow = (int)dv.Count + 12;
// 将图片写入到Excel
xSt.get_Range(xSt.Cells[ExRow, 6], xSt.Cells[ExRow + 6, 9]).Merge(Missing.Value);
Worksheet wk = (Worksheet)xSt;
int heit = ((int)dv.Count) * 22;
heit = heit - 30;
string strpath = Session["sa010"].ToString();
if (strpath != "")
{
wk.Shapes.AddPicture(strpath, MsoTriState.msoFalse, MsoTriState.msoTrue, 455, heit, 113, 84);
}
xSt.get_Range(xSt.Cells[ExRow, 6], xSt.Cells[ExRow + 6, 9]).Value2 = "";
xSt.Cells[ExRow, 2] = "产线总人数(人):";
xSt.Cells[ExRow + 1, 2] = "产线瓶颈时间(秒):";
xSt.Cells[ExRow + 2, 2] = "产线平衡率(%):";
xSt.Cells[ExRow + 3, 2] = "产品标准工时(秒):";
xSt.Cells[ExRow + 4, 2] = "标准日产能(PCS/10H):";
//设置位置
SetHAlignCenter(xSt, xSt.Cells[ExRow, 2], xSt.Cells[ExRow, 2], "str");
SetHAlignCenter(xSt, xSt.Cells[ExRow + 1, 2], xSt.Cells[ExRow + 1, 2], "str");
SetHAlignCenter(xSt, xSt.Cells[ExRow + 2, 2], xSt.Cells[ExRow + 2, 2], "str");
SetHAlignCenter(xSt, xSt.Cells[ExRow + 3, 2], xSt.Cells[ExRow + 3, 2], "str");
SetHAlignCenter(xSt, xSt.Cells[ExRow + 4, 2], xSt.Cells[ExRow + 4, 2], "str");
//设置字体产色
#region
SetBold(xSt, xSt.Cells[ExRow, 2], xSt.Cells[ExRow, 2], "");
SetBold(xSt, xSt.Cells[ExRow + 1, 2], xSt.Cells[ExRow + 1, 2], "");
SetBold(xSt, xSt.Cells[ExRow + 2, 2], xSt.Cells[ExRow + 2, 2], "");
SetBold(xSt, xSt.Cells[ExRow + 3, 2], xSt.Cells[ExRow + 3, 2], "");
SetBold(xSt, xSt.Cells[ExRow + 4, 2], xSt.Cells[ExRow + 4, 2], "");
#endregion
xSt.get_Range(xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5]).Value2 = GetSum("4");
SetBold(xSt, xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5], "");
xSt.get_Range(xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5]).Value2 = GetSum("5");
SetBold(xSt, xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5], "");
xSt.get_Range(xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5]).Value2 = GetSum("6");
SetBold(xSt, xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5], "");
xSt.get_Range(xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5]).Value2 = GetSum("7");
SetBold(xSt, xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5], "");
xSt.get_Range(xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5]).Merge(Missing.Value);
xSt.get_Range(xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5]).Value2 = GetSum("8");
SetBold(xSt, xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5], "");
//居中
SetHAlignCenter(xSt, xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5]);
SetHAlignCenter(xSt, xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5]);
SetHAlignCenter(xSt, xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5]);
SetHAlignCenter(xSt, xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5]);
SetHAlignCenter(xSt, xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5]);
xSt.get_Range(xSt.Cells[ExRow + 5, 1], xSt.Cells[ExRow + 5, 5]).Merge(Missing.Value); //横向合并
xSt.get_Range(xSt.Cells[ExRow + 5, 1], xSt.Cells[ExRow + 5, 5]).Value2 = "注: 适用型号: ";
//
//显示效果
//
excel.Visible = false;
//xSt.Export(Server.MapPath(".")+"//"+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
string stick = DateTime.Now.Ticks.ToString();
xBk.SaveCopyAs(Server.MapPath("xls_files") + "//" + stick + ".xls");
//ds = null;
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath("xls_files") + "//" + stick + ".xls";
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，,弹出另存为窗口
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
//让浏览器显示下载信息
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回一个不能被客户端读取的流，下载
Response.ContentType = "application/ms-excel";
//把文件流下载到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
#endregion
}
0人
0人
0人
0人
