因为程序是在Ubuntu上写的，如果在Windows上使用，需要略加改动。由于发现了更好的导出到Excel的方法，这个程序暂时不做windows版了。大家可以看《html字符串生成器源代码》。
先把要转化的.xls文件另存为.html文件，然后再用html字符串生成器转化。
为了实现C#导出到Excel，我选用了生成htm字符串的方式，用StringBuilder的方式，调用append函数，添加字符串，然后保存成htm格式，最后重命名生成Excel。但是在这个过程中，模板Excel中的很多格式、固定内容都是不变的，所以我就产生了用Python生成固定部分的字符串的想法。于是htm字符串生成器应运而生。
原htm代码:
View Code
<html 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"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="clmx.files/filelist.xml">
<link rel=Edit-Time-Data href="clmx.files/editdata.mso">
<link rel=OLE-Object-Data href="clmx.files/oledata.mso">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:LastAuthor>·¬ÇÑ»šÔ°</o:LastAuthor>
<o:Created>2012-01-15T03:52:01Z</o:Created>
<o:LastSaved>2012-01-15T03:52:01Z</o:LastSaved>
<o:Version>11.5606</o:Version>
</o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:ËÎÌå;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:³£¹æ;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:ËÎÌå;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:middle;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
color:red;
font-size:18.0pt;
font-weight:700;
text-align:center;}
.xl25
{mso-style-parent:style0;
color:red;
font-size:18.0pt;
font-weight:700;}
.xl26
{mso-style-parent:style0;
font-weight:700;}
.xl27
{mso-style-parent:style0;
text-align:center;}
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:ËÎÌå;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>285</x:DefaultRowHeight>
<x:Unsynced/>
<x:StandardWidth>2304</x:StandardWidth>
<x:Print>
<x:ValidPrinterInfo/>
<x:PaperSizeIndex>9</x:PaperSizeIndex>
<x:HorizontalResolution>300</x:HorizontalResolution>
<x:VerticalResolution>300</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>8</x:ActiveRow>
<x:ActiveCol>6</x:ActiveCol>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>10005</x:WindowHeight>
<x:WindowWidth>10005</x:WindowWidth>
<x:WindowTopX>120</x:WindowTopX>
<x:WindowTopY>135</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>
<table x:str border=0 cellpadding=0 cellspacing=0 width=504 style='border-collapse:
collapse;table-layout:fixed;378pt'>
<col width=72 span=7 style='54pt'>
<tr height=40 style='mso-height-source:userset;height:30.0pt'>
<td colspan=6 height=40 class=xl24 width=432 style='height:30.0pt;324pt'>²ÄÁÏÃ÷Ïž±í</td>
<td class=xl25 width=72 style='54pt'></td>
</tr>
<tr height=26 style='mso-height-source:userset;height:20.1pt'>
<td height=26 style='height:20.1pt'></td>
<td class=xl26>ÄêÔÂÈÕ</td>
<td colspan=2 class=xl26 style='mso-ignore:colspan'></td>
<td colspan=3 style='mso-ignore:colspan'></td>
</tr>
<tr height=19 style='mso-height-source:userset;height:14.25pt'>
<td height=19 style='height:14.25pt'>²ÄÁϱàºÅ</td>
<td>²ÄÁÏÀà±ð</td>
<td>²ÄÁÏÃû³Æ</td>
<td>²ÄÁϹæžñ</td>
<td>ÀÛŒÆÊýÄ¿</td>
<td>ÀیƜð¶î</td>
<td></td>
</tr>
<tr height=19 style='mso-height-source:userset;height:14.25pt'>
<td height=19 class=xl27 style='height:14.25pt'>1</td>
<td class=xl27>q</td>
<td class=xl27>qq</td>
<td class=xl27>qqq</td>
<td align=right x:num>11</td>
<td align=right x:num>11</td>
<td></td>
</tr>
<tr height=19 style='mso-height-source:userset;height:14.25pt'>
<td height=19 class=xl27 style='height:14.25pt'>2</td>
<td class=xl27>w</td>
<td class=xl27>ww</td>
<td class=xl27>www</td>
<td align=right x:num>22</td>
<td align=right x:num>22</td>
<td></td>
</tr>
<tr height=171 style='height:128.25pt;mso-xlrowspan:9'>
<td height=171 colspan=7 style='height:128.25pt;mso-ignore:colspan'></td>
</tr>
<tr height=19 style='mso-height-source:userset;height:14.25pt'>
<td height=19 style='height:14.25pt'>×ÜŒÆ:33</td>
<td colspan=6 style='mso-ignore:colspan'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=72 style='54pt'></td>
<td width=72 style='54pt'></td>
<td width=72 style='54pt'></td>
<td width=72 style='54pt'></td>
<td width=72 style='54pt'></td>
<td width=72 style='54pt'></td>
<td width=72 style='54pt'></td>
</tr>
<![endif]>
</table>
</body>
</html>
使用生成的代码：
View Code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace 按格式导出到excel
{
public partial class Form1 : Form
{
private SqlConnection _sqlConnection;
private SqlCommand _sqlCmd;
private SqlDataAdapter _sqlDa;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
String sql = "select CL_bianhao as '材料编号',CL_leibie as '材料类别',CL_mingcheng as '材料名称',CL_guige as '材料规格',CL_leijishumu as '累计数目',CL_leijijine as '累计金额' from CL";
ds = GetDataSet(sql);
dt = ds.Tables[0];
this.dataGridView1.DataSource = dt;
}
public DataSet GetDataSet(string selectCommand)
{
//MessageBox.Show(selectCommand);
DataSet ds = null;
// MessageBox.Show("*");
_sqlConnection = new SqlConnection("Data Source=LONGLONG\\SQLEXPRESS;Initial Catalog=StorageSystem;Integrated Security=True");
_sqlCmd = new SqlCommand();
_sqlCmd.Connection = _sqlConnection;
_sqlCmd.CommandType = CommandType.Text;
_sqlCmd.CommandText = selectCommand;
_sqlDa = new SqlDataAdapter(_sqlCmd);
ds = new DataSet();
_sqlDa.Fill(ds);
return ds;
}
private void button2_Click(object sender, EventArgs e)
{
string htmPath = "C://Export.htm";
string xlsPath="C://Export.xls";
StreamWriter sw = new StreamWriter(htmPath, false, System.Text.Encoding.Default);
ExportHtm(ds,sw);
if (File.Exists(xlsPath) == false)
{
File.Move(htmPath, xlsPath);
}
else
{
File.Delete(xlsPath);
File.Move(htmPath, xlsPath);
}
MessageBox.Show("导出已完成!");
}
private void ExportHtm(DataSet dataset, StreamWriter w)
{
string allstr = ExcelHeader();
allstr = allstr + ExcelContent();
w.Write(allstr);
w.Flush();
w.Close();
}
private string ExcelContent()
{
System.Text.StringBuilder sb=new System.Text.StringBuilder();
sb.Append("<body link=blue vlink=purple>\n\n<table x:str border=0 cellpadding=0 cellspacing=0 width=504 style='border-collapse:\n collapse;table-layout:fixed;378pt'>\n <col width=72 span=7 style='54pt'>\n <tr height=40 style='mso-height-source:userset;height:30.0pt'>\n <td colspan=6 height=40 class=xl24 width=432 style='height:30.0pt;324pt'>材料明细表</td>\n <td class=xl25 width=72 style='54pt'></td>\n </tr>\n");//表的名字
sb.Append("<tr height=26 style='mso-height-source:userset;height:20.1pt'>\n <td height=26 style='height:20.1pt'></td>\n <td class=xl26>年月日</td>\n <td colspan=2 class=xl26 style='mso-ignore:colspan'></td>\n <td colspan=3 style='mso-ignore:colspan'></td>\n </tr>\n");//年月日(暂时忽略日期的操作)
sb.Append(" <tr height=19 style='mso-height-source:userset;height:14.25pt'>\n <td height=19 style='height:14.25pt'>材料编号</td>\n <td>材料类别</td>\n <td>材料名称</td>\n <td>材料规格</td>\n <td>累计数目</td>\n <td>累计金额</td>\n <td></td>\n </tr>\n ");//列标
int sum = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append("<tr height=19 style='mso-height-source:userset;height:14.25pt'>\n <td height=19 class=xl27 style='height:14.25pt'>"+dt.Rows[i][0]+"</td>\n<td class=xl27>"+dt.Rows[i][1]+"</td>\n <td class=xl27>"+dt.Rows[i][2]+"</td>\n <td class=xl27>"+dt.Rows[i][3]+"</td>\n <td align=right x:num>"+dt.Rows[i][4]+"</td>\n <td align=right x:num>"+dt.Rows[i][5]+"</td>\n <td></td>\n </tr>\n");
sum = sum + Convert.ToInt32(dt.Rows[i][5]);
}
sb.Append("<tr height=171 style='height:128.25pt;mso-xlrowspan:9'>\n <td height=171 colspan=7 style='height:128.25pt;mso-ignore:colspan'></td>\n </tr>\n <tr height=19 style='mso-height-source:userset;height:14.25pt'>\n <td height=19 style='height:14.25pt'>总计:"+sum.ToString()+"</td>\n <td colspan=6 style='mso-ignore:colspan'></td>\n </tr>\n <![if supportMisalignedColumns]>\n <tr height=0 style='display:none'>\n <td width=72 style='54pt'></td>\n <td width=72 style='54pt'></td>\n <td width=72 style='54pt'></td>\n <td width=72 style='54pt'></td>\n <td width=72 style='54pt'></td>\n <td width=72 style='54pt'></td>\n <td width=72 style='54pt'></td>\n </tr>\n <![endif]>\n</table>\n\n</body>\n\n</html>\n\n\n\n");
return sb.ToString();
}
/// <summary>
/// 得到head标签的内容
/// </summary>
/// <returns>head标签的内容的字符串格式</returns>
private string ExcelHeader()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("<html xmlns:o="urn:schemas-microsoft-com:office:office"\nxmlns:x="urn:schemas-microsoft-com:office:excel"\nxmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"\nxmlns:html="http://www.w3.org/TR/REC-html40"\nxmlns="http://www.w3.org/TR/REC-html40">\n\n<head>\n<meta http-equiv=Content-Type content="text/html; charset=gb2312">\n<meta name=ProgId content=Excel.Sheet>\n<meta name=Generator content="Microsoft Excel 11">\n<link rel=File-List href="clmx.files/filelist.xml">\n<link rel=Edit-Time-Data href="clmx.files/editdata.mso">\n<link rel=OLE-Object-Data href="clmx.files/oledata.mso">\n<!--[if gte mso 9]><xml>\n <o:DocumentProperties>\n <o:LastAuthor>longlong</o:LastAuthor>\n <o:Created>2012-01-15T03:52:01Z</o:Created>\n <o:LastSaved>2012-01-15T03:52:01Z</o:LastSaved>\n <o:Version>11.5606</o:Version>\n </o:DocumentProperties>\n</xml><![endif]-->\n<style>\n<!--table\n {mso-displayed-decimal-separator:".";\n mso-displayed-thousand-separator:",";}\n@page\n {margin:1.0in .75in 1.0in .75in;\n mso-header-margin:.5in;\n mso-footer-margin:.5in;}\ntr\n {mso-height-source:auto;\n mso-ruby-visibility:none;}\ncol\n {mso-width-source:auto;\n mso-ruby-visibility:none;}\nbr\n {mso-data-placement:same-cell;}\n.style0\n {mso-number-format:General;\n text-align:general;\n vertical-align:middle;\n white-space:nowrap;\n mso-rotate:0;\n mso-background-source:auto;\n mso-pattern:auto;\n color:windowtext;\n font-size:12.0pt;\n font-weight:400;\n font-style:normal;\n text-decoration:none;\n font-family:宋体;\n mso-generic-font-family:auto;\n mso-font-charset:134;\n border:none;\n mso-protection:locked visible;\n mso-style-name:常规;\n mso-style-id:0;}\ntd\n {mso-style-parent:style0;\n padding-top:1px;\n padding-right:1px;\n padding-left:1px;\n mso-ignore:padding;\n color:windowtext;\n font-size:12.0pt;\n font-weight:400;\n font-style:normal;\n text-decoration:none;\n font-family:宋体;\n mso-generic-font-family:auto;\n mso-font-charset:134;\n mso-number-format:General;\n text-align:general;\n vertical-align:middle;\n border:none;\n mso-background-source:auto;\n mso-pattern:auto;\n mso-protection:locked visible;\n white-space:nowrap;\n mso-rotate:0;}\n.xl24\n {mso-style-parent:style0;\n color:red;\n font-size:18.0pt;\n font-weight:700;\n text-align:center;}\n.xl25\n {mso-style-parent:style0;\n color:red;\n font-size:18.0pt;\n font-weight:700;}\n.xl26\n {mso-style-parent:style0;\n font-weight:700;}\n.xl27\n {mso-style-parent:style0;\n text-align:center;}\nruby\n {ruby-align:left;}\nrt\n {color:windowtext;\n font-size:9.0pt;\n font-weight:400;\n font-style:normal;\n text-decoration:none;\n font-family:宋体;\n mso-generic-font-family:auto;\n mso-font-charset:134;\n mso-char-type:none;\n display:none;}\n-->\n</style>\n<!--[if gte mso 9]><xml>\n <x:ExcelWorkbook>\n <x:ExcelWorksheets>\n <x:ExcelWorksheet>\n <x:Name>Sheet1</x:Name>\n <x:WorksheetOptions>\n <x:DefaultRowHeight>285</x:DefaultRowHeight>\n <x:Unsynced/>\n <x:StandardWidth>2304</x:StandardWidth>\n <x:Print>\n <x:ValidPrinterInfo/>\n <x:PaperSizeIndex>9</x:PaperSizeIndex>\n <x:HorizontalResolution>300</x:HorizontalResolution>\n <x:VerticalResolution>300</x:VerticalResolution>\n </x:Print>\n <x:Selected/>\n <x:Panes>\n <x:Pane>\n <x:Number>3</x:Number>\n <x:ActiveRow>8</x:ActiveRow>\n <x:ActiveCol>6</x:ActiveCol>\n </x:Pane>\n </x:Panes>\n <x:ProtectContents>False</x:ProtectContents>\n <x:ProtectObjects>False</x:ProtectObjects>\n <x:ProtectScenarios>False</x:ProtectScenarios>\n </x:WorksheetOptions>\n </x:ExcelWorksheet>\n </x:ExcelWorksheets>\n <x:WindowHeight>10005</x:WindowHeight>\n <x:WindowWidth>10005</x:WindowWidth>\n <x:WindowTopX>120</x:WindowTopX>\n <x:WindowTopY>135</x:WindowTopY>\n <x:ProtectStructure>False</x:ProtectStructure>\n <x:ProtectWindows>False</x:ProtectWindows>\n </x:ExcelWorkbook>\n</xml><![endif]-->\n</head>\n");
return sb.ToString();
}
}
}
代码里的html代码部分就是用代码生成器生成的。但是双引号由于浏览器又解释一遍的原因，显示不对，应该转义，在双引号前加斜线。
下载地址:https://files.cnblogs.com/pythonlover/htm%E5%AD%97%E7%AC%A6%E4%B8%B2%E7%94%9F%E6%88%90%E5%99%A8.zip