DataSet 读写 Excel......
RT 我想在b/s里 对一个DataSet 读写到一个Excel 里面...并且保存到客户端...或者让他们下载出来...
新手...看了一些资料 都没头没尾的...看的头疼...最后我直接 dataset.writexml(.xls) 了 但是格式保存出来木有字段的 表头.... 而且不知道如何保存到 用户那里...
大家有什么方法能把一个dataset 保存成一个excel嘛... 求帮忙给思路或者可以参考的材料 谢谢大家了》》》
表示操作的恶心..... 还有dom在用户那里 window.open 打不开该怎吗办？？ 失效了..windows.close(); 好使？
[解决办法]
下面是一段Datagrid导出excel的源码,你参考一下:
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (DataGrid1 != null)
{
curContext.Response.Clear();
curContext.Response.Buffer = true;
// 设置了类型为中文防止乱码的出现
curContext.Response.Charset = "GB2312";
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
// 设置输出流为简体中文
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
// 定义输出文件和文件名
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "QueryResult" + ".xls");
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
this.EnableViewState = false;
// 导出excel文件
strWriter = new System.IO.StringWriter(myCItrad);
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
//dgExport = new System.Web.UI.WebControls.DataGrid();
//dgExport.DataSource = userEntity.EntityDataSet.Tables[0].DataSet;
//dgExport.AllowPaging = false;
//dgExport.DataBind();
// 返回客户端
DataGrid1.AllowPaging = false;
DataGrid1.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End()
}
[解决办法]
http://blog.csdn.net/highplayer/article/details/5662169
[解决办法]
建议你使用 NPOI 导出
先下载NPOI.dll
然后
/// <summary>
/// 先调用这个方法
/// </summary>
/// <param name="SourceTable">数据源</param>
/// <param name="FileName">文件名</param>
public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
{
try
{
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));
MemoryStream ms = RenderDataTableToExcel(SourceTable,FileName,1) as MemoryStream;
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}
catch (Exception e)
{
WebMessageBox.Show("执行出错！错误原因是：" + e.Message.ToString());
}
}
/// <summary>
///
/// </summary>
/// <param name="SourceTable">数据源</param>
/// <returns>生成的数据流</returns>
public static Stream RenderDataTableToExcel(DataTable SourceTable,string filename,int type)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet(filename.Substring(0,filename.LastIndexOf('.'))) as HSSFSheet;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
sheet.DefaultColumnWidth =13;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
// handling header.
foreach (DataColumn column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in SourceTable.Columns)
{
// dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
if (drValue == null
[解决办法]
drValue == "")
{
newCell.SetCellValue("");
}
else
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
}
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return ms;
}
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelToDS.aspx.cs" Inherits="MyWebSiteTest.ExcelToDS" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Excel 导入到DataSet Demo</title>
<style type="text/css">
table{ background-color:#eee; font-size:12px; color:#666; font-family:Arial,微软雅黑;}
table tr td{ background-color:#fff; padding:4px 10px;}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="0" cellspacing="1">
<tr><td colspan="2" style="color:#e26f09;"><%=TipInfo() %></td></tr>
<tr>
<td style="width:100px;"><asp:FileUpload ID="FileUpload1" runat="server" /></td>
<td>
<asp:Button runat="server" ID="btnInputExcel" Text="导入Excel" style="height:22px;" OnClick="btnInputExcel_Click" />
<asp:Button runat="server" ID="btnReloadFile" Visible="false" Text="更换附件" style="height:22px;" OnClick="btnReloadFile_Click" />
</td>
</tr>
<%=(Session["employee_entran_filename"] != null && Session["employee_entran_filename"].ToString() != "") ?
"<tr><td colspan="2">" + Session["employee_entran_filename"].ToString() + "</td></tr>" : ""%>
</table>
</div>
</form>
</body>
</html>
...略
//
using System.Data.OleDb;
using System.Data;
using System.Data.OracleClient;
using System.Text;
namespace MyWebSiteTest
{
public partial class ExcelToDS : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Session["employee_entran_filename"] != null && Session["employee_entran_filename"].ToString() != "")
{
this.btnInputExcel.Visible = false;
this.btnReloadFile.Visible = true;
}
}
/// <summary>
/// 上传应聘登记表电子档附件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnInputExcel_Click(object sender, EventArgs e)
{
string fullname = FileUpload1.PostedFile.FileName;
if (string.IsNullOrEmpty(fullname))
return;
string name = fullname.Substring(fullname.LastIndexOf('\\') + 1).Replace(".", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + ".");
string type = fullname.Substring(fullname.LastIndexOf('.')).ToLower();
if (type == ".xlsx"
[解决办法]
type == ".xls")
{
Session["upfileError"] = "5";
string savepath = Server.MapPath("~/UploadFiles/Hr/");
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(savepath);
if (!dir.Exists)//检查构建好的路径，没有就创建该文件夹
dir.Create();
Session["employee_entran_filename"] = "~/UploadFiles/Hr/" + name;//记录到session中
this.FileUpload1.PostedFile.SaveAs(savepath + "\" + name);
DataSet tempDs = ExcelReader(savepath + name);
if (tempDs != null && tempDs.Tables.Count > 0 && tempDs.Tables[0].Rows.Count > 0)
{
Session["EntranEmployee_AttechInfo"] = tempDs as object;
this.btnInputExcel.Visible = false;
this.btnReloadFile.Visible = true;
Session["upfileError"] = "6";
}
else
Session["upfileError"] = "2";
}
else
Session["upfileError"] = "4";
}
/// <summary>
/// 重新上传
/// </summary>
protected void btnReloadFile_Click(object sender, EventArgs e)
{
Session.Remove("upfileError");
Session.Remove("EntranEmployee_AttechInfo");
//清除文件
if (Session["employee_entran_filename"] != null && Session["employee_entran_filename"].ToString() != "")
{
string _delPath = Session["employee_entran_filename"].ToString();
if (_delPath != "")
if (System.IO.File.Exists(Server.MapPath(_delPath)))
System.IO.File.Delete(Server.MapPath(_delPath));
}
Session.Remove("employee_entran_filename");
this.btnInputExcel.Visible = true;
this.btnReloadFile.Visible = false;
}
/// <summary>
/// 读取Excel数据到DS
/// </summary>
/// <param name="excelName">xls文件路径(服务器物理路径)</param>
/// <returns>Excel转化的DataSet</returns>
public DataSet ExcelReader(string excelName)
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelName.Replace(@"\", "\") + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
//string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection objConn = new OleDbConnection(strConn);
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);// 取得Excel工作簿中所有工作表
OleDbDataAdapter sqlada = new OleDbDataAdapter();
DataSet ds = new DataSet();
try
{
foreach (DataRow dr in schemaTable.Rows)
{
string strSql = "Select * From [" + dr[2].ToString().Trim() + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
sqlada.SelectCommand = objCmd;
sqlada.Fill(ds, dr[2].ToString().Trim());
}
}
catch (Exception ex)
{
Session["upfileError"] = "2";
throw new Exception(ex.Message);
}
finally
{
objConn.Close();
}
return ds;
}
/// <summary>
/// 错误提示
/// </summary>
/// <returns>错误信息</returns>
public string TipInfo()
{
int errType = 3;
if (Session["upfileError"] != null && Session["upfileError"].ToString() != "")
errType = int.Parse(Session["upfileError"].ToString());
string str = string.Empty;
switch (errType)
{
case 1:
str = "※当前文件路径获取失败，请更换IE浏览器重试";
break;
case 2:
str = "※文件数据读取失败，请重试";
break;
case 3:
str = "※应聘登记表电子档附件应为.xls、.xlsx类型";
break;
case 4:
str = "※您上传的附件类型不正确，请更换附件";
break;
case 5:
str = "※当前选择的附件符合上传要求";
break;
case 6:
str = "<span style="color:green;">※文件数据读取成功</span>";
break;
}
return str;
}
}
}
