本文探讨了使用C#从GridView导出数据到Excel时遇到的问题，并提供了两种不同的解决方案。第一种方法导致文件格式错误，第二种方法成功解决了问题并允许用户下载导出的Excel文件。
开发环境：C# + EXCEL2003 + VS2008
问题：
当使用如下代码从gridview导出数据到excel后，无法从excel取出数据，具体如下：
1、原使用的代码：
protected void ExcelOut_Click(object sender, EventArgs e)
{
Response.Clear(); //清除缓冲区流中的所有内容输出
Response.Buffer = true; //
Response.Charset = ""; //设置输出流的http字符集
//保存附件用"attachment;filename=bang.xls";在线打开用"online;filename=bang.xls"
//可以是.doc、.xls、.txt、.htm、
Response.AppendHeader("Content-Disposition", "attachment;filename=bang.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
//设置输出文件类型为excel文件。保存为word时，应为"application/ms-word"
//可以为application/ms-excel、application/ms-word、application/ms-txt、application/ms-html、或其他浏览器可直接支持文档
Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false; //关闭保存视图状态
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);//区域设置
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
DepartList.RenderControl(oHtmlTextWriter); //将DataGrid(dgBang)中的内容输出到oHtmlTextWriter
Response.Write(oStringWriter.ToString());
Response.End();//将当前所有缓冲的输出发送到客户端，并停止该页执行
｝前台页面需要一个控件配合：
<asp:FileUpload ID="ExcelIn" runat="server" Width="250px" CssClass="inputcss"
Height="16px" ></asp:FileUpload> <font face="宋体"></font>
protected void ExcelInButton_Click(object sender, EventArgs e)
{
string strFilePath = "";
string serverPath = "";
//通过控件来获取用户待上传的excel文件名，这个excel文件其实是上面程序产生的文件
strFilePath = ExcelIn.PostedFile.FileName;
// strFilePath = strFilePath.Substring(strFilePath.LastIndexOf("."));
// 获取系统时间生成附件名
//strFilePath = DateTime.Now.ToFileTime().ToString() + strFilePath;
// 将附件保存到服务器上
ExcelIn.PostedFile.SaveAs(("D:") + "\\空调标杆附件\\" + strFilePath);
serverPath = "D:\\空调标杆附件\\" + strFilePath;
string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + serverPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = "SELECT * FROM [Sheet1$]";
<span style="background-color: rgb(255, 102, 102);">ExcelConn.Open();</span>
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[Sheet1$]");<pre> //由于excel列名“当月效益”和gridview中“当月效益”指定的数据源"效益"不一样，无法匹配数据源，因此要把excel中列名改为“效益”
ds.Tables[0].Columns["当月效益"].ColumnName = "效益";
DepartList.DataSource = ds;
DepartList.DataBind();
ExcelConn.Close();
}
catch (MembershipCreateUserException ex)
{
ExcelConn.Close();
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}当程序运行到下面程序时，会报错，错误代码为："外部表不是预期的格式错误"。
ExcelConn.Open();分析：
当我们用2003以上版本的excel打开这些excel文件时，会出现：文件格式和扩展名不匹配，文件可能已损坏或不安全。
这说明我们用
ExcelOut_Click(object sender, EventArgs e) /// <summary>
/// 将数据从gridview导入到本地excel，文件保存在D:\\调试文件\\中，文件名为当时的时间
/// </summary>
/// <param name="FileName"></param>
private void SaveFile(String FileName)
{
try
{
string fileNameString = "D:\\调试文件\\" + FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{ return; }
//定义表格内数据的行数和列数
int rowscount = DepartList.Rows.Count;
int colscount = DepartList.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
System.Web.HttpContext.Current.Response.Write("没有数据可供保存");
return;
}
//列数必须大于0
if (colscount <= 0)
{
System.Web.HttpContext.Current.Response.Write("没有数据可供保存");
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
System.Web.HttpContext.Current.Response.Write("数据记录数太多(最多不能超过65536条)");
return;
}
//列数不可以大于255
if (colscount > 255)
{
System.Web.HttpContext.Current.Response.Write("数据记录行数太多，不能保存");
return;
}
Microsoft.Office.Interop.Excel.Application objExcel = null;
Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(true);
objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//设置列宽
//objExcel.Range objRng = objsheet.get_Range(objsheet.Cells[1, 1], objsheet.Cells[1, 1]);
//向Excel中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= DepartList.Columns.Count - 1; i++)
{
if (DepartList.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = DepartList.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= DepartList.Rows.Count - 1; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < 2; col++)
{
if (DepartList.Columns[col].Visible == true)
{
try
{
if (DepartList.Rows[row].Cells[col].Text != null)
{
objExcel.Cells[row + 2, displayColumnsCount] = DepartList.Rows[row].Cells[col].Text.ToString();
}
else
{
objExcel.Cells[row + 2, displayColumnsCount] = "";
}
displayColumnsCount++;
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(ex.Message);
}
}
}
}
//保存文件
objWorkbook.SaveAs(fileNameString, true, null, null, null,
null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, true, true, true,
true, true);
objExcel.Quit();
}
catch (Exception error)
{
System.Web.HttpContext.Current.Response.Write(error.Message);
return;
}
}
catch (Exception ex)
{
System.Web.HttpContext.Current.Response.Write(ex.Message);
}
} /// 下载服务器上已有文件
/// </summary>
/// <param name="fileName">保存到客户端时的文件名</param>
/// <param name="filePath">下载文件在服务器上所在物理路径全名</param>
public static void DownloadFile(String fileName, String filePath)
{
FileInfo fileInfo = new FileInfo(filePath);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearContent();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
System.Web.HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
System.Web.HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
System.Web.HttpContext.Current.Response.WriteFile(fileInfo.FullName);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}使用方法如下：
protected void ExcelOut_Click(object sender, EventArgs e)
{
string FileName = DateTime.Now.ToFileTime().ToString() + ".xls";
string FilePath = "D:\\调试文件\\" + FileName;
SaveFile(FileName);
DownloadFile(FileName, FilePath);
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
