Question

c#如何将table导入excel

Answer 1

朋友我给你一段代码吧!我测试已经通过了
public void DataSetToExcel(System.Data.DataTable dt,string FileName)
{
try
{
//Web页面定义
//System.Web.UI.Page mypage=new System.Web.UI.Page();

HttpResponse resp;
resp=HttpContext.Current.Response;
resp.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-disposition","attachment;filename="+ FileName +".xls");
resp.ContentType="application/ms-excel";

//变量定义
string colHeaders=null;
string Is_item=null;

//显示格式定义////////////////

//文件流操作定义
//FileStream fs=new FileStream(FileName,FileMode.Create,FileAccess.Write);
//StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("GB2312"));

StringWriter sfw=new StringWriter();
//定义表对象与行对象，同时用DataSet对其值进行初始化
// System.Data.DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select();
int i=0;
int cl=dt.Columns.Count;

//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for(i=0;i<cl;i++)
{
//if(i==(cl-1)) //最后一列，加\n
// colHeaders+=dt.Columns[i].Caption.ToString();
//else
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
sfw.WriteLine(colHeaders);
//sw.WriteLine(colHeaders);

//逐行处理数据
foreach (DataRow row in myRow)
{
//当前数据写入
for (i = 0; i < cl; i++)
{
//if(i==(cl-1))
// Is_item+=row[i].ToString()+"\n";
//else
Is_item += row[i].ToString() + "\t";
}
sfw.WriteLine(Is_item);
//sw.WriteLine(Is_item);
Is_item = null;
}
resp.Write(sfw);
//resp.Clear();
resp.End();
}
catch (Exception e)
{
throw e;
}
}
这段代码是把datatable数据导入到excel中

Answer 2

public void ExportToExcel(DataSet ds, string strExcelFileName)
{
Excel.Application excel = new Excel.Application();
Excel.Worksheet sheet = null;
int i = 1;
excel.Application.Workbooks.Add(true);
sheet = (Excel.Worksheet)excel.Application.Worksheets.get_Item(1);
foreach (System.Data.DataTable table in ds.Tables)
{
int rowIndex = 1;
int colIndex = 0;
try
{
if (i > 1)
excel.Worksheets.Add(System.Reflection.Missing.Value, sheet, 1, Excel.XlSheetType.xlWorksheet);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
sheet = (Excel.Worksheet)excel.Application.Worksheets.get_Item(i);
i++;
}
excel.Visible = false;
excel.ActiveWorkbook.SaveAs(strExcelFileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
excel.Quit();
excel = null;
GC.Collect();
}
//导出xls
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlconn = new SqlConnection("server=.;uid=sa;pwd=123;database=test"))
{
using (SqlDataAdapter sda = new SqlDataAdapter("select id as 标识列, name as 名称 from TD", sqlconn))
{
DataSet ds = new DataSet();
sda.Fill(ds, "TD");
ExportToExcel(ds, "C:\\Documents and Settings\\Administrator\\桌面\\Book1.xls");
MessageBox.Show("导出成功！", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
}
GC.Collect();
}
加二个引用
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;