转载自: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(“文件名”, “文件编码”);//如果文件名为中文格式,则可用这行代码对文件名进行编码。然后得用底下带(¥¥¥)行对其进行反编码,这样输出的文件名就不会是乱码。当然使用的编码要正确。
1HttpResponse 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(“文件名”, “文件编码”)); //inline
6 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
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,这点还是让人比较欣慰的。。。