html/xml转excle

来源：互联网发布：finereport 阿里云编辑：程序博客网时间：2024/06/16 13:04

最近公司项目,要实现各种导出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的耗时
        }

0 0