不调用com组件,实现格式化Excel数据导出
方法1：我们常用的导出Excel，但是无法实现对于Excel表里面内容进行格式化处理。
如果格式化处理一般就要操作com组件,但是感觉总有点后遗症(进程无法关闭等等问题)。
Code//以流的形式向客户端输出
public void CreateExcel(System.Data.DataTable dt)
{
StringWriter sw = new StringWriter();
string rowStr = "";
//取所有列名
for (int i = 0; i < dt.Columns.Count; i++)
{
rowStr = rowStr + dt.Columns[i] + "\t";
}
sw.WriteLine(rowStr);
//取每行数据
for (int j = 0; j < dt.Rows.Count; j++)
{
rowStr = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
rowStr = rowStr + dt.Rows[j][i].ToString() + "\t";
}
sw.WriteLine(rowStr);
}
sw.Close();
string filename = HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMdd-HHmm"));
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
方法2：实现对复杂格式的Excel表导出。
打开一个Excel表另存为，发现保存类型里面有个【XML表格】选项.
保存以后用记事本打开发现都是XMl数据格式,而且发现把后缀名xml直接改为xls就变成原先的Excel表。
我们就会想,如果我们在服务器输出这种格式的文件,是不是就可以不操作com组建,导出复杂格式的Excel表了。
下面是xml格式的Excel表:
Code<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>自动生成</Author>
<LastAuthor>自动生成</LastAuthor>
<LastPrinted>2009-06-29T02:48:02Z</LastPrinted>
<Created>2009-06-22T00:48:00Z</Created>
<LastSaved>2009-09-16T07:56:36Z</LastSaved>
<Company>服务器</Company>
<Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>10245</WindowHeight>
<WindowWidth>13875</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>60</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="m20988562">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988572">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988582">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988592">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988602">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988612">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988622">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988632">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="m20988642">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="黑体" x:CharSet="134" ss:Size="12"/>
</Style>
<Style ss:ID="s21">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
</Style>
<Style ss:ID="s22">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
</Style>
<Style ss:ID="s24">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="方正小标宋简体" x:CharSet="134" ss:Size="22"/>
</Style>
<Style ss:ID="s34">
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="10.5"/>
</Style>
<Style ss:ID="s35">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="10.5"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Names>
<NamedRange ss:Name="Print_Titles" ss:RefersTo="=Sheet1!R2:R3"/>
</Names>
<Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="4" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="35.0625">
<Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="213.75"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="99.75"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="56.25"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="71.25"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="42"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="39.75"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="81"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="90"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0" ss:Width="57"/>
<Row ss:AutoFitHeight="0" ss:Height="33.75">
<Cell ss:MergeAcross="8" ss:StyleID="s24"><Data ss:Type="String">公文类信息目录备案表</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="18">
<Cell ss:MergeDown="1" ss:StyleID="m20988562"><Data ss:Type="String">标 题</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988572"><Data ss:Type="String">文 号</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988582"><Data ss:Type="String">成文日期</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988592"><Data ss:Type="String">发文机关 (默认)</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988602"><Data ss:Type="String">公文 种类</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988612"><Data ss:Type="String">公开 属性</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988622"><Data ss:Type="String">免予公开理由</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988632"><Data ss:Type="String">主题词</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
<Cell ss:MergeDown="1" ss:StyleID="m20988642"><Data ss:Type="String">是否为规范性文件 (自己填)</Data><NamedCell
ss:Name="Print_Titles"/></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="16.5"/>
<Row ss:AutoFitHeight="0" ss:Height="39.9375">
<Cell ss:StyleID="s34"><Data ss:Type="String">关于印发《XXXXXXX》的通知</Data></Cell>
<Cell ss:StyleID="s35"><Data ss:Type="String"> 1号</Data></Cell>
<Cell ss:StyleID="s35"><Data ss:Type="String">09.01.05</Data></Cell>
<Cell ss:StyleID="s35"><Data ss:Type="String">默默机关</Data></Cell>
<Cell ss:StyleID="s35"><Data ss:Type="String">通知</Data></Cell>
<Cell ss:StyleID="s35"><Data ss:Type="String">不公开</Data></Cell>
<Cell ss:StyleID="s35"><Data ss:Type="String">单位秘密</Data></Cell>
<Cell ss:StyleID="s35"><Data ss:Type="String">印发 经费 管理办法 通知</Data></Cell>
<Cell ss:StyleID="s35"/>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Layout x:Orientation="Landscape"/>
<Header x:Margin="0.51181102362204722"/>
<Footer x:Margin="0.39370078740157483" x:Data="&C&11&P"/>
<PageMargins x:Bottom="0.6692913385826772" x:Left="0.59055118110236227"
x:Right="0.59055118110236227" x:Top="0.78740157480314965"/>
</PageSetup>
<Unsynced/>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<PageBreakZoom>115</PageBreakZoom>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>8</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
Code//以流的形式向客户端输出
public void CreateComplexExcel(System.Data.DataTable dt)
{
string xmlTemplate = "~/ControlSource/ItemTemplate/公文类信息目录备案表.xml";
string xmlPath = this.MapPath(xmlTemplate);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlPath);
//填充数据
//XmlNode xmlTable = xmlDoc.ChildNodes[2].ChildNodes[3].ChildNodes[1];
XmlNode xmlRowlist = xmlDoc.CreateElement("Rowlist");
foreach (DataRow dr in dt.Rows)
{
XmlNode xmlRow = CreateRow(xmlDoc, dr["标题"].ToString(), dr["文号"].ToString(),
dr["拟稿部门"].ToString(),dr["发文时间"].ToString(),dr["类型"].ToString(), dr["是否公开"].ToString(),
dr["公开理由"].ToString(), dr["主题词"].ToString());
//添加进去的节点属性，前缀命名空间会出问题,未查出什么原因的只能替换处理。
//xmlDoc.ChildNodes[2].ChildNodes[3].ChildNodes[1].AppendChild(xmlRow);
xmlRowlist.AppendChild(xmlRow);
}
//设置正确的格式
string strRow = xmlRowlist.InnerXml;
//设置行数
int strCount = dt.Rows.Count + 3;
string TemplateXml = xmlDoc.OuterXml.Replace("ExpandedRowCountValue", strCount.ToString());
//添加到xml数据中
string strFile = TemplateXml.Replace("<!--DataRow-->", strRow);
//重新加载数据 格式化输出
xmlDoc = new XmlDocument();
xmlDoc.LoadXml(strFile);
string filename = HttpUtility.UrlEncode("公文类信息目录备案表" + DateTime.Now.ToString("yyyyMMdd-HHmm"));
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.GetEncoding("utf-8");
Response.Write(xmlDoc.OuterXml);
Response.End();
}
//创建行数据
private XmlNode CreateRow(XmlDocument doc, string title, string wh, string DeptName,
string fwdate,string type,string ispublic, string ly, string ztc)
{
XmlNode xmlRow = doc.CreateElement("Row");
//自适应
XmlAttribute atrAutoFitHeight = doc.CreateAttribute("AutoFitHeight", "urn:schemas-microsoft-com:office:spreadsheet");
atrAutoFitHeight.Value = "0";
xmlRow.Attributes.Append(atrAutoFitHeight);
//行高度
XmlAttribute atrHeight = doc.CreateAttribute("Height", "urn:schemas-microsoft-com:office:spreadsheet");
atrHeight.Value = "39.9375";
xmlRow.Attributes.Append(atrHeight);
xmlRow.AppendChild(CreateCell(doc, "s34", "String", title));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", wh));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", DeptName));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", fwdate));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", type));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", ispublic));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", ly));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", ztc));
xmlRow.AppendChild(CreateCell(doc, "s35", "String", string.Empty));
return xmlRow;
}
//创建单元格
private XmlNode CreateCell(XmlDocument doc, string StyleID, string DataType, string Value)
{
//<Cell ss:StyleID="s34"><Data ss:Type="String">关于印发《XXXXXXX》的通知</Data></Cell>
XmlNode xmlCell = doc.CreateElement("Cell");
//样式属性
XmlAttribute atrStyle = doc.CreateAttribute("StyleID", "urn:schemas-microsoft-com:office:spreadsheet");
atrStyle.Value = StyleID;
xmlCell.Attributes.Append(atrStyle);
if (Value != string.Empty)
{
XmlNode xmlData = doc.CreateElement("Data");
//数据类型
XmlAttribute atrType = doc.CreateAttribute("Type", "urn:schemas-microsoft-com:office:spreadsheet");
atrType.Value = DataType;
xmlData.Attributes.Append(atrType);
xmlData.InnerText = Value;
xmlCell.AppendChild(xmlData);
}
return xmlCell;
}
方法3：不通过xml表格模板，直接通过输出,Html控件的InnerHtml属性内容.
(就是把我们要导出的数据用div框起来)
Aspx页面：
Code<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>表单数据导出到Excel</title>
</head>
<body>
<form id="form1" runat="server">
<div id="divXml" runat="server">
<table id="table10" align="center" border="0" cellpadding="0" cellspacing="0" style="width: 800px;
border: black 1px solid;">
<tr>
<td style="height: 5px" colspan="5">
</td>
</tr>
<tr>
<td align="center" colspan="5">
<br>
<h1>
车辆维修保养申请单
</h1>
</td>
</tr>
<tr>
<td style="height: 15px">
维修车辆</td>
<td style="height: 5px;">
送 修 人</td>
<td style="height: 15px">
申请金额</td>
<td style="height: 15px">
维修部门
</td>
<td style="height: 15px">
维修理由
</td>
</tr>
</table>
</div>
<div style="text-align: center">
<asp:ImageButton ID="btnExcel" SkinID="ibtnSkinMouseOver" ImageUrl="images/btnExcel.gif"
runat="server" OnClick="btnExcel_Click" /></div>
</form>
</body>
</html>
Codeprotected void btnExcel_Click(object sender, ImageClickEventArgs e)
{
//导出数据
CreateExcel(this.divXml.InnerHtml);
}
//以流的形式向客户端输出
public void CreateExcel(string strXml)
{
string filename = HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMdd-HHmm"));
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.Write(strXml);
Response.End();
}
———————————————————————————————————
浩瀚的天空，会有改变的希望，世界会不会变得更加好，选择在於我们的手上。
标签:
Execl
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义