第1篇：asp.net 将数据导出到excel中，并设置其格式
-----------------------------------------------------------
添加引用：Microsoft Excel 11.0 Object Library ;
添加：using Microsoft.Office.Interop.Excel;
一、打开Excel文件＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03.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);
04.
05.excel1.Visible = true;
二、新建Excel对象＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03.Workbook workbook1 = excel1.Workbooks.Add(XlWBATemplate.xlWBATWorksheet或true);
04.
05.worksheet1.Activate();//激活sheet1表
06.
07.excel1.Visible = true;
三、新建Excel表＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03. Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05. Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
06.
07. Worksheet worksheet1 =(Worksheet)workbook1.Worksheets.Add(Type.Missing,workbook1.Worksheets[1], 1, Type.Missing);
08.
09. excel1.Visible = true;
四、重命名Excel表名＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03. Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05. Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"或1];
06.
07. worksheet1.Name = "工作计划表";
08. excel1.Visible = true;
五、设置或修改Excel表单元格内容＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03.Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05.Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
06.
07.worksheet1.Cells[1, 1] = "姓名";
08.
09.worksheet1.Cells[1, 2] = "性别";
10.
11.excel1.Visible = true;
六、设置Excel表行宽和列高＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03.Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05.Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
06.
07.worksheet1.Columns.ColumnWidth = 20;//全局行宽
08.
09.worksheet1.Columns.RowHeight = 20;//全局列高
10.
11.Range range1 = (Range) worksheet1.Cells[2, 1];
12.
13.range1.Columns.ColumnWidth = 40;//单元格行宽
14.
15.range1.Columns.RowHeight = 40;//单元格列高
16.
17.excel1.Visible = true;
七、设置Excel表单元格边框＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03.Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05.Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
06.
07.Range range1 = (Range)worksheet1.Cells[2, 2];
08.
09.range1.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Red);
10.
11.range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
12.
13.range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
14.
15.range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
16.
17.range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
18.
19.//也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);
20.
21.range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle = XlLineStyle.xlContinuous;//斜杠
22.
23.range1.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle = XlLineStyle.xlContinuous;//反斜杠
24.
25.range1.Borders.get_Item(XlBordersIndex.xlDiagonalDown).Color = System.Drawing.ColorTranslator.ToOle(Color.Gold);
26.
27.excel1.Visible = true;
八、Excel表块操作＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03. Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05. Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
06.
07. Range range1 = worksheet1.get_Range("A2", "E8");//选择操作块
08.
09. range1.Font.Bold = true;//设置黑体
10.
11. range1.Font.Size = 18;//设置字体大小
12.
13.range1.Font.Name = "仿宋";//设置字体
14.
15. range1.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Blue);//设置字体颜色
16.
17. range1.HorizontalAlignment = XlHAlign.xlHAlignCenter;//设置水平对齐方式
18.
19. range1.VerticalAlignment = XlVAlign.xlVAlignCenter;//设置垂直对齐方式
20.
21. range1.Value2 = "123\r\n456";
22.
23. range1.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = XlLineStyle.xlContinuous;
24.
25. range1.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
26.
27. range1.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
28.
29. range1.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
30.
31. //也可用后面的代码代替上面四项range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic,null);
32.
33. range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;//块内竖线
34.
35. range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;//块内横线
36.
37. excel1.Visible = true;
九、Excel表单元格合并＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03. Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05. Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
06.
07. Range range1 = worksheet1.get_Range("A2", "E8");//选择操作块
08.
09. range1.Value2 = "123\r\n456";
10.
11. excel1.Application.DisplayAlerts = false;//使合并操作不提示警告信息
12.
13. range1.Merge(false);//参数为True则为每一行合并为一个单元格
14.
15. excel1.Application.DisplayAlerts = true;
16.
17. excel1.Visible = true;
十、复制Excel表＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03.Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05.Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
06.
07.worksheet1.Cells[1, 1] = "123";
08.
09.worksheet1.Copy(Type.Missing, worksheet1);
10.
11.Worksheet worksheet2 =(Worksheet)worksheet1.Next;
12.
13.//worksheet2.Name = "Sheet2";
14.
15.excel1.Visible = true;
十一、页面设置＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
view plaincopy to clipboardprint?
01.Microsoft.Office.Interop.Excel.Application excel1 = new Microsoft.Office.Interop.Excel.Application();
02.
03. Workbook workbook1 = excel1.Workbooks.Add(true);
04.
05. excel1.Caption = "我的报表";
06.
07. Worksheet worksheet1 = (Worksheet)workbook1.Worksheets["sheet1"];
08.
09. worksheet1.PageSetup.PaperSize = XlPaperSize.xlPaperA3;//纸张大小
10.
11. worksheet1.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
12.
13. worksheet1.PageSetup.Orientation = XlPageOrientation.xlLandscape;//页面方向为横向
14.
15. worksheet1.PageSetup.TopMargin = excel1.CentimetersToPoints(2);//上边距为２厘米(厘米转像素)
16.
17. worksheet1.PageSetup.BottomMargin = excel1.CentimetersToPoints(2);//下边距为2厘米(厘米转像素)
18.
19. worksheet1.PageSetup.LeftMargin = excel1.CentimetersToPoints(1.5);//左边距为1.5厘米(厘米转像素)
20.
21. worksheet1.PageSetup.RightMargin = excel1.CentimetersToPoints(1.5);//右边距为1.5厘米(厘米转像素)
22.
23. worksheet1.PageSetup.HeaderMargin = excel1.CentimetersToPoints(1.2);//页眉边距为1.2厘米(厘米转像素)
24.
25. worksheet1.PageSetup.FooterMargin = excel1.CentimetersToPoints(1);//页脚边距为1厘米(厘米转像素)
26.
27. worksheet1.PageSetup.CenterHorizontally = true;//页面水平居中
28.
29. worksheet1.PageSetup.CenterVertically = false;//页面不垂直居中
30.
31. worksheet1.PageSetup.CenterFooter = "第&P页，共&N页";//中间页脚内容
32.
33. excel1.Visible = true;
view plaincopy to clipboardprint?
01.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 = "我正在玩搜狐微博，快来“关注”我，了解我的最新动态吧。
02."+userLink+""; $('miniAd').show(); } }, onFailure: function(){} }}); } showMiniAd();
转自：http://sdshizq.blog.163.com/blog/static/109414056201052432126508/
第2篇： asp.net中将DataGrid中的数据导入到excel中，并设置其格式
----------------------------------------------------------------------------
终于完成了从datagrid 中导入excel，为了防止忘记，特意记录下来，为大家和自己提供方便。
web应用程序中主要代码如下：
view plaincopy to clipboardprint?
01.//设置DataGrid2数据源 ，并绑定（由于这一步很简单，所以略过）
02.
03./**//*设置DataGrid2的格式为文本型，这样就解决了导入excel之后，形如“00000123”变成了“123”的问题。在这里，为了简单起见，我设置了dataGrid总的属性。也可以为每个单元格设置属性，如DataGrid2.Items[0].Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");*/
04.DataGrid2.Attributes.Add("style","vnd.ms-excel.numberformat:@");
05.
06.//将DataGrid2中的数据以刘的形式写入excel文件中
07.Response.Clear();
08.Response.Buffer= true;
09.Response.Charset="GB2312";
10.Response.AppendHeader("Content-Disposition","attachment;filename=zjxx.xls");
11.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
12.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
13.this.EnableViewState = false;
14.System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
15.System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
16.System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
17.this.DataGrid2.RenderControl(oHtmlTextWriter);
18.Response.Write(oStringWriter.ToString());
19.Response.End();
windows应用程序中如下：
view plaincopy to clipboardprint?
01.//其中zjtable中已经从数据库中读入了数据
02.Excel.ApplicationClass excelApp ;
03.excelApp = new Excel.ApplicationClass();
04.Excel.Workbook excelBook =excelApp.Workbooks.Add(1);
05.Excel.Worksheet excelSheet=(Excel.Worksheet)excelBook.Worksheets[1];
06.
07.excelApp.Visible=true;
08.excelSheet.Cells[1,1]="姓名";
09.excelSheet.Cells[1,2]="性别";
10.excelSheet.Cells[1,3]="出生日期";
11.
12.//设置excel文件中所有的单元格为文本型
13.excelSheet.Cells.NumberFormatLocal="@";
14.for(int i=0;i < zjtable.Rows.Count ;i++)
15....{
16.//将zjtable中的数据导入到excel文件中
17. DataRow row=zjtable.Rows[i];
18. for (int j=1;j<=3;j++)
19. excelSheet.Cells[i+2,j]=row[j].ToString();
20.}
本文来自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就可以用了！
view plaincopy to clipboardprint?
01.using System;
02.using System.Data;
03.using System.Web;
04.
05.namespace ExcelExporter
06.{
07.
08. public class ExcelExporter
09. {
10.
11. #region Private variables
12.
13. private System.Web.UI.Page page;
14.
15. #endregion
16.
17. public ExcelExporter( System.Web.UI.Page TargetPage )
18. {
19. this.page = TargetPage;
20. }
21.
22. public void Export( DataSet Data, string ExcelFileName )
23. {
24.
25. this.page.Response.Clear();
26. this.page.Response.ContentType = "application/vnd.ms-excel";
27. this.page.Response.AddHeader( "Content-Disposition", "attachment; Filename=" + ExcelFileName );
28.
29. //頭
30. WriteWorkbookHeader();
31. //
32. foreach( DataTable table in Data.Tables )
33. WriteTable( table );
34. //尾
35. WriteWorkbookFooter();
36. this.page.Response.End();
37.
38. }
39.
40. private void WriteWorkbookHeader()
41. {
42. this.page.Response.Write( "<?xml version=\"1.0\"?>\r\n" );
43. this.page.Response.Write( "<?mso-application progid=\"Excel.Sheet\"?>\r\n" );
44. this.page.Response.Write( "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" );
45. this.page.Response.Write( "xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" );
46. this.page.Response.Write( "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n" );
47. this.page.Response.Write( "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" );
48. this.page.Response.Write( "xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n" );
49. this.page.Response.Write( "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n" );
50. this.page.Response.Write( "<LastAuthor>MSINC</LastAuthor>\r\n" );
51. this.page.Response.Write( " <Created>" + DateTime.Now.ToString() + "</Created>\r\n" );
52. this.page.Response.Write( " <Version>11.5703</Version>\r\n" );
53. this.page.Response.Write( "</DocumentProperties>\r\n" );
54. this.page.Response.Write( "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n" );
55. this.page.Response.Write( " <ProtectStructure>False</ProtectStructure>\r\n" );
56. this.page.Response.Write( " <ProtectWindows>False</ProtectWindows>\r\n" );
57. this.page.Response.Write( "</ExcelWorkbook>\r\n" );
58. this.page.Response.Write( " <Styles>\r\n" );
59. this.page.Response.Write( " <Style ss:ID=\"s1\">\r\n" );
60. this.page.Response.Write( " <Font ss:Bold=\"1\"/>\r\n" );
61. this.page.Response.Write( " </Style>\r\n" );
62. this.page.Response.Write( " </Styles>\r\n" );
63.
64.
65. }
66.
67. private void WriteWorkbookFooter()
68. {
69. this.page.Response.Write( "</Workbook>\r\n" );
70. }
71.
72.
73. private void WriteTable( DataTable table )
74. {
75.
76. this.page.Response.Write( "<Worksheet ss:Name='" + table.TableName + "'>\r\n" );
77. this.page.Response.Write( "<Table ss:ExpandedColumnCount=\"" + table.Columns.Count + "\" ss:ExpandedRowCount=\"" + (table.Rows.Count + 1) + "\" x:FullColumns=\"1\" x:FullRows=\"1\">\r\n" );
78. WriteTableHeader( table );
79. WriteTableRows( table );
80. this.page.Response.Write( "</Table>\r\n" );
81. this.page.Response.Write( "</Worksheet>\r\n" );
82.
83. }
84. // 表格[頭]
85. private void WriteTableHeader( DataTable table )
86. {
87.
88. foreach( DataColumn column in table.Columns )
89. this.page.Response.Write( "<Column>" + column.ColumnName + "</Column>\r\n" );
90.
91. this.page.Response.Write( "<Row>\r\n" );
92.
93. foreach( DataColumn column in table.Columns )
94. this.page.Response.Write( "<Cell ss:StyleID=\"s1\"><Data ss:Type=\"String\">" + column.ColumnName + "</Data></Cell>\r\n" );
95.
96. this.page.Response.Write( "</Row>\r\n" );
97.
98. }
99.
100. //
101. private void WriteTableRows( DataTable table )
102. {
103. foreach( DataRow Row in table.Rows )
104. WriteTableRow( Row );
105. }
106. //
107. private void WriteTableRow( DataRow Row )
108. {
109.
110. this.page.Response.Write( "<Row>\r\n" );
111.
112. foreach( object loop in Row.ItemArray )
113. {
114.
115. this.page.Response.Write( "<Cell><Data ss:Type=\"" + GetExcelType( loop ) + "\">" );
116.
117. if( loop != null && loop != DBNull.Value )
118. {
119.
120. if( loop is byte[] )
121. this.page.Response.Write( "(...)" );
122. else if( loop is decimal )
123. {
124. decimal decimalNumber = (decimal) loop;
125. this.page.Response.Write( System.Web.HttpUtility.HtmlEncode(decimalNumber.ToString( "N" )) );
126. }
127. else if( loop is DateTime )
128. {
129. this.page.Response.Write( ((DateTime) loop).ToString( "yyyy-MM-dd HH:mm:ss" ) );
130. }
131. else
132. {
133. this.page.Response.Write( System.Web.HttpUtility.HtmlEncode( loop.ToString() ) );
134. }
135.
136. }
137.
138. this.page.Response.Write( "</Data></Cell>\r\n" );
139.
140. }
141.
142. this.page.Response.Write( "</Row>\r\n" );
143.
144. }
145.
146.
147. private bool IsNumber( string Value )
148. {
149.
150. if( Value == "" )
151. return false;
152.
153. char[] chars = Value.ToCharArray();
154.
155. foreach( char ch in chars )
156. {
157. if( ch != '$' && ch != '.' && ch != ',' && !char.IsNumber( ch ) )
158. return false;
159. }
160.
161. return true;
162.
163. }
164.
165. private string GetExcelType( object Value )
166. {
167.
168. if( Value == null || Value == DBNull.Value || Value is string )
169. return "String";
170. // else if( Value is DateTime )
171. // return "Date";
172. else if( IsNumber( Value.ToString() ) )
173. return "Number";
174. else
175. return "String";
176. }
177. }
178.}
第4篇：asp.net用模板導出excel的類
-----------------------------------
view plaincopy to clipboardprint?
01.<3>用模板列導出excel類
02.前台設置
03.<asp:Button runat="server" ID="btn_Excel2" CausesValidation="False" Text="導Excel" UseSubmitBehavior="False" CssClass="button" OnClick="btn_Excel_Click" />
04.<asp:PostBackTrigger ControlID="btn_Excel2" />
05.後台類
06.//public override void VerifyRenderingInServerForm(Control control)
07. //{
08. //}
09.public void OutputExcel(DataView dv, string str)
10. {
11. #region
12. GC.Collect();
13.
14. //ExcelOperate excelOperate = new ExcelOperate();
15. Application excel;// = new Application();
16. //int rowIndex = 4;
17. //int colIndex = 1;
18.
19. _Workbook xBk;
20. _Worksheet xSt;
21.
22. excel = new ApplicationClass();
23.
24. xBk = excel.Workbooks.Add(Server.MapPath("xls_template") + "\\標准工時模板.xls");
25.
26. xSt = (_Worksheet)xBk.ActiveSheet;
27.
28. xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[2, 9]).Merge(Missing.Value); //横向合并
29. xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[2, 1]).Value2 = GetDept(Session["sa_kb"].ToString()) + " " + "標准工時";
30. //13838
31. xSt.Cells[3, 1] = "成品料號:" + Session["sa001"].ToString();
32. xSt.Cells[3, 3] = "型號:" + Session["sa02x"].ToString();
33. xSt.Cells[3, 8] = "版次/版本:" + Session["banchi"].ToString();
34. xSt.Cells[4, 1] = "半成品料號:" + Session["sa011"].ToString();
35. xSt.Cells[4, 3] = "訂單:" + Session["sa_NO"].ToString();
36. xSt.Cells[4, 8] = "IE:" + Session["sa_IE"].ToString();
37. for (int i = 0; i < dv.Count; i++)
38. {
39. xSt.Cells[7 + i, 1] = dv[i].Row[0].ToString();
40.
41. xSt.Cells[7 + i, 2] = dv[i].Row[1].ToString();
42. xSt.Cells[7 + i, 3] = dv[i].Row[3].ToString();
43. xSt.Cells[7 + i, 4] = dv[i].Row[4].ToString();
44. xSt.Cells[7 + i, 5] = dv[i].Row[5].ToString();
45.
46. if (dv[i].Row[2].ToString() == "1")//若是有數據變更的則著色表示
47. {
48.
49. SetBold(xSt, xSt.Cells[7 + i, 1], xSt.Cells[7 + i, 9], 1);
50.
51. }
52.
53.
54. SetHAlignCenter(xSt, xSt.Cells[7 + i, 6], xSt.Cells[7 + i, 9]);//居中
55.
56. }
57. int rowNum = 7;
58. DataView dvw = GetBiao();
59. for (int i = 0; i < dvw.Count; i++)
60. {
61. int rowAdd = Convert.ToInt32(dvw[i].Row[1].ToString());
62.
63. xSt.get_Range(xSt.Cells[rowNum, 6], xSt.Cells[rowNum + rowAdd - 1, 6]).Merge(Missing.Value);
64. xSt.get_Range(xSt.Cells[rowNum, 6], xSt.Cells[rowNum + rowAdd - 1, 6]).Value2 = dvw[i].Row[2].ToString();
65. xSt.get_Range(xSt.Cells[rowNum, 7], xSt.Cells[rowNum + rowAdd - 1, 7]).Merge(Missing.Value);
66. xSt.get_Range(xSt.Cells[rowNum, 7], xSt.Cells[rowNum + rowAdd - 1, 7]).Value2 = dvw[i].Row[3].ToString();
67. xSt.get_Range(xSt.Cells[rowNum, 8], xSt.Cells[rowNum + rowAdd - 1, 8]).Merge(Missing.Value);
68. xSt.get_Range(xSt.Cells[rowNum, 8], xSt.Cells[rowNum + rowAdd - 1, 8]).Value2 = dvw[i].Row[4].ToString();
69. xSt.get_Range(xSt.Cells[rowNum, 9], xSt.Cells[rowNum + rowAdd - 1, 9]).Merge(Missing.Value);
70. xSt.get_Range(xSt.Cells[rowNum, 9], xSt.Cells[rowNum + rowAdd - 1, 9]).Value2 = dvw[i].Row[6].ToString();
71. rowNum = rowNum + rowAdd;
72.
73. }
74.
75. xSt.get_Range(xSt.Cells[(int)dv.Count + 7, 1], xSt.Cells[(int)dv.Count + 7, 2]).Merge(Missing.Value);
76. xSt.get_Range(xSt.Cells[(int)dv.Count + 7, 1], xSt.Cells[(int)dv.Count + 7, 2]).Value2 = "間接人員";
77. #region
78. int dvc = (int)dv.Count + 7;
79. xSt.Cells[dvc + 1, 1] = "1";
80. xSt.Cells[dvc + 2, 1] = "2";
81. xSt.Cells[dvc + 3, 1] = "3";
82. xSt.Cells[dvc + 1, 2] = "修改";
83. xSt.Cells[dvc + 2, 2] = "調機";
84. xSt.Cells[dvc + 3, 2] = "備注";
85. string[] str1 = GetSum("1").Split(';');
86. xSt.Cells[dvc + 1, 3] = str1[0];
87. xSt.Cells[dvc + 1, 4] = str1[1];
88. xSt.Cells[dvc + 1, 5] = str1[2];
89. xSt.Cells[dvc + 1, 6] = str1[3];
90. string[] str2 = GetSum("2").Split(';');
91. xSt.Cells[dvc + 2, 3] = str2[0];
92. xSt.Cells[dvc + 2, 4] = str2[1];
93. xSt.Cells[dvc + 2, 5] = str2[2];
94. xSt.Cells[dvc + 2, 6] = str2[3];
95. string[] str3 = GetSum("3").Split(';');
96. xSt.Cells[dvc + 3, 3] = str3[0];
97. xSt.Cells[dvc + 3, 4] = str3[1];
98. xSt.Cells[dvc + 3, 5] = str3[2];
99. xSt.Cells[dvc + 3, 6] = str3[3];
100. #endregion
101.
102. int ExRow = (int)dv.Count + 12;
103. // 將圖片寫入到Excel
104. xSt.get_Range(xSt.Cells[ExRow, 6], xSt.Cells[ExRow + 6, 9]).Merge(Missing.Value);
105. Worksheet wk = (Worksheet)xSt;
106. int heit = ((int)dv.Count) * 22;
107. heit = heit - 30;
108.
109. string strpath = Session["sa010"].ToString();
110. if (strpath != "")
111. {
112. wk.Shapes.AddPicture(strpath, MsoTriState.msoFalse, MsoTriState.msoTrue, 455, heit, 113, 84);
113. }
114. xSt.get_Range(xSt.Cells[ExRow, 6], xSt.Cells[ExRow + 6, 9]).Value2 = "";
115. xSt.Cells[ExRow, 2] = "產線總人數(人):";
116. xSt.Cells[ExRow + 1, 2] = "產線瓶頸時間(秒):";
117. xSt.Cells[ExRow + 2, 2] = "產線平衡率(%):";
118. xSt.Cells[ExRow + 3, 2] = "產品標準工時(秒):";
119. xSt.Cells[ExRow + 4, 2] = "標準日產能(PCS/10H):";
120. //設置位置
121. SetHAlignCenter(xSt, xSt.Cells[ExRow, 2], xSt.Cells[ExRow, 2], "str");
122. SetHAlignCenter(xSt, xSt.Cells[ExRow + 1, 2], xSt.Cells[ExRow + 1, 2], "str");
123. SetHAlignCenter(xSt, xSt.Cells[ExRow + 2, 2], xSt.Cells[ExRow + 2, 2], "str");
124. SetHAlignCenter(xSt, xSt.Cells[ExRow + 3, 2], xSt.Cells[ExRow + 3, 2], "str");
125. SetHAlignCenter(xSt, xSt.Cells[ExRow + 4, 2], xSt.Cells[ExRow + 4, 2], "str");
126. //設置字體產色
127.
128. #region
129. SetBold(xSt, xSt.Cells[ExRow, 2], xSt.Cells[ExRow, 2], "");
130. SetBold(xSt, xSt.Cells[ExRow + 1, 2], xSt.Cells[ExRow + 1, 2], "");
131. SetBold(xSt, xSt.Cells[ExRow + 2, 2], xSt.Cells[ExRow + 2, 2], "");
132. SetBold(xSt, xSt.Cells[ExRow + 3, 2], xSt.Cells[ExRow + 3, 2], "");
133. SetBold(xSt, xSt.Cells[ExRow + 4, 2], xSt.Cells[ExRow + 4, 2], "");
134. #endregion
135.
136. xSt.get_Range(xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5]).Merge(Missing.Value);
137. xSt.get_Range(xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5]).Value2 = GetSum("4");
138. SetBold(xSt, xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5], "");
139. xSt.get_Range(xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5]).Merge(Missing.Value);
140. xSt.get_Range(xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5]).Value2 = GetSum("5");
141. SetBold(xSt, xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5], "");
142. xSt.get_Range(xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5]).Merge(Missing.Value);
143. xSt.get_Range(xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5]).Value2 = GetSum("6");
144. SetBold(xSt, xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5], "");
145.
146. xSt.get_Range(xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5]).Merge(Missing.Value);
147. xSt.get_Range(xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5]).Value2 = GetSum("7");
148. SetBold(xSt, xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5], "");
149. xSt.get_Range(xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5]).Merge(Missing.Value);
150. xSt.get_Range(xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5]).Value2 = GetSum("8");
151. SetBold(xSt, xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5], "");
152. //居中
153. SetHAlignCenter(xSt, xSt.Cells[ExRow, 3], xSt.Cells[ExRow, 5]);
154. SetHAlignCenter(xSt, xSt.Cells[ExRow + 1, 3], xSt.Cells[ExRow + 1, 5]);
155. SetHAlignCenter(xSt, xSt.Cells[ExRow + 2, 3], xSt.Cells[ExRow + 2, 5]);
156. SetHAlignCenter(xSt, xSt.Cells[ExRow + 3, 3], xSt.Cells[ExRow + 3, 5]);
157. SetHAlignCenter(xSt, xSt.Cells[ExRow + 4, 3], xSt.Cells[ExRow + 4, 5]);
158.
159. xSt.get_Range(xSt.Cells[ExRow + 5, 1], xSt.Cells[ExRow + 5, 5]).Merge(Missing.Value); //横向合并
160. xSt.get_Range(xSt.Cells[ExRow + 5, 1], xSt.Cells[ExRow + 5, 5]).Value2 = "注: 適用型號: ";
161.
162. //
163. //显示效果
164. //
165. excel.Visible = false;
166.
167. //xSt.Export(Server.MapPath(".")+"\\"+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
168.
169. string stick = DateTime.Now.Ticks.ToString();
170. xBk.SaveCopyAs(Server.MapPath("xls_files") + "\\" + stick + ".xls");
171.
172. //ds = null;
173. xBk.Close(false, null, null);
174.
175. excel.Quit();
176. System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
177. System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
178. System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
179. xBk = null;
180. excel = null;
181. xSt = null;
182. GC.Collect();
183. string path = Server.MapPath("xls_files") + "\\" + stick + ".xls";
184.
185. System.IO.FileInfo file = new System.IO.FileInfo(path);
186. Response.Clear();
187. Response.Charset = "GB2312";
188. Response.ContentEncoding = System.Text.Encoding.UTF8;
189. // 添加頭信息，,彈出另存為窗口
190. Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
191. //讓瀏覽器顯示下載信息
192. Response.AddHeader("Content-Length", file.Length.ToString());
193.
194. // 指定返回一個不能被客戶端讀取的流，下載
195. Response.ContentType = "application/ms-excel";
196.
197. //把文件流下載到客戶端
198. Response.WriteFile(file.FullName);
199. // 停止頁面的執行
200.
201. Response.End();
202. #endregion
203. }
926
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
