asp.net 将数据集中的数据导入到excel中

参考地址：http://www.cnblogs.com/xuanhun/archive/2010/04/29/1724500.html

参考地址：百度文库，excel格式设置：http://wenku.baidu.com/view/f5f862ff04a1b0717fd5dd10.html。

参考地址：excel参考地址:http://topic.csdn.net/u/20080904/09/8c0013c6-536f-4746-9786-2ad9a230c133.html

参考地址：http://www.cnblogs.com/smjack/archive/2009/02/25/1398257.html

 

 

本人写的代码。

可供参考：using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using Microsoft.Office.Interop.Excel; using System.IO; using System.Configuration; using System.Drawing; namespace excel { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string constring = ConfigurationManager.ConnectionStrings["CS"].ToString(); SqlConnection con = new SqlConnection(constring); con.Open(); SqlDataAdapter da = new SqlDataAdapter("select * from counter", con); DataSet a = new DataSet(); da.Fill(a); //建立一个文件 夹 //Console.WriteLine("...正在创建文件夹wtq.../n/n/n"); DirectoryInfo dir = new DirectoryInfo("c://wtq//wtq"); if (!dir.Exists) { dir.Create(); } else { Response.Write("你创建的文件夹已存在，/n/n/n"); } //开始EXCEL object objOpt = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application ap = new Application(); ap.Visible = true; Microsoft.Office.Interop.Excel._Workbook wk = ap.Workbooks.Add(objOpt); Sheets sheet = wk.Sheets; // Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)wk.ActiveSheet; _Worksheet ws = (_Worksheet)sheet.get_Item(2); _Worksheet wss = (_Worksheet)sheet.get_Item(1); ws.Activate();//激活那个sheet ws.Name = "重命名表单";//重命名表单 ws.Columns.ColumnWidth = 20;//设置列宽 ws.Columns.RowHeight = 20;//设置行号 //ap.Cells[1, 1] = "id"; //ap.Cells[1, 2] = "businessNum"; //ap.Cells[1, 3] = "exportTime"; //ap.Cells[1, 4] = "shipSide"; //ap.Cells[1, 5] = "shipOwner"; //ap.Cells[1, 6] = "destinationPort"; ws.Cells[1, 1] = "id"; ws.Cells[1, 2] = "businessNum"; ws.Cells[1, 3] = "exportTime"; ws.Cells[1, 4] = "shipSide"; ws.Cells[1, 5] = "shipOwner"; ws.Cells[1, 6] = "destinationPort"; wss.Cells[1, 1] = "标识符"; wss.Cells[1, 2] = "业务号"; wss.Cells[1, 3] = "ETD"; wss.Cells[1, 4] = "码头"; wss.Cells[1, 5] = "船长"; wss.Cells[1, 6] = "目的港"; for (int i = 0; i < a.Tables[0].Rows.Count; i++) { //ap.Cells[i + 2, 1] = a.Tables[0].Rows[i][0]; //ap.Cells[i + 2, 2] = a.Tables[0].Rows[i][1]; //ap.Cells[i + 2, 3] = a.Tables[0].Rows[i][2]; //ap.Cells[i + 2, 4] = a.Tables[0].Rows[i][3]; //ap.Cells[i + 2, 5] = a.Tables[0].Rows[i][4]; //ap.Cells[i + 2, 6] = a.Tables[0].Rows[i][5]; ws.Cells[i + 2, 1] = a.Tables[0].Rows[i][0]; ws.Cells[i + 2, 2] = a.Tables[0].Rows[i][1]; ws.Cells[i + 2, 3] = a.Tables[0].Rows[i][2]; ws.Cells[i + 2, 4] = a.Tables[0].Rows[i][3]; ws.Cells[i + 2, 5] = a.Tables[0].Rows[i][4]; ws.Cells[i + 2, 6] = a.Tables[0].Rows[i][5]; wss.Cells[i + 2, 1] = a.Tables[0].Rows[i][0]; wss.Cells[i + 2, 2] = a.Tables[0].Rows[i][1]; wss.Cells[i + 2, 3] = a.Tables[0].Rows[i][2]; wss.Cells[i + 2, 4] = a.Tables[0].Rows[i][3]; wss.Cells[i + 2, 5] = a.Tables[0].Rows[i][4]; wss.Cells[i + 2, 6] = a.Tables[0].Rows[i][5]; } Range range = ws.get_Range(ws.Cells[1,1],ws.Cells[1,6]); //选定单元格的范围 range.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Red);//设置边框的颜色 // range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Color = ColorTranslator.ToOle(Color.Blue); range.Font.Color =ColorTranslator.ToOle(Color.Blue);//设置区域字体颜色 range.Font.Bold = true;//设置字体为粗体。 range.Columns.RowHeight = 40;//设置局部行高 range.Columns.ColumnWidth = 40;//设置局部列宽 range.Interior.Color = 15; //设置背景颜色 range.BorderAround(XlLineStyle.xlDot, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, 15); try { wk.SaveAs("c://wtq//wtq//sjj", objOpt, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); } catch (Exception ex) { } // wk.Close(false,objOpt,objOpt); } } } 

 

注意：使用   wss.Cells[i + 2, 6] = a.Tables[0].Rows[i][5];这种方法给excel表格效率非常低。

应当使用如下，

这是我定义好的借口以后可以使用。

 

using System; using System.Collections.Generic; using System.Linq; using System.Web; using Microsoft.Office.Interop.Excel; using System.Reflection; using System.Data; using System.IO; using System.Text; namespace excel { public class exportExcel { public exportExcel() { } private Application excelApp = null;//定义excel应用程序 private _Workbook workBook = null;//定义工作簿 private _Worksheet workSheet =null; //定义工作表 private Range range = null; //定义单元格区域 private object objOpt = Missing.Value; public void Create() // { /*初始化对象*/ excelApp = new Application(); workBook = excelApp.Workbooks.Add(objOpt); workSheet = (_Worksheet)workBook.ActiveSheet; excelApp.Visible = true; } /// <summary> /// 从dataset中获取数据，并将其存入到excel中。 /// </summary> /// <param name="ds">数据集</param> /// <param name="title">标题数组</param> public void GetDataToExcel(DataSet ds, object[] title) { int rowCount = ds.Tables[0].Rows.Count;//数据集的行数。 // int colCount = ds.Tables[0].Columns.Count;//数据集的列数。 int colCount = 6; object[,] data=new object[rowCount,colCount]; for (int i = 0;i < colCount; i++) //设置标题 { workSheet.Cells[1 , i+1] = title[i] ; } for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { data[i, j] = ds.Tables[0].Rows[i][j].ToString(); } } workSheet.get_Range(workSheet.Cells[2,1],workSheet.Cells[rowCount+1,colCount]).Value2=data; range = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, colCount]); //选定单元格的范围 range.Interior.Color = 255; //设置背景颜色 range.BorderAround(XlLineStyle.xlDot, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, 15); //定义一个目录 DirectoryInfo dir = new DirectoryInfo("c://wtq//wtq"); if (!dir.Exists) { dir.Create(); } else { HttpContext.Current.Response.Write("你创建的文件夹已存在，/n/n/n"); } try { workBook.SaveAs("c://wtq//wtq//sjj", objOpt, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); } catch (Exception ex) { HttpContext.Current.Response.Write(ex); } } } } 

使用旁边代码：workSheet.get_Range(workSheet.Cells[2,1],workSheet.Cells[rowCount+1,colCount]).Value2=data;

这种效率比较高。