求C#WINDOWS里头的dateset 导出到excel

求C#WINDOWS里面的dateset 导出到excel
如题
目前我写了一中方法，但是导出太慢了
求一种快的导出方法
下面是我的代码，如果有谁有速度快点的导出方法，请告诉下！
谢谢！

C# code

public static void OutputExceltemp(DataView dv,string str,EmicroWin.EmiList list,bool isTotal)        {            try            {                OutputExceltemp(dv,str,list.TheListView,isTotal);            }            catch            {               MessageBox.Show("请确保安装Office软件，否则不能导出Excel!");               return;            }        }        public static void OutputExceltemp(DataView dv,string str,EmicroWin.EmiListView listview,bool isTotal)        {            ArrayList cs=new ArrayList();            foreach(EmicroWin.EmiColumnHeader header in listview.TheColumns)            {                string field=header.DataField;                if(field==null||field=="")continue;                dv.Table.Columns[field].Caption=header.Text;                cs.Add(field);            }            OutputExceltemp(dv,str,(string[])cs.ToArray(typeof(string)),isTotal);        }        public static void OutputExceltemp(DataView dv,string str,bool isTotal)        {            ArrayList cs=new ArrayList();            foreach(DataColumn c in dv.Table.Columns)            {                cs.Add(c.ColumnName);            }            OutputExceltemp(dv,str,(string[])cs.ToArray(typeof(string)),isTotal);        }        public static void OutputExceltemp(DataView dv,string str,string columns,bool isTotal)        {            OutputExceltemp(dv,str,columns.Split(",".ToCharArray()),isTotal);        }//比较正规的导出        public static void OutputExceltemp(DataView dv,string str,string[] columns,bool isTotal)        {            //            // TODO: 在此处添加构造函数逻辑            //            Excel.Application excel;            int rowIndex=4;            int colIndex=0;            Excel._Workbook xBk;            Excel._Worksheet xSt;            excel= new Excel.ApplicationClass();;            xBk = excel.Workbooks.Add(true);            xSt = (Excel._Worksheet)xBk.ActiveSheet;            //            //取得标题            //            //foreach(DataColumn col in dv.Table.Columns)            foreach(string columnname in columns)            {                DataColumn col=dv.Table.Columns[columnname];                colIndex++;                excel.Cells[4,colIndex] = col.Caption;                xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐            }            decimal[] sums=new decimal[dv.Table.Columns.Count];            //            //取得表格中的数据            //            foreach(DataRowView row in dv)            {                rowIndex ++;                colIndex = 0;                foreach(string columnname in columns)                {                    DataColumn col=dv.Table.Columns[columnname];                    colIndex++;                    if(col.DataType == System.Type.GetType("System.DateTime"))                    {                        excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");                        xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐                    }                    else if(col.DataType == System.Type.GetType("System.String"))                    {                        excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();                        xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐                    }                    else                    {                        excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();                    }                    int sumindex=colIndex-2;                    switch(col.DataType.FullName)                    {                        case "System.Int32":                        case "System.Single":                        case "System.Double":                        case "System.Decimal":                            object o=row.Row[col];                            if(!Convert.IsDBNull(o))                                sums[sumindex]+=Convert.ToDecimal(o);                            break;                    }                }            }                        //            //加载一个合计行            //            int rowSum = rowIndex + 1;            int colSum = 1;            excel.Cells[rowSum,1] = "合计";            xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;                        //            //设置选中的部分的颜色            //            xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();            xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种            rowIndex++;            colIndex = 0;            foreach(string columnname in columns)            {                DataColumn col=dv.Table.Columns[columnname];                colIndex++;                switch(col.DataType.FullName)                {                    case "System.Int32":                    case "System.Single":                    case "System.Double":                    case "System.Decimal":                        xSt.Cells[rowIndex,colIndex]=sums[colIndex-2];                        break;                }            }            if(!isTotal)            {                for(int i=1;i<dv.Table.Columns.Count+2;i++)                {                    excel.Cells[rowSum,i] = "";                }            }            //            //取得整个报表的标题            //            excel.Cells[2,1] = str;            //            //设置整个报表的标题格式            //            xSt.get_Range(excel.Cells[2,1],excel.Cells[2,1]).Font.Bold = true;            xSt.get_Range(excel.Cells[2,1],excel.Cells[2,1]).Font.Size = 14;            //            //设置报表表格为最适应宽度            //            xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Select();            xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Columns.AutoFit();            //            //设置整个报表的标题为跨列居中            //            xSt.get_Range(excel.Cells[2,1],excel.Cells[2,colIndex]).Select();            xSt.get_Range(excel.Cells[2,1],excel.Cells[2,colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;            //            //绘制边框            //            xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;            xSt.get_Range(excel.Cells[4,1],excel.Cells[rowSum,2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗            xSt.get_Range(excel.Cells[4,1],excel.Cells[4,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗            xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗            xSt.get_Range(excel.Cells[rowSum,1],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗            //            //显示效果            //            excel.Visible=true;                    } 
 

[解决办法]
看索引里面有关excel交互的帖子
http://blog.csdn.net/jinjazz/archive/2008/12/05/3448268.aspx
[解决办法]
打开Excel模板，取range范围,再把值添加到数组，把数组赋值给range
[解决办法]
public static void ExportDataToExcel(DataSet ds, string FileName, string value)
{
for (int index = 0; index < ds.Tables[0].Rows.Count; index++)
{
string timeFlag = DateTime.Now.ToFileTime().ToString();
string strCom = string.Empty;
try
{

//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName +"\\" + timeFlag + ".xls" + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;""";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;

conn.Open();
string sqlStr = "insert into [Sheet4$] values (";
strCom = "CREATE TABLE [Sheet4]( ";
for (int k = 0; k < ds.Tables[0].Columns.Count ; k++)
{
if (value == "BU")
{
if (ds.Tables[0].Columns[k].Caption != "Datasource" && ds.Tables[0].Columns[k].Caption != "Server" && ds.Tables[0].Columns[k].Caption != "Country" && ds.Tables[0].Columns[k].Caption != "Engineer" && ds.Tables[0].Columns[k].Caption != "Reply")
{
sqlStr += "@" + ds.Tables[0].Columns[k].Caption + ",";
strCom += "[" + ds.Tables[0].Columns[k].Caption + "] ntext , ";
}
}
else
{
sqlStr += "@" + ds.Tables[0].Columns[k].Caption + ",";
strCom += "[" + ds.Tables[0].Columns[k].Caption + "] ntext, ";
}
}
sqlStr += "@" + "RawDataDesc" + "";
strCom += "[" + "RawDataDesc" + "] char";
sqlStr += ")";
strCom += ") ";

cmd.CommandText = strCom;

cmd.ExecuteNonQuery();

查看更多 下一篇