以前用framework2.0做的导出excel:
//导出Excel
public void readxlsfile(string modelfilepath, string newfilepath, System.Data.DataTable dt)
{
FileInfo fi = new FileInfo(modelfilepath);//读取模板
fi.CopyTo(newfilepath, true);
Excel.Application excel = new Excel.ApplicationClass();
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
object missing = System.Reflection.Missing.Value;
try
{
String templatePath = newfilepath;
//创建一个Application对象并使其可见
//app = new Excel.ApplicationClass();
//打开模板文件,得到WorkBook对象
workbook = excel.Workbooks.Open(templatePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet = (Excel.Worksheet)workbook.Sheets[1];//指定操作第一个表
if (dt.Rows.Count >0)
{
string[,] str1 = new string[dt.Rows.Count, dt.Columns.Count];
for (Int32 r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
str1[r, c] = (string)dt.Rows[r][c].ToString();
}
}
worksheet.get_Range("A2", worksheet.Cells[dt.Rows.Count, dt.Columns.Count]).Value2 = str1;
worksheet.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
catch (Exception ex)
{
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
}
if (workbook != null)
{
workbook.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (excel != null)
{
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
return;
}
excel.Application.DisplayAlerts = false; //不显示提示信息
//不显示效果
//excel.Visible = false;
string urlopen = Server.MapPath("~/Manager/Public/Template/职位信息表.xls");
workbook.SaveCopyAs(urlopen);
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
workbook.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (excel != null)
{
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();//挂起当前线程,直到处理终结器队列的线程清空该队列为止
string path = urlopen;
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename = " + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
public void readxlsfile(string modelfilepath, string newfilepath, System.Data.DataTable dt)
{
FileInfo fi = new FileInfo(modelfilepath);//读取模板
fi.CopyTo(newfilepath, true);
Excel.Application excel = new Excel.ApplicationClass();
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
object missing = System.Reflection.Missing.Value;
try
{
String templatePath = newfilepath;
//创建一个Application对象并使其可见
//app = new Excel.ApplicationClass();
//打开模板文件,得到WorkBook对象
workbook = excel.Workbooks.Open(templatePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet = (Excel.Worksheet)workbook.Sheets[1];//指定操作第一个表
if (dt.Rows.Count >0)
{
string[,] str1 = new string[dt.Rows.Count, dt.Columns.Count];
for (Int32 r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
str1[r, c] = (string)dt.Rows[r][c].ToString();
}
}
worksheet.get_Range("A2", worksheet.Cells[dt.Rows.Count, dt.Columns.Count]).Value2 = str1;
worksheet.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
worksheet.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
catch (Exception ex)
{
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
}
if (workbook != null)
{
workbook.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (excel != null)
{
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
return;
}
excel.Application.DisplayAlerts = false; //不显示提示信息
//不显示效果
//excel.Visible = false;
string urlopen = Server.MapPath("~/Manager/Public/Template/职位信息表.xls");
workbook.SaveCopyAs(urlopen);
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
workbook.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (excel != null)
{
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();//挂起当前线程,直到处理终结器队列的线程清空该队列为止
string path = urlopen;
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息,为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename = " + Server.UrlEncode(file.Name));
// 添加头信息,指定文件大小,让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流,必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
列名还是存在的。但升级到3.5后再来导出时发现列名没有了,最后
worksheet.get_Range("A2", worksheet.Cells[dt.Rows.Count, dt.Columns.Count]).Value2 = str1;
改为
worksheet.get_Range("A2", worksheet.Cells[dt.Rows.Count+1, dt.Columns.Count]).Value2 = str1;
这里“+1”是指列名(也就是标题栏有1行,当然有时候标题栏有2行,那我们就+2),就可以了。
很是想不通怎么回事。难道是BUG?