Excel的导出

前段时间因为有需求,需要将数据表导出导Execl,导出的Excel是有多个表头列的..类似以下这种的

..没有找到什么好的办法.后边用了Office的引用实现的,写了个类..不过就是效率慢..而且容易出现进程的死锁.
后边也找了些办法,不过都没很好的解决..感觉比较好的是
在运行中输入dcomcnfg-组件服务-计算机-我的电脑-DCOM配置-MS Excel应用程序-属性
然后在选安全选项卡-把三个权限改成自定义,添加对应的帐户..或者直接添加Everyone,把权限选上..
标识的选项卡选择-交互式用户

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Collections;

using Excel;

using Microsoft.Office.Core;

/// <summary>

/// ConvertToExcel 的摘要说明

/// </summary>

public struct ToExeclReport

{

public string unitName;

public string dimension;

public string auditing;

public string report;

public string tab;

public string tel;

public string reportTime;

}

public class ConvertToExcel

{

//Hashtable hsTable = new Hashtable();

public ConvertToExcel()

{

// TODO: 在此处添加构造函数逻辑

}

public bool ToExcel(System.Data.DataTable dt,string totalTitel)

{

Hashtable hsTableAcrossColumn,hsTableAcrossRows;

ToExeclReport rp=new ToExeclReport();

//GetReportInfomation(dt,ref rp);

string[,] tableColumnNameArray = ConvertTableTitleToArray(dt);

int oneDimensionLength = tableColumnNameArray.GetLength(0);

int twoDimensionLength = tableColumnNameArray.GetLength(1);

hsTableAcrossRows = GetElementAcrossRows(tableColumnNameArray);

hsTableAcrossColumn = GetElementAcrossColumns(tableColumnNameArray);

Excel.Application excel = new Application();

excel.DisplayAlerts = false;

Excel.Workbook wb = excel.Workbooks.Open("D:\\ToExcel\\Excel\\Test.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);

Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets["Sheet1"];

wb.Activate();

ws.Activate();

/*写入标题*/

ws.Cells[1, 1] = totalTitel;

((Excel.Range)ws.Cells[1, 1]).Font.Size = 22;

//((Excel.Range)ws.Cells[1, 1]).Font.Bold = true ;

Excel.Range rng = (Excel.Range)ws.get_Range(ws.Cells[1, 1], ws.Cells[1, oneDimensionLength]);

//rng.Font.Size = 22;

//rng.Font.Bold = true;

rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

rng.VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;

/*写入表头*/

for (int i = 0; i < twoDimensionLength; i++)

{

for (int j = 0; j < oneDimensionLength; j++)

{

if(tableColumnNameArray[j,i]==null)

{

continue;

}

ws.Cells[3+i, j+1] = tableColumnNameArray[j, i];

foreach (DictionaryEntry log in hsTableAcrossColumn)

{

if (log.Key.ToString() == j.ToString()+ "_" + i.ToString())

{

Excel.Range titleRag=ws.get_Range(ws.Cells[3+i,j+1],ws.Cells[3+i,j+Convert.ToInt32(log.Value)]);

titleRag.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

titleRag.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

}

int acrossRows=Convert.ToInt32(hsTableAcrossRows[j.ToString() + "_" + i.ToString()]);

if (acrossRows>1)

{

Excel.Range titleRag = ws.get_Range(ws.Cells[3+i,j+1], ws.Cells[3+i+acrossRows-1,j+1]);

titleRag.MergeCells = true;

titleRag.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

titleRag.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

}

/*写入内容*/

for (int i = 0; i < dt.Rows.Count; i++)

{

for (int j = 0; j < dt.Columns.Count; j++)

{

ws.Cells[3 + twoDimensionLength + i, j + 1] = dt.Rows[i][j];

}

/*更改样式*/

Excel.Range tableRng = ws.get_Range(ws.Cells[3,1],ws.Cells[twoDimensionLength+dt.Rows.Count+2,oneDimensionLength]);

tableRng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

/*保存&清除资源占用*/

excel.DisplayAlerts = true;

wb.SaveAs("D:\\ToExcel\\Excel\\" + DateTime.Now.Millisecond + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

wb.Close(null, null, null);

excel.Workbooks.Close();

excel.Quit();

if (rng != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);

rng = null;

}

if (ws != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);

ws = null;

}

if (wb != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);

wb = null;

}

if (excel != null)

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

excel = null;

}

GC.Collect();

return true;

}

public void GetReportInfomation(System.Data.DataTable dt,ref ToExeclReport rp)

{

rp.auditing = dt.Rows[0]["审核人"].ToString();

rp.report = dt.Rows[0]["填报人"].ToString();

rp.tab = dt.Rows[0]["制表人"].ToString();

rp.tel = dt.Rows[0]["联系电话"].ToString();

rp.reportTime = dt.Rows[0]["日期"].ToString();

rp.unitName = dt.Rows[0]["会员单位"].ToString();

for (int i = 0; i < dt.Columns.Count; i++)

{

if (dt.Columns[i].ColumnName == "维度")

{

rp.dimension = dt.Rows[0]["维度"].ToString();

}

dt.Columns.Remove("审核人");

dt.Columns.Remove("填报人");

dt.Columns.Remove("制表人");

dt.Columns.Remove("联系电话");

dt.Columns.Remove("日期");

dt.Columns.Remove("会员单位");

dt.Columns.Remove("维度");

}

public Hashtable GetElementAcrossRows(string[,] tableColumnNameArray)

{

int offest = 1;

Hashtable hsTable = new Hashtable();

int oneDimensionLength = tableColumnNameArray.GetLength(0);

int twoDimensionLength = tableColumnNameArray.GetLength(1);

for (int i = 0; i < oneDimensionLength; i++)

{

for (int j = 0; j < twoDimensionLength-1; j++)

{

if (tableColumnNameArray[i, j + 1] == null)

{

offest = twoDimensionLength - j;

hsTable.Add(i.ToString()+"_" + j.ToString(), offest);

offest = 1;

break;

}

else

{

hsTable.Add(i.ToString() + "_" + j.ToString(), offest);

}

return hsTable;

}

public Hashtable GetElementAcrossColumns(string[,] tableColumnNameArray)

{

int offest=1;

Hashtable hsTable=new Hashtable();

for (int i = 0; i < tableColumnNameArray.GetLength(1); i++)

{

for (int j = tableColumnNameArray.GetLength(0)-1; j > 0 ; j--)

{

if (tableColumnNameArray[j, i] == tableColumnNameArray[j - 1, i] && tableColumnNameArray[j, i]!=null)

{

offest++;

tableColumnNameArray[j, i] = null;

}

else

{

//if(hsTable[j-offest+i.ToString()]

if (offest > 1)

{

hsTable[j.ToString()+"_"+ i.ToString()] = offest;

}

offest = 1;

}

return hsTable;

}

public string[,] ConvertTableTitleToArray(System.Data.DataTable dt)

{

ArrayList tableColumnNameList = new ArrayList();

for(int i=0;i<dt.Columns.Count;i++)

{

tableColumnNameList.Add(dt.Columns[i].ColumnName.Split('_'));

}

int maxColumn = GetMaxArrayLength(tableColumnNameList);

int maxRow = dt.Columns.Count;

string[,] tableColumnNameArray=new string[maxRow,maxColumn];

for (int i = 0; i < maxRow; i++)

{

string[] currentArray = dt.Columns[i].ColumnName.Split('_');

for (int j = 0; j < maxColumn; j++)

{

if (currentArray.Length > j)

{

tableColumnNameArray[i,j] = currentArray[j].ToString().Trim();

}

return tableColumnNameArray;

}

public int GetMaxArrayLength(ArrayList al)

{

for (int i = 1; i < al.Count; i++)

{

for (int j = 0; j < al.Count - i; j++)

{

if (((String[])al[j]).Length < ((String[])al[j + 1]).Length)

{

String[] temp =(String[])al[j];

al[j] = al[j + 1];

al[j + 1] = temp;

}

return ((String[])al[0]).Length;

}

导出表格的规格

private System.Data.DataTable CreatTable()

{

/*有同级表头的列必须放在一起,次级的表头用'_'分割*/

System.Data.DataTable dt = new System.Data.DataTable();

dt.Columns.Add("会员单位");

dt.Columns.Add("维度");

dt.Columns.Add("填报人");

dt.Columns.Add("审核人");

dt.Columns.Add("制表人");

dt.Columns.Add("联系电话");

dt.Columns.Add("日期");

dt.Columns.Add("编号");

dt.Columns.Add("队号及队数");

dt.Columns.Add("队型");

dt.Columns.Add("合同来源");

dt.Columns.Add("施工地区");

dt.Columns.Add("合同或协商工作量_炮");

dt.Columns.Add("合同或协商工作量_KM");

dt.Columns.Add("实际完成_炮");

dt.Columns.Add("实际完成_KM");

dt.Columns.Add("主要施工方法_仪器接收道数_道");

dt.Columns.Add("主要施工方法_道距_M");

dt.Columns.Add("主要施工方法_道检波器个数_个");

dt.Columns.Add("主要施工方法_覆盖次数_次");

dt.Columns.Add("主要施工方法_平均井深_M");

dt.Columns.Add("主要施工方法_扫描长度_S");

dt.Columns.Add("主要施工方法_震台数_新加_次*台");

dt.Columns.Add("主要施工方法_震台数_新加_次*新");

dt.Columns.Add("主要施工方法_震台数_次*M");

dt.Columns.Add("队年功效分析_施工天数_D");

dt.Columns.Add("队年功效分析_施工天数_M");

dt.Columns.Add("队年功效分析_工地日均工作时间_H");

dt.Columns.Add("队年功效分析_施工日效_炮/日");

dt.Columns.Add("队年功效分析_标准日效_炮/标日");

dt.Rows.Add("海贼王", "二维", "罗宾", "娜美", "山治", "012111", DateTime.Now.ToShortDateString(), "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23");

return dt;

}

posted on 2007-11-30 19:31 zhuibobo 阅读(214) 评论(0) 编辑收藏举报