转载自：http://www.cnblogs.com/xiaye-liuyu/archive/2008/01/08/1030094.html
十分感谢这位同志。。。。
今天开始自己写文章，有描述不当的地方请多指教。
这里主要讲述两种方法，包括处理导出的乱码和格式化问题。
（1）在客户端把GridView的数据导出到Excel格式文件。
（2）使用COM组件把数据导出到Excel格式文件。
这两种方法主要的区别（或者说是使用COM导出数据比较不理想的地方）是使用COM组件必须添加该组件的DLL的引用，由于组件有版本的不同，所以随之而来的将是因版本的不同而不能使用该功能。另一点区别就是使用COM组件前提是客户端必须有安装Excel。
当然，你可以根据运用环境的不同使用不同的方法。以下贴出两种方法的代码，希望对大家有用。
（1） fileName = HttpUtility.UrlEncode(“文件名”, “文件编码”);／／如果文件名为中文格式，则可用这行代码对文件名进行编码。然后得用底下带（￥￥￥）行对其进行反编码，这样输出的文件名就不会是乱码。当然使用的编码要正确。
Code
1
HttpResponse response = control.Page.Response;//control为包含导出数据的容器2
response.Clear();3
response.ContentType = "application/vnd.ms-excel";4
（￥￥￥）response.AddHeader("Content-Disposition", "attachment;filename="5
+ HttpUtility.UrlDecode(“文件名”, “文件编码”)); //inline6
response.HeaderEncoding = System.Text.Encoding.GetEncoding("GB2312");／／对导出数据的标题进行编码设置（在导出数据标题是乱码的情况下使用）7
response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");／／对导出数据的内容进行编码设置（在导出数据的内容是乱码的情况下使用）8
response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");／／如果要对输出的数据进行样式控制，输出里必须先输出这行代码，否则使用样式无效。9
response.Write("\r\n");10
response.Write("<style> .mystyle1 " + "\r\n" + "{mso-style-parent:style0;mso-number-format:\"" + @"\@" + "\"" + ";} " + "\r\n" + "</style>");／／这里定义了特殊数字的样式11
response.Write("<style> .mystyle2" + "\r\n" + "{mso-style-parent:style0;mso-number-format:\"" + "0.0" + "\"" + ";}" + "\r\n" + "</style>");／／这里定义了对金钱的样式设置，12
StringBuilder builder1 = new StringBuilder();13
control.RenderControl(new HtmlTextWriter(new StringWriter(builder1)));14
response.Write("<html><body>" + builder1.ToString() + "</body></html>");15
response.End();16
17
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)18
{19
if (e.Row.RowType == DataControlRowType.DataRow)20
{21
e.Row.Cells[5].Attributes.Add("class", "mystyle2");／／对单元格的样式设置22
}23
}24
（2）假设这里的数据源为Table1
Application excelApp
=
new
ApplicationClass(); Workbook excelBook
=
excelApp.Workbooks.Add(Type.Missing); Worksheet excelSheet
=
(Worksheet)excelBook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelSheet.Name
=
strTitle; excelApp.Visible
=
true
;
int
rowCount
=
Table1.Rows.Count;
int
colCount
=
Table1.Columns.Count;
object
[,] dataArray
=
new
object
[rowCount
+
1
, colCount];
//
二维数组定义是多一个标题行。
for
(
int
j
=
0
; j
<
colCount; j
++
)
{ dataArray[0, j] = Table1.Columns[j].Caption;//导出字段标题。 //根据各列的数据类型设置Excel的格式。 switch (Table1.Columns[j].DataType.ToString())／／格式化单元格的输出格式 { case "System.String": excelSheet.get_Range(excelSheet.Cells[1, 1 + j], excelSheet.Cells[rowCount + 1, 1 + j]).NumberFormatLocal = "@"; break; case "System.Decimal": excelSheet.get_Range(excelSheet.Cells[1, 1 + j], excelSheet.Cells[rowCount + 1, 1 + j]).NumberFormatLocal = "$0.00"; break; case "System.DateTime": excelSheet.get_Range(excelSheet.Cells[1, 1 + j], excelSheet.Cells[rowCount + 1, 1 + j]).NumberFormatLocal = "yyyy-mm-dd"; break; //可以根据自己的需要扩展。 default: excelSheet.get_Range(excelSheet.Cells[1, 1 + j], excelSheet.Cells[rowCount + 1, 1 + j]).NumberFormatLocal = "G/通用格式"; break; } for (int i = 0; i < rowCount; i++) { dataArray[i + 1, j] = Table1.Rows[i][j]; } }
excelSheet.get_Range(
"
A1
"
, excelSheet.Cells[rowCount
+
1
, colCount]).Value2
=
dataArray;
注：使用这种方法的时候需要注意两个地方。（1）必须对本地用户设置权限，可能会出现的错误是：检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败。对这个错误的解决办法将贴在下一篇。（2）导出的行号设置。很多人可能会遇到的错误是：
异常类型: COMException
异常消息: 异常来自 HRESULT:0x800A03EC
这个问题主要是因为导出的行号设置不对所引起的异常。在这个方法中使用到行号的有：
《1》excelSheet.get_Range(excelSheet.Cells[1, 1 + j], excelSheet.Cells[rowCount + 1, 1 + j]).NumberFormatLocal = "@";
《2》excelSheet.get_Range("A1", excelSheet.Cells[rowCount + 1, colCount]).Value2 = dataArray;
需注意的是excelSheet.Cells[1, 1 + j],，如果有标题的情况下，必须把标题行去掉，所以开始行是1,
这样就算结尾了，第一篇文章，请大家多多指教。谢谢。
==============================================================================================
下面是自己的一些实验：
具体的配置过程如下:
1:在服务器上安装Excel.
2:文件工作目录添加everyone用户并且拥有修改的权限.
3:在web.config文件中加入:
<identity impersonate="true"/>
C#-Doce //上传excel文件
protected void flUpload_Click(object sender, EventArgs e)
{
if (txtFile.FileContent.Length > 0)
{
string fileName = txtFile.FileName;
string extensionName = Path.GetExtension(fileName).ToLower();
if (extensionName.Equals(".xls") || extensionName.Equals(".xlsx"))
{
fileName = Path.GetFileNameWithoutExtension(fileName) + DateTime.Now.ToString("-yyyyMMddhhmmss") + extensionName;
string physicalPath = Request.PhysicalApplicationPath + "ExcelFiles\\";
if (Directory.Exists(physicalPath))
Directory.CreateDirectory(physicalPath);
string mapPath = Server.MapPath(YQKC.Common.WebUtility.app() + "ExcelFiles/" + fileName);
FileInfo fi = new FileInfo(mapPath);
txtFile.SaveAs(mapPath);
GetExcelContent(mapPath, extensionName);
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('格式不正确！');", true);
}
}
}
//导入
private void GetExcelContent(string filepath, string extensionName)
{
try
{
string connString = (extensionName.Equals("xls"))
? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString);
conn.Open();
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT * FROM [盘点单$]", conn);
DataTable dt = (DataTable)Session["DataTable"];
DataRow dr;
for (int ii = 0; ii < int.Parse(hdfrindex.Value); ii++)
{
TextBox tb0 = (TextBox)GVList.Rows[ii].FindControl("Txtsl");
TextBox tb1 = (TextBox)GVList.Rows[ii].FindControl("Txtmxbz");
TextBox tb2 = (TextBox)GVList.Rows[ii].FindControl("Txtzmkc");
TextBox tb3 = (TextBox)GVList.Rows[ii].FindControl("Txtgx");
HiddenField hd = (HiddenField)GVList.Rows[ii].FindControl("HDGX");
if (dt.Rows[ii]["rIndex"].ToString() != "-1")
{
dt.Rows[ii]["Quantity"] = tb0.Text.Trim(); dt.Rows[ii]["OI"] = tb1.Text.Trim();
dt.Rows[ii]["BQ"] = tb2.Text.Trim();
}//写入数量
}
DataRow[] drs = dt.Select("rIndex = '-1'");
for (int ii = 0; ii < drs.Length; ii++)
{
dt.Rows.Remove(drs[ii]);
}
int cIndex = int.Parse(hdfcindex.Value);//修改时的行号
int gIndex = int.Parse(hdfrindex.Value);//新增时的行号
gIndex = gIndex < cIndex ? cIndex : gIndex;//修正gIndex
int i = gIndex > cIndex - 1 && gIndex > 0 ? cIndex : gIndex;//当前编辑行号
using (System.Data.OleDb.OleDbDataReader sdr = command.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
string id = sdr[0].ToString();
if (id.Equals("")) { continue; }
if (dt.Select("GoodsID='" + id + "'").Length > 0) { continue; }
dr = dt.NewRow();
dr["ID"] = "0";
dr["GoodsID"] = id;
dr["Code"] = sdr[1].ToString();
dr["Name"] = sdr[2].ToString();
dr["Unit"] = sdr[3];
dr["Model"] = sdr[4].ToString();
dr["BQ"] = sdr[5].ToString();
dr["Quantity"] = sdr[6].ToString();
dr["ImgNum"] = sdr[7].ToString();
dr["OI"] = sdr[8].ToString();
dr["rIndex"] = i;
dt.Rows.Add(dr);
i++;
}
}
}
gIndex = i;
gIndex = gIndex > cIndex ? gIndex : gIndex + 1;
hdfrindex.Value = gIndex.ToString();//保存当前的gIndex
if (dt.Rows[dt.Rows.Count - 2]["rIndex"].ToString() != "-1" && gIndex >= dt.Rows.Count)
{
dt = addRows(dt);//满10行自动增加1行
}
if (dt.Rows.Count < 12)
{
int counts = 12 - dt.Rows.Count;
for (int j = 0; j < counts; j++)
{
dt = addRows(dt);//满10行自动增加1行
}
}
dt.Dispose();
Session["DataTable"] = dt;
GVList.DataSource = (DataTable)Session["DataTable"];
GVList.DataBind();
conn.Close();
}
catch (Exception)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('导入失败！')", true);
}
}
//导出
protected void lkbOutExcel_Click(object sender, EventArgs e)
{
GridViewToExcel(GVList, "盘点单");
}
#region
/// <summary>
/// 将GridView中的数据导入到Excel中
/// </summary>
/// <param name="datagridview">GridView</param>
/// <param name="SheetName">Excel sheet title</param>
public void GridViewToExcel(GridView datagridview, string SheetName)
{
GC.Collect();
int iRows = 0;
int iCols = 0;
int iTrueCols = 0;
int notEmptyRow = 0;
iTrueCols = datagridview.Columns.Count;
iRows = datagridview.Rows.Count;
for (int i = 0; i < datagridview.Columns.Count; i++)
{
if (datagridview.Columns[i].Visible) iCols++;
}
string[,] dimArray = new string[iRows, iCols];
dimArray[0, 0] = "ID";
for (int j = 1, k = 1; j < iTrueCols; j++)
{
if (datagridview.Columns[j].Visible)
{
dimArray[0, k] = datagridview.Columns[j].HeaderText;
k++;
}
}
for (int i = 0; i < iRows - 1; i++)
{
if (!datagridview.DataKeys[i]["rIndex"].ToString().Equals("-1"))
{
dimArray[i + 1, 0] = datagridview.DataKeys[i]["GoodsID"].ToString();
dimArray[i + 1, 1] = datagridview.Rows[i].Cells[1].Text;
dimArray[i + 1, 2] = ((TextBox)datagridview.Rows[i].FindControl("Txtmc")).Text;
dimArray[i + 1, 3] = datagridview.Rows[i].Cells[3].Text;
dimArray[i + 1, 4] = datagridview.Rows[i].Cells[4].Text;
dimArray[i + 1, 5] = ((TextBox)datagridview.Rows[i].FindControl("Txtzmkc")).Text;
dimArray[i + 1, 6] = ((TextBox)datagridview.Rows[i].FindControl("Txtsl")).Text;
dimArray[i + 1, 7] = ((TextBox)datagridview.Rows[i].FindControl("TxtImgNum")).Text;
dimArray[i + 1, 8] = ((TextBox)datagridview.Rows[i].FindControl("Txtmxbz")).Text;
notEmptyRow++;
}
}
if (dimArray.Length > 0)
{
excel::Application app = null;
excel::Workbook wb = null;
excel::Worksheet ws = null;
try
{
app = new excel::Application();
wb = app.Workbooks.Add(Missing.Value);
if (wb.Worksheets.Count > 0)
{
ws = (excel::Worksheet)wb.Worksheets.get_Item(1);
}
else
{
wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ws = (excel::Worksheet)wb.Worksheets.get_Item(1);
}
if (ws != null)
{
if (SheetName.Trim() != "")
{
ws.Name = SheetName;
}
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows, iCols]).Value2 = dimArray;
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Font.Bold = true;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows, iCols]).Font.Size = 10.0;
ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows, iCols]).RowHeight = 14.25;
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Select();
ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Columns.AutoFit();
}
app.Visible = false;
string path = HttpContext.Current.Server.MapPath(".") + "\\" + SheetName + DateTime.Now.ToString("-yyyyMMddhhmmss") + ".xls";
wb.SaveCopyAs(path);
wb.Close(false, null, null);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
wb = null;
app = null;
ws = null;
GC.Collect();
FileInfo file = new FileInfo(path);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpContext.Current.Server.UrlEncode(file.Name));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(file.FullName);
Response.End();
}
catch (Exception ex) { ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('导出失败！');", true); }
finally
{
if (app != null)
{
wb.Close(false, null, null);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
wb = null;
app = null;
ws = null;
GC.Collect();
}
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('数据为空，请输录入数据！');", true);
}
}
#endregion
以上代码可以成功导入导出excel，当然是用com组件读取excel也可以实现同样功能，具体代码就不写了，核心代码如下： C#-Code /// <summary>
/// 读取一个单元格
/// </summary>
/// <param name="row">行</param>
/// <param name="column">列</param>
/// <returns></returns>
public string ReadText(int row, int column)
{
try
{
Excel.Range range = (Excel.Range)worksheet.Cells[row, column];
return (string)range.Text;
}
catch
{
return "";
}
}
总结：com组件的导入导出方式比较麻烦，代码也比较复杂，但却是对excel的底层操作，可以设置excel的列宽，字体样式，对齐方式等，导出的是真正excel，这点还是让人比较欣慰的。。。
412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
