.net

2021-09-09 00:32:40

一、打开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;  

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;  

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;  

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;  

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;  
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;  
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;  
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;  
　　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;  
　　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);
    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);//上边距为２厘米(厘米转像素)   
  
     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;  
　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);//上边距为２厘米(厘米转像素)
     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(); 
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应用程序中主要代码如下：

view plaincopy to clipboardprint?
//设置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();

//设置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();   
}  
//其中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就可以用了！

view plaincopy to clipboardprint?
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";   
        }   
    }   
}

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的類

-----------------------------------

view plaincopy to clipboardprint?
<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   
    }

<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
    }

.net,布布扣,bubuko.com

.net

码农公寓

相关文章