求教一个asp.net下读取excel文件的问题 请教各位大大,最近在做一个项目,要求用Excel制作一个表头(表头包含合并行和列),通过读取excel文件将表头读为一个字符串,字符串中要包含单元格的信息(如A1~A4列为合并单元格“姓名”,则记录为#姓名,row1,col4;若A1~B2为合并单元格“性别”,则记录为#性别,row2,col2),请问如何能读取excel合并单元格的信息? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 三种解决方案public partial class Tracking_downloadToExcel : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { common.CheckIsAllowed(); string strCondition = (Session["downLoadSQL_Condition"] == null ? "" : Session["downLoadSQL_Condition"].ToString()); SqlHelper sqlh = new SqlHelper(); DataSet ds = new DataSet("ExportData"); Int32 iTotal = 0; ds = sqlh.ExecuteDataSet("select b.EcouponName as '优惠券名称', sum(a.trackingCount) as '监测数量' from TrackingDetail a, Ecoupon b where " + strCondition + " GROUP BY EcouponName ORDER BY Sum(TrackingCount) DESC "); if (ds.Tables.Count > 0) { if (ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) iTotal += Convert.ToInt32(ds.Tables[0].Rows[i]["监测数量"].ToString()); } else { return; } DataRow datarow = ds.Tables[0].NewRow(); datarow["优惠券名称"] = "合计: 共" + ds.Tables[0].Rows.Count.ToString() + "张优惠券"; datarow["监测数量"] = iTotal; // datarow["addtime"] = DBNull.Value; ds.Tables[0].Rows.Add(datarow); //string strGuid = Page.Request.Url.Query.Substring (1); string fileName = "" + Guid.NewGuid().ToString() + ".xls"; string serverPath = Server.MapPath("../") + "Upload//Excel//"; //判断路径是否存在,不存在则创建 //Response.Write(serverPath); if (!System.IO.Directory.Exists(serverPath)) { System.IO.Directory.CreateDirectory(serverPath); } //string serverPath = Request.PhysicalPath ; this.ExportToXls(serverPath + fileName, ds); Page.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Page.Response.WriteFile(serverPath + fileName); } else { return; } } #region 导出数据到Excel文件(第三种解决方案) /// <summary> /// 导出数据到Excel文件 /// </summary> /// <author>tianzm</author> /// <param name="saveFileName">存储的文件名</param> /// <param name="dt">数据表</param> /// <returns>bool</returns> /// <history>2005-05-21</history> private bool ExportToXls(string saveFileName, DataSet ds) { //Path. if (File.Exists(saveFileName)) { File.Delete(saveFileName); } string strPath = saveFileName.Substring(0, saveFileName.Length - 4); string strXml = strPath + ".xml";//xml文件 string strXsl = strPath + ".xsl";//xsl形式 string strXls = strPath + ".xls";//excel文件 try { GetXmlFile(ds, strXml); GetXSLFile(ds, strXsl); //Excel转换 XmlDocument doc = new XmlDocument(); doc.Load(strXml); XslTransform xslt = new XslTransform(); xslt.Load(strXsl); XmlElement root = doc.DocumentElement; XPathNavigator nav = root.CreateNavigator(); XmlTextWriter writer = new XmlTextWriter(strXls, null); xslt.Transform(nav, null, writer, null); writer.Close(); File.Delete(strXml); File.Delete(strXsl); return true; } catch (Exception ex) { throw (new Exception(ex.Message)); } } #endregion #region 根据数据集,生成替换后的xml文件 /// <summary> /// 根据数据集,生成替换后的xml文件 /// </summary> /// <param name="ds">数据集合</param> /// <param name="XmlFilePath">xml文件路径</param> private void GetXmlFile(DataSet ds, string XmlFilePath) { string strXml = ds.GetXml(); if (File.Exists(XmlFilePath)) { File.Delete(XmlFilePath); } FileStream fs1 = File.Create(XmlFilePath); StreamWriter writer = new StreamWriter(fs1); writer.Write(strXml); writer.Close(); fs1.Close(); } #endregion #region 创建转换格式文件(XSL) /// <summary> /// 创建转换格式文件(XSL) /// </summary> /// <param name="ds">要导出的数据集</param> /// <param name="XslPath">xsl文件存放路径</param> private void GetXSLFile(DataSet ds, string XslPath) { string strColumn = ""; string strRow = ""; string dsName = ds.DataSetName; string tableName = ds.Tables[0].TableName; string header = dsName + "/" + tableName; foreach (DataColumn clm in ds.Tables[0].Columns) { //特殊字符 <,>,",*,%,(,),& 替换 //************************************************* //************************************************* // 符号 xml下的值 excel中的值 // < -------- _x003C_ ------ < // > -------- _x003E_ ------ > // " -------- _x0022_ ------ " // * -------- _x002A_ ------ * // % -------- _x0025_ ------ % // & -------- _x0026_ ------ & // ( -------- _x0028_ ------ ( // ) -------- _x0029_ ------ ) // = -------- _x003D_ ------ = //************************************************* //************************************************* string strClmName = clm.ColumnName; string strRowName = clm.ColumnName; if (strClmName.IndexOf("&") != -1) strClmName = strClmName.Replace("&", "&"); if (strClmName.IndexOf("<") != -1) strClmName = strClmName.Replace("<", "<"); if (strClmName.IndexOf(">") != -1) strClmName = strClmName.Replace(">", ">"); if (strClmName.IndexOf("\"") != -1) strClmName = strClmName.Replace("\"", """); if (strRowName.IndexOf("<") != -1) strRowName = strRowName.Replace("<", "_x003C_"); if (strRowName.IndexOf(">") != -1) strRowName = strRowName.Replace(">", "_x003E_"); if (strRowName.IndexOf("\"") != -1) strRowName = strRowName.Replace("\"", "_x0022_"); if (strRowName.IndexOf("*") != -1) strRowName = strRowName.Replace("*", "_x002A_"); if (strRowName.IndexOf("%") != -1) strRowName = strRowName.Replace("%", "_x0025_"); if (strRowName.IndexOf("&") != -1) strRowName = strRowName.Replace("&", "_x0026_"); if (strRowName.IndexOf("(") != -1) strRowName = strRowName.Replace("(", "_x0028_"); if (strRowName.IndexOf(")") != -1) strRowName = strRowName.Replace(")", "_x0029_"); if (strRowName.IndexOf("=") != -1) strRowName = strRowName.Replace("=", "_x003D_"); strColumn += "<th><div style=\"font-family:宋体\">" + strClmName + "</div></th>" + "\r\n"; strRow += "<td><div style=\"font-family:宋体\">" + "<xsl:value-of select=" + "\"" + strRowName + "\"" + "/>" + "</div></td>" + "\r\n"; } string str = @"<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform""> <xsl:template match=""/""> <html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns=""http://www.w3.org/TR/REC-html40""> <head> <meta http-equiv=""Content-Type"" content=""text/html;charset=utf-8"" /> <style> .xl24{mso-style-parent:style0;mso-number-format:""\@"";text-align:right;} </style> <xml> <x:ExcelWorkbook> <x:ExcelWorksheets> <x:ExcelWorksheet> <x:Name>Sheet1</x:Name> <x:WorksheetOptions> <x:ProtectContents>False</x:ProtectContents> <x:ProtectObjects>False</x:ProtectObjects> <x:ProtectScenarios>False</x:ProtectScenarios> </x:WorksheetOptions> </x:ExcelWorksheet> </x:ExcelWorksheets> </x:ExcelWorkbook> </xml> </head> <body> "; str += "\r\n" + @"<table border=""1"" cellpadding=""0"" cellspacing=""0""> <tr>" + "\r\n"; str += strColumn; str += @" </tr> <xsl:for-each select=""" + header + @"""> <tr>"; str += "\r\n" + strRow; str += @"</tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet> "; string path = XslPath; if (File.Exists(path)) { File.Delete(path); } FileStream fs = File.Create(path); StreamWriter sw = new StreamWriter(fs); sw.Write(str); sw.Close(); fs.Close(); } #endregion} 楼上大大的意思是先把excel转换为xsl,再读取相关信息吗?可是我对xsl操作不太熟悉,有没其他简单点的办法? 操作office,地球人用npoi,一位前辈说的 <Worksheet ss:Name="Sheet1"> <Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"> <Row> <Cell ss:MergeDown="2" ss:StyleID="s64"><Data ss:Type="String">时间</Data></Cell> <Cell ss:MergeAcross="5" ss:StyleID="s64"><Data ss:Type="String">基本信息</Data></Cell> <Cell ss:MergeAcross="1" ss:MergeDown="1" ss:StyleID="s64"><Data ss:Type="String">其他信息</Data></Cell> </Row> <Row> <Cell ss:Index="2" ss:MergeDown="1" ss:StyleID="s64"><Data ss:Type="String">日期</Data></Cell> <Cell ss:MergeAcross="2" ss:StyleID="s64"><Data ss:Type="String">个人信息</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s64"><Data ss:Type="String">单位信息</Data></Cell> </Row> <Row> <Cell ss:Index="3" ss:StyleID="s62"><Data ss:Type="String">姓名</Data></Cell> <Cell ss:StyleID="s62"><Data ss:Type="String">性别</Data></Cell> <Cell ss:StyleID="s62"><Data ss:Type="String">年龄</Data></Cell> <Cell ss:StyleID="s62"><Data ss:Type="String">单位名</Data></Cell> <Cell ss:StyleID="s62"><Data ss:Type="String">邮编</Data></Cell> <Cell ss:StyleID="s62"><Data ss:Type="String">备注1</Data></Cell> <Cell ss:StyleID="s62"><Data ss:Type="String">备注2</Data></Cell> </Row> </Table>这个是用excel转换出来的xml文件,请问如何获取比如上面<Cell ss:MergeDown="2" ss:StyleID="s64"><Data ss:Type="String">时间</Data></Cell>的信息,转换为#时间,row1,col2这个string呢 viewstate为什么绑定不上值 有什么办法让服务器自动删除过期数据? 问个验证控件冲突的问题 关于DropDownList的一个奇怪问题 新人急问:APS转asp.net马上结帖 自定义控件的问题. 怎么做到识别密码的大小写 刚刚用asp.net/c#+mssql做了个网站,大家给点意见 求助!!! asp.net工具 如何在网页上将硬盘某个目录下的文件以多种(列表、缩略图、图标)方式显示 请问GDI+怎么样操作gif格式的图片
public partial class Tracking_downloadToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
common.CheckIsAllowed();
string strCondition = (Session["downLoadSQL_Condition"] == null ? "" : Session["downLoadSQL_Condition"].ToString()); SqlHelper sqlh = new SqlHelper();
DataSet ds = new DataSet("ExportData");
Int32 iTotal = 0;
ds = sqlh.ExecuteDataSet("select b.EcouponName as '优惠券名称', sum(a.trackingCount) as '监测数量' from TrackingDetail a, Ecoupon b where " + strCondition + " GROUP BY EcouponName ORDER BY Sum(TrackingCount) DESC ");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
iTotal += Convert.ToInt32(ds.Tables[0].Rows[i]["监测数量"].ToString());
}
else
{
return;
}
DataRow datarow = ds.Tables[0].NewRow();
datarow["优惠券名称"] = "合计: 共" + ds.Tables[0].Rows.Count.ToString() + "张优惠券";
datarow["监测数量"] = iTotal;
// datarow["addtime"] = DBNull.Value;
ds.Tables[0].Rows.Add(datarow); //string strGuid = Page.Request.Url.Query.Substring (1);
string fileName = "" + Guid.NewGuid().ToString() + ".xls"; string serverPath = Server.MapPath("../") + "Upload//Excel//";
//判断路径是否存在,不存在则创建
//Response.Write(serverPath);
if (!System.IO.Directory.Exists(serverPath))
{
System.IO.Directory.CreateDirectory(serverPath);
} //string serverPath = Request.PhysicalPath ; this.ExportToXls(serverPath + fileName, ds); Page.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Page.Response.WriteFile(serverPath + fileName); }
else
{
return;
}
} #region 导出数据到Excel文件(第三种解决方案)
/// <summary>
/// 导出数据到Excel文件
/// </summary>
/// <author>tianzm</author>
/// <param name="saveFileName">存储的文件名</param>
/// <param name="dt">数据表</param>
/// <returns>bool</returns>
/// <history>2005-05-21</history>
private bool ExportToXls(string saveFileName, DataSet ds)
{
//Path.
if (File.Exists(saveFileName))
{
File.Delete(saveFileName);
}
string strPath = saveFileName.Substring(0, saveFileName.Length - 4);
string strXml = strPath + ".xml";//xml文件
string strXsl = strPath + ".xsl";//xsl形式
string strXls = strPath + ".xls";//excel文件 try
{
GetXmlFile(ds, strXml);
GetXSLFile(ds, strXsl); //Excel转换
XmlDocument doc = new XmlDocument();
doc.Load(strXml);
XslTransform xslt = new XslTransform();
xslt.Load(strXsl);
XmlElement root = doc.DocumentElement;
XPathNavigator nav = root.CreateNavigator();
XmlTextWriter writer = new XmlTextWriter(strXls, null);
xslt.Transform(nav, null, writer, null);
writer.Close();
File.Delete(strXml);
File.Delete(strXsl);
return true;
}
catch (Exception ex)
{
throw (new Exception(ex.Message));
}
}
#endregion #region 根据数据集,生成替换后的xml文件
/// <summary>
/// 根据数据集,生成替换后的xml文件
/// </summary>
/// <param name="ds">数据集合</param>
/// <param name="XmlFilePath">xml文件路径</param>
private void GetXmlFile(DataSet ds, string XmlFilePath)
{
string strXml = ds.GetXml();
if (File.Exists(XmlFilePath))
{
File.Delete(XmlFilePath);
}
FileStream fs1 = File.Create(XmlFilePath);
StreamWriter writer = new StreamWriter(fs1);
writer.Write(strXml);
writer.Close();
fs1.Close();
}
#endregion #region 创建转换格式文件(XSL)
/// <summary>
/// 创建转换格式文件(XSL)
/// </summary>
/// <param name="ds">要导出的数据集</param>
/// <param name="XslPath">xsl文件存放路径</param>
private void GetXSLFile(DataSet ds, string XslPath)
{
string strColumn = "";
string strRow = "";
string dsName = ds.DataSetName;
string tableName = ds.Tables[0].TableName;
string header = dsName + "/" + tableName;
foreach (DataColumn clm in ds.Tables[0].Columns)
{
//特殊字符 <,>,",*,%,(,),& 替换
//*************************************************
//*************************************************
// 符号 xml下的值 excel中的值
// < -------- _x003C_ ------ <
// > -------- _x003E_ ------ >
// " -------- _x0022_ ------ "
// * -------- _x002A_ ------ *
// % -------- _x0025_ ------ %
// & -------- _x0026_ ------ &
// ( -------- _x0028_ ------ (
// ) -------- _x0029_ ------ )
// = -------- _x003D_ ------ =
//*************************************************
//************************************************* string strClmName = clm.ColumnName;
string strRowName = clm.ColumnName; if (strClmName.IndexOf("&") != -1)
strClmName = strClmName.Replace("&", "&");
if (strClmName.IndexOf("<") != -1)
strClmName = strClmName.Replace("<", "<");
if (strClmName.IndexOf(">") != -1)
strClmName = strClmName.Replace(">", ">");
if (strClmName.IndexOf("\"") != -1)
strClmName = strClmName.Replace("\"", """); if (strRowName.IndexOf("<") != -1)
strRowName = strRowName.Replace("<", "_x003C_");
if (strRowName.IndexOf(">") != -1)
strRowName = strRowName.Replace(">", "_x003E_");
if (strRowName.IndexOf("\"") != -1)
strRowName = strRowName.Replace("\"", "_x0022_");
if (strRowName.IndexOf("*") != -1)
strRowName = strRowName.Replace("*", "_x002A_");
if (strRowName.IndexOf("%") != -1)
strRowName = strRowName.Replace("%", "_x0025_");
if (strRowName.IndexOf("&") != -1)
strRowName = strRowName.Replace("&", "_x0026_");
if (strRowName.IndexOf("(") != -1)
strRowName = strRowName.Replace("(", "_x0028_");
if (strRowName.IndexOf(")") != -1)
strRowName = strRowName.Replace(")", "_x0029_");
if (strRowName.IndexOf("=") != -1)
strRowName = strRowName.Replace("=", "_x003D_");
strColumn += "<th><div style=\"font-family:宋体\">" + strClmName + "</div></th>" + "\r\n";
strRow += "<td><div style=\"font-family:宋体\">" + "<xsl:value-of select=" + "\"" + strRowName + "\"" + "/>" + "</div></td>" + "\r\n";
}
string str = @"<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">
<xsl:template match=""/"">
<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns=""http://www.w3.org/TR/REC-html40"">
<head>
<meta http-equiv=""Content-Type"" content=""text/html;charset=utf-8"" />
<style>
.xl24{mso-style-parent:style0;mso-number-format:""\@"";text-align:right;}
</style>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
</head>
<body> ";
str += "\r\n" + @"<table border=""1"" cellpadding=""0"" cellspacing=""0"">
<tr>" + "\r\n";
str += strColumn;
str += @" </tr>
<xsl:for-each select=""" + header + @""">
<tr>";
str += "\r\n" + strRow;
str += @"</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet> "; string path = XslPath;
if (File.Exists(path))
{
File.Delete(path);
}
FileStream fs = File.Create(path);
StreamWriter sw = new StreamWriter(fs);
sw.Write(str);
sw.Close();
fs.Close();
}
#endregion
}
<Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="3" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Row>
<Cell ss:MergeDown="2" ss:StyleID="s64"><Data ss:Type="String">时间</Data></Cell>
<Cell ss:MergeAcross="5" ss:StyleID="s64"><Data ss:Type="String">基本信息</Data></Cell>
<Cell ss:MergeAcross="1" ss:MergeDown="1" ss:StyleID="s64"><Data
ss:Type="String">其他信息</Data></Cell>
</Row>
<Row>
<Cell ss:Index="2" ss:MergeDown="1" ss:StyleID="s64"><Data ss:Type="String">日期</Data></Cell>
<Cell ss:MergeAcross="2" ss:StyleID="s64"><Data ss:Type="String">个人信息</Data></Cell>
<Cell ss:MergeAcross="1" ss:StyleID="s64"><Data ss:Type="String">单位信息</Data></Cell>
</Row>
<Row>
<Cell ss:Index="3" ss:StyleID="s62"><Data ss:Type="String">姓名</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">性别</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">年龄</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">单位名</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">邮编</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">备注1</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">备注2</Data></Cell>
</Row>
</Table>
这个是用excel转换出来的xml文件,请问如何获取比如上面<Cell ss:MergeDown="2" ss:StyleID="s64"><Data ss:Type="String">时间</Data></Cell>的信息,转换为#时间,row1,col2这个string呢