C#中Table导入Excel
前阵子项目中遇到得问题。。。。当时在网上没找到解决得办法。。。今天发一个上来。。。
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.Diagnostics;
using Excel;
using System.Text;
/// <summary>
/// TableToExcel
///Table导入Excel
/// </summary>
public class TableToExcel
{
public TableToExcel()
{
// TODO: 在此处添加构造函数逻辑
}
public void ToExcel(Table t,string title)
{
Excel.ApplicationClass ea = new ApplicationClass();
Excel.Workbook wb = ea.Workbooks.Add(true);
Excel.Worksheet ws = wb.ActiveSheet as Excel.Worksheet;
int col = t.Rows[(t.Rows.Count - 1)].Cells.Count;
Excel.Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, col]);
range.MergeCells = true;
range.Value2 = title;
for (int i = 1; i <= t.Rows.Count; i++)
{
int count = 1;//当前列
for (int j = 1; j <= t.Rows[i - 1].Cells.Count; j++)
{
//ws.Cells[i, j] = "";
int colspan = t.Rows[i - 1].Cells[j-1].ColumnSpan;
if (colspan == 0) colspan = 1;
if (colspan == 1)
{
ws.Cells[i + 1, count] = cleanStr(t.Rows[i - 1].Cells[j - 1].Text);
Range _r = ws.Cells[i+1, count] as Range;
Excel.Style s = _r.Style as Excel.Style;
s.HorizontalAlignment = XlHAlign.xlHAlignCenter;
count++;
}
else
{
Excel.Range _range = ws.get_Range(ws.Cells[i+1, count], ws.Cells[i+1, count + colspan - 1]);
_range.MergeCells = true;
_range.Value2 = cleanStr(t.Rows[i - 1].Cells[j - 1].Text);
count = count + colspan ;
}
}
}
wb.SaveCopyAs(HttpContext.Current.Server.MapPath("~/Courses/temp/Excel.xls"));
}
protected string cleanStr(String str)
{
StringBuilder bs = new StringBuilder(str);
bs = bs.Replace("<br/>", " ");
return bs.ToString();
}
}
现在存在的问题:
1、还不能控制Excel单元格内得换行;
2、生成Excel下载到客户端,用Excel编辑时会出现两个界面sheet1和Excel.xls(如下图)
望高人指点迷津。。。。