最近公司项目,要实现各种导出Excle,各种开源组件都使用过,其中用过COM组件Excel,NPOI,MyXls,OLEDB.其中效率最佳的是OLEDB,但是有得必有失,样式不能保留,NPOI操作Excle2007有问题,COM组件样式能保留,但是效率不佳(最烦的是各种进程驻留后台,不管是quit()还是dispose()都还有).后来几个同事讨论了下,既然Office保存时可以选择保存为HTML/XML,就突发奇想能不能反过来操作,然后各种网上找都无果,于是就自己写了.下面是代码(高手勿喷),粘贴的不知道怎么保留样式
public static void SaveExcel(DataTable dt)
{
DateTime bg = DateTime.Now;//开始时间
string tr = "";
string path = string.Format(@"D:\开发临时目录\{0}.html", DateTime.Now.ToString("yyyyMMddhhmmss"));
FileStream fs = new FileStream(path, FileMode.CreateNew);//文件流写html
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);
sw.WriteLine("<table>");
for (var index = 0; index < dt.Rows.Count; index++)
{
#region 循环写TR
var Customer_type = string.IsNullOrEmpty(dt.Rows[index]["Customer_type"].ToString()) ? " " : dt.Rows[index]["Customer_type"].ToString();
var NAME = string.IsNullOrEmpty(dt.Rows[index]["NAME"].ToString()) ? " " : dt.Rows[index]["NAME"].ToString();
var STR_1 = string.IsNullOrEmpty(dt.Rows[index]["STR_1"].ToString()) ? " " : dt.Rows[index]["STR_1"].ToString();
var Group = string.IsNullOrEmpty(dt.Rows[index]["Group"].ToString()) ? " " : dt.Rows[index]["Group"].ToString();
var Grade = string.IsNullOrEmpty(dt.Rows[index]["Grade"].ToString()) ? " " : dt.Rows[index]["Grade"].ToString();
var sale_Uprc = string.IsNullOrEmpty(dt.Rows[index]["sale_Uprc"].ToString()) ? " " : dt.Rows[index]["sale_Uprc"].ToString();
var inv_AvePrc = string.IsNullOrEmpty(dt.Rows[index]["inv_AvePrc"].ToString()) ? " " : dt.Rows[index]["inv_AvePrc"].ToString();
var Spread = string.IsNullOrEmpty(dt.Rows[index]["Spread"].ToString()) ? " " : dt.Rows[index]["Spread"].ToString();
var Costs = string.IsNullOrEmpty(dt.Rows[index]["Costs"].ToString()) ? " " : dt.Rows[index]["Costs"].ToString();
var ML = string.IsNullOrEmpty(dt.Rows[index]["ML"].ToString()) ? " " : dt.Rows[index]["ML"].ToString();
var distributor = string.IsNullOrEmpty(dt.Rows[index]["distributor"].ToString()) ? " " : dt.Rows[index]["distributor"].ToString();
var dis_name = string.IsNullOrEmpty(dt.Rows[index]["dis_name"].ToString()) ? " " : dt.Rows[index]["dis_name"].ToString();
var rq = string.IsNullOrEmpty(dt.Rows[index]["rq"].ToString()) ? " " : dt.Rows[index]["rq"].ToString();
var dsr_code = string.IsNullOrEmpty(dt.Rows[index]["dsr_code"].ToString()) ? " " : dt.Rows[index]["dsr_code"].ToString();
var dsr_name = string.IsNullOrEmpty(dt.Rows[index]["dsr_name"].ToString()) ? " " : dt.Rows[index]["dsr_name"].ToString();
var customer_code = string.IsNullOrEmpty(dt.Rows[index]["customer_code"].ToString()) ? " " : dt.Rows[index]["customer_code"].ToString();
var customer_name = string.IsNullOrEmpty(dt.Rows[index]["customer_name"].ToString()) ? " " : dt.Rows[index]["customer_name"].ToString();
var com_code = string.IsNullOrEmpty(dt.Rows[index]["com_code"].ToString()) ? " " : dt.Rows[index]["com_code"].ToString();
var com_name = string.IsNullOrEmpty(dt.Rows[index]["com_name"].ToString()) ? " " : dt.Rows[index]["com_name"].ToString();
var standard = string.IsNullOrEmpty(dt.Rows[index]["standard"].ToString()) ? " " : dt.Rows[index]["standard"].ToString();
var unit = string.IsNullOrEmpty(dt.Rows[index]["unit"].ToString()) ? " " : dt.Rows[index]["unit"].ToString();
var sale_DJ = string.IsNullOrEmpty(dt.Rows[index]["sale_DJ"].ToString()) ? " " : dt.Rows[index]["sale_DJ"].ToString();
var flag = string.IsNullOrEmpty(dt.Rows[index]["flag"].ToString()) ? " " : dt.Rows[index]["flag"].ToString();
var quantity = string.IsNullOrEmpty(dt.Rows[index]["quantity"].ToString()) ? " " : dt.Rows[index]["quantity"].ToString();
var num_1 = string.IsNullOrEmpty(dt.Rows[index]["num_1"].ToString()) ? " " : dt.Rows[index]["num_1"].ToString();
var STR_4 = string.IsNullOrEmpty(dt.Rows[index]["STR_4"].ToString()) ? " " : dt.Rows[index]["STR_4"].ToString();
tr = string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td><td>{8}</td><td>{9}</td><td>{10}</td><td>{11}</td><td>{12}</td><td>{13}</td><td>{14}</td><td>{15}</td><td>{16}</td><td>{17}</td><td>{18}</td><td>{19}</td><td>{20}</td><td>{21}</td><td>{22}</td><td>{23}</td><td>{24}</td><td>{25}</td></tr>",
Customer_type, NAME, STR_1, Group, Grade, sale_Uprc, inv_AvePrc, Spread, Costs, ML, distributor, dis_name, rq, dsr_code, dsr_name, customer_code,
customer_name, com_code, com_name, standard, unit, sale_DJ, flag, quantity, num_1, STR_4);
sw.WriteLine(tr);
#endregion
}
sw.WriteLine("</table>");
sw.Close();
fs.Close();
DateTime t = DateTime.Now;
TimeSpan s = t - bg;//写html所用时间
var excle=new Microsoft.Office.Interop.Excel.Application();
//打开html
Microsoft.Office.Interop.Excel.Workbook book = excle.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//保存为xlsx
book.SaveAs(string.Format(@"D:\开发临时目录\{0}.xlsx", DateTime.Now.ToString("yyyyMMddhhmmss")), Type.Missing,
"freesoft", "freesoft", Type.Missing,
Type.Missing, XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
book.Close();
book = null;
excle.Quit();
excle = null;
DateTime end = DateTime.Now;
TimeSpan span = end - bg;//统计写html到完成Excle总耗时
TimeSpan ss = end - t;//统计从html到Excle的耗时
}