第1篇:asp.net 将数据导出到excel中,并设置其格式
-----------------------------------------------------------
添加引用:Microsoft Excel 11.0 Object Library ;
添加:using Microsoft.Office.Interop.Excel;
一、打开Excel文件============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Open(@"E:/aaa.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
- excel1.Visible = true;
二、新建Excel对象============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);
- worksheet1.Activate();//激活sheet1表
- excel1.Visible = true;
三、新建Excel表============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- Worksheet worksheet1 =(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheets[1], 1, Type.Missing);
- excel1.Visible = true;
四、重命名Excel表名============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"或1];
- worksheet1.Name = "工作计划表";
- excel1.Visible = true;
五、设置或修改Excel表单元格内容========================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- worksheet1.Cells[1, 1] = "姓名";
- worksheet1.Cells[1, 2] = "性别";
- excel1.Visible = true;
六、设置Excel表行宽和列高===========================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- worksheet1.Columns.ColumnWidth = 20;//全局行宽
- worksheet1.Columns.RowHeight = 20;//全局列高
- Range range1 = (Range) worksheet1.Cells[2, 1];
- range1.Columns.ColumnWidth = 40;//单元格行宽
- range1.Columns.RowHeight = 40;//单元格列高
- excel1.Visible = true;
七、设置Excel表单元格边框===========================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- Range range1 = (Range)worksheet1.Cells[2, 2];
- range1.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Red);
- range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
- range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
- range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
- range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
- //也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);
- range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle = XlLineStyle.xlContinuous;//斜杠
- range1.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle = XlLineStyle.xlContinuous;//反斜杠
- range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).Color = System.Drawing.ColorTranslator.ToOle(Color.Gold);
- excel1.Visible = true;
八、Excel表块操作============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- Range range1 = worksheet1.get_Range("A2", "E8");//选择操作块
- range1.Font.Bold = true;//设置黑体
- range1.Font.Size = 18;//设置字体大小
- range1.Font.Name = "仿宋";//设置字体
- range1.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Blue);//设置字体颜色
- range1.HorizontalAlignment = XlHAlign.xlHAlignCenter;//设置水平对齐方式
- range1.VerticalAlignment = XlVAlign.xlVAlignCenter;//设置垂直对齐方式
- range1.Value2 = "123/r/n456";
- range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
- range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
- range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
- range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
- //也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);
- range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;//块内竖线
- range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;//块内横线
- excel1.Visible = true;
九、Excel表单元格合并============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- Range range1 = worksheet1.get_Range("A2", "E8");//选择操作块
- range1.Value2 = "123/r/n456";
- excel1.Application.DisplayAlerts = false;//使合并操作不提示警告信息
- range1.Merge(false);//参数为True则为每一行合并为一个单元格
- excel1.Application.DisplayAlerts = true;
- excel1.Visible = true;
十、复制Excel表============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- worksheet1.Cells[1, 1] = "123";
- worksheet1.Copy(Type.Missing, worksheet1);
- Worksheet worksheet2 =(Worksheet)worksheet1.Next;
- //worksheet2.Name = "Sheet2";
- excel1.Visible = true;
十一、页面设置============================
- Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
- Workbook workbook1 = excel1.Workbooks.Add(true);
- excel1.Caption = "我的报表";
- Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
- worksheet1.PageSetup.PaperSize = XlPaperSize.xlPaperA3;//纸张大小
- worksheet1.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
- worksheet1.PageSetup.Orientation = XlPageOrientation.xlLandscape;//页面方向为横向
- worksheet1.PageSetup.TopMargin = excel1.CentimetersToPoints(2);//上边距为2厘米(厘米转像素)
- worksheet1.PageSetup.BottomMargin = excel1.CentimetersToPoints(2);//下边距为2厘米(厘米转像素)
- worksheet1.PageSetup.LeftMargin = excel1.CentimetersToPoints(1.5);//左边距为1.5厘米(厘米转像素)
- worksheet1.PageSetup.RightMargin = excel1.CentimetersToPoints(1.5);//右边距为1.5厘米(厘米转像素)
- worksheet1.PageSetup.HeaderMargin = excel1.CentimetersToPoints(1.2);//页眉边距为1.2厘米(厘米转像素)
- worksheet1.PageSetup.FooterMargin = excel1.CentimetersToPoints(1);//页脚边距为1厘米(厘米转像素)
- worksheet1.PageSetup.CenterHorizontally = true;//页面水平居中
- worksheet1.PageSetup.CenterVertically = false;//页面不垂直居中
- worksheet1.PageSetup.CenterFooter = "第&P页,共&N页";//中间页脚内容
- excel1.Visible = true;
- function showMiniAd(){ var vn = "tinfo"; var dataURL = "http://t.sohu.com/third/user.jsp?passport="+window._xpt+"&vn="+vn; new LinkFile(dataURL, { type: 'script', noCache: false, callBack: { variable: vn, onLoad: function(){ var data = eval("(" + vn + ")") if(data != null && data.status == 1){ var userLink = data.url; //var userLink = data.icon; $('miniAd').innerHTML = "我正在玩搜狐微博,快来“关注”我,了解我的最新动态吧。
- "+userLink+""; $('miniAd').show(); } }, onFailure: function(){} }}); } showMiniAd();
转自:http://sdshizq.blog.163.com/blog/static/109414056201052432126508/
第2篇: asp.net中将DataGrid中的数据导入到excel中,并设置其格式
----------------------------------------------------------------------------
终于完成了从datagrid 中导入excel,为了防止忘记,特意记录下来,为大家和自己提供方便。
web应用程序中主要代码如下:
- //设置DataGrid2数据源 ,并绑定(由于这一步很简单,所以略过)
- /**//*设置DataGrid2的格式为文本型,这样就解决了导入excel之后,形如“00000123”变成了“123”的问题。在这里,为了简单起见,我设置了dataGrid总的属性。也可以为每个单元格设置属性,如DataGrid2.Items[0].Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");*/
- DataGrid2.Attributes.Add("style","vnd.ms-excel.numberformat:@");
- //将DataGrid2中的数据以刘的形式写入excel文件中
- Response.Clear();
- Response.Buffer= true;
- Response.Charset="GB2312";
- Response.AppendHeader("Content-Disposition","attachment;filename=zjxx.xls");
- Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
- Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
- this.EnableViewState = false;
- System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
- System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
- System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
- this.DataGrid2.RenderControl(oHtmlTextWriter);
- Response.Write(oStringWriter.ToString());
- Response.End();
windows应用程序中如下:
- //其中zjtable中已经从数据库中读入了数据
- Excel.ApplicationClass excelApp ;
- excelApp = new Excel.ApplicationClass();
- Excel.Workbook excelBook =excelApp.Workbooks.Add(1);
- Excel.Worksheet excelSheet=(Excel.Worksheet)excelBook.Worksheets[1];
- excelApp.Visible=true;
- excelSheet.Cells[1,1]="姓名";
- excelSheet.Cells[1,2]="性别";
- excelSheet.Cells[1,3]="出生日期";
- //设置excel文件中所有的单元格为文本型
- excelSheet.Cells.NumberFormatLocal="@";
- for(int i=0;i < zjtable.Rows.Count ;i++)
- ...{
- //将zjtable中的数据导入到excel文件中
- DataRow row=zjtable.Rows[i];
- for (int j=1;j<=3;j++)
- excelSheet.Cells[i+2,j]=row[j].ToString();
- }
本文来自CSDN博客,出处:http://blog.csdn.net/tthxqttzln/archive/2006/09/18/1240044.aspx
第3篇: asp.net中将DataTable中的数据导出到excel中,并设置其格式【中间件】
----------------------------------------------------------------------------
中間包xml格式
下面是一個ExcelExporter.cs檔,我用一個物件去參考它後,用裡面的Export(DataSet, ExcelFileName)就可以跳出另存新檔的視窗!
就可以順利存成Excel檔了!
我要如何像之前的文章中所做的一樣,像加下面這一行,就可以在Excel中正常顯示!
依照我提供的Code要加在哪??我試好久摟,東塞西塞也沒用!>,<"...
this.page.Response.Write( "<style>Data { mso-number-format:/"//@/"; } </style>" );
這一段直接全部Copy就可以用了!
- using System;
- using System.Data;
- using System.Web;
- namespace ExcelExporter
- {
- public class ExcelExporter
- {
- #region Private variables
- private System.Web.UI.Page page;
- #endregion
- public ExcelExporter( System.Web.UI.Page TargetPage )
- {
- this.page = TargetPage;
- }
- public void Export( DataSet Data, string ExcelFileName )
- {
- this.page.Response.Clear();
- this.page.Response.ContentType = "application/vnd.ms-excel";
- this.page.Response.AddHeader( "Content-Disposition", "attachment; Filename=" + ExcelFileName );
- //頭
- WriteWorkbookHeader();
- //
- foreach( DataTable table in Data.Tables )
- WriteTable( table );
- //尾
- WriteWorkbookFooter();
- this.page.Response.End();
- }
- private void WriteWorkbookHeader()
- {
- this.page.Response.Write( "<?xml version=/"1.0/"?>/r/n" );
- this.page.Response.Write( "<?mso-application progid=/"Excel.Sheet/"?>/r/n" );
- this.page.Response.Write( "<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"/r/n" );
- this.page.Response.Write( "xmlns:o=/"urn:schemas-microsoft-com:office:office/"/r/n" );
- this.page.Response.Write( "xmlns:x=/"urn:schemas-microsoft-com:office:excel/"/r/n" );
- this.page.Response.Write( "xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/"/r/n" );
- this.page.Response.Write( "xmlns:html=/"http://www.w3.org/TR/REC-html40/">/r/n" );
- this.page.Response.Write( "<DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">/r/n" );
- this.page.Response.Write( "<LastAuthor>MSINC</LastAuthor>/r/n" );
- this.page.Response.Write( " <Created>" + DateTime.Now.ToString() + "</Created>/r/n" );
- this.page.Response.Write( " <Version>11.5703</Version>/r/n" );
- this.page.Response.Write( "</DocumentProperties>/r/n" );
- this.page.Response.Write( "<ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">/r/n" );
- this.page.Response.Write( " <ProtectStructure>False</ProtectStructure>/r/n" );
- this.page.Response.Write( " <ProtectWindows>False</ProtectWindows>/r/n" );
- this.page.Response.Write( "</ExcelWorkbook>/r/n" );
- this.page.Response.Write( " <Styles>/r/n" );
- this.page.Response.Write( " <Style ss:ID=/"s1/">/r/n" );
- this.page.Response.Write( " <Font ss:Bold=/"1/"/>/r/n" );
- this.page.Response.Write( " </Style>/r/n" );
- this.page.Response.Write( " </Styles>/r/n" );
- }
- private void WriteWorkbookFooter()
- {
- this.page.Response.Write( "</Workbook>/r/n" );
- }
- private void WriteTable( DataTable table )
- {
- this.page.Response.Write( "<Worksheet ss:Name='" + table.TableName + "'>/r/n" );
- this.page.Response.Write( "<Table ss:ExpandedColumnCount=/"" + table.Columns.Count + "/" ss:ExpandedRowCount=/"" + (table.Rows.Count + 1) + "/" x:FullColumns=/"1/" x:FullRows=/"1/">/r/n" );
- WriteTableHeader( table );
- WriteTableRows( table );
- this.page.Response.Write( "</Table>/r/n" );
- this.page.Response.Write( "</Worksheet>/r/n" );
- }
- // 表格[頭]
- private void WriteTableHeader( DataTable table )
- {
- foreach( DataColumn column in table.Columns )
- this.page.Response.Write( "<Column>" + column.ColumnName + "</Column>/r/n" );
- this.page.Response.Write( "<Row>/r/n" );
- foreach( DataColumn column in table.Columns )
- this.page.Response.Write( "<Cell ss:StyleID=/"s1/"><Data ss:Type=/"String/">" + column.ColumnName + "</Data></Cell>/r/n" );
- this.page.Response.Write( "</Row>/r/n" );
- }
- //
- private void WriteTableRows( DataTable table )
- {
- foreach( DataRow Row in table.Rows )
- WriteTableRow( Row );
- }
- //
- private void WriteTableRow( DataRow Row )
- {
- this.page.Response.Write( "<Row>/r/n" );
- foreach( object loop in Row.ItemArray )
- {
- this.page.Response.Write( "<Cell><Data ss:Type=/"" + GetExcelType( loop ) + "/">" );
- if( loop != null && loop != DBNull.Value )
- {
- if( loop is byte[] )
- this.page.Response.Write( "(...)" );
- else if( loop is decimal )
- {
- decimal decimalNumber = (decimal) loop;
- this.page.Response.Write( System.Web.HttpUtility.HtmlEncode(decimalNumber.ToString( "N" )) );
- }
- else if( loop is DateTime )
- {
- this.page.Response.Write( ((DateTime) loop).ToString( "yyyy-MM-dd HH:mm:ss" ) );
- }
- else
- {
- this.page.Response.Write( System.Web.HttpUtility.HtmlEncode( loop.ToString() ) );
- }
- }
- this.page.Response.Write( "</Data></Cell>/r/n" );
- }
- this.page.Response.Write( "</Row>/r/n" );
- }
- private bool IsNumber( string Value )
- {
- if( Value == "" )
- return false;
- char[] chars = Value.ToCharArray();
- foreach( char ch in chars )
- {
- if( ch != '$' && ch != '.' && ch != ',' && !char.IsNumber( ch ) )
- return false;
- }
- return true;
- }
- private string GetExcelType( object Value )
- {
- if( Value == null || Value == DBNull.Value || Value is string )
- return "String";
- // else if( Value is DateTime )
- // return "Date";
- else if( IsNumber( Value.ToString() ) )
- return "Number";
- else
- return "String";
- }
- }
- }
第4篇:asp.net用模板導出excel的類
-----------------------------------
- <3>用模板列導出excel類
- 前台設置
- <asp:Button runat="server" ID="btn_Excel2" CausesValidation="False" Text="導Excel" UseSubmitBehavior="False" CssClass="button" OnClick="btn_Excel_Click" />
- <asp:PostBackTrigger ControlID="btn_Excel2" />
- 後台類
- //public override void VerifyRenderingInServerForm(Control control)
- //{
- //}
- 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
- }