快速写excel的方法

对于用com组件写excel，笔者表示那个太慢了。而且很耗资源，还要装excel。

今天我们就用写文本文件的方式来写excel。

步骤1，用excel写好一个设计一个我们想要的模板。

步骤2，我们把做好的excel模板，另存为。 这时会出现另存为的对话框。在保存类型中选择“XML 电子表格 2003 （*.xml）”。然后名字随便写。

步骤3，把我们刚保存的xml文件用文本打开。我们就会看到，其实excel是以xml方式存放的数据。如果这时你用excel打开那个xml文件，会发现excel出现的就是我们刚才的设计的模板。而且格式和我们设计的一样。

步骤4，所以我们可以用写xml，或者文本的方式写excel，只要符合我们刚才保存的那个xml格式。然后文件的后缀名改成xls就OK了。 而这个写的速度超快。

 

---

 

说完了上面的方法我们用代码把它实现吧。笔者主要编程语言为c#。所以就用c#封装了个写excel的类。代码如下

 

public class ExcelHelper
{
    private Excel.Application oExcel = null;
    private Excel.Workbook oBook = null;
    private Excel.Worksheet oSheet = null;
    private Excel.Range oRange = null;
    public ExcelHelper()
    {
        oExcel = new Excel.Application();
        oBook = oExcel.Application.Workbooks.Add(true);
        oSheet = (Excel.Worksheet)oBook.ActiveSheet;
    }
    /// <summary>
    /// 向excel写入文本（如向“A1”写入文字：InsertText("A1",要填入的文字)）
    /// </summary>
    /// <param name="val_range">单元格</param>
    /// <param name="val_text">文本</param>
    public void InsertText(string val_range, string val_text)
    {
        oRange = oSheet.Range[val_range];
        oRange.Value = val_text;
    }
    /// <summary>
    /// 向excel写入文本
    /// </summary>
    /// <param name="val_range">单元格</param>
    /// <param name="val_text">文本</param>
    /// <param name="val_fontSize">字体大小</param>
    public void InsertText(string val_range, string val_text, float val_fontSize)
    {
        oRange = oSheet.Range[val_range];
        oRange.Value = val_text;
        oRange.Font.Size = val_fontSize;
    }
    /// <summary>
    /// 向excel写入文本（如向“B1”写入文字：InsertText(1,2,要填入的文字)）
    /// </summary>
    /// <param name="val_i">行号</param>
    /// <param name="val_j">列号</param>
    /// <param name="val_text">文本</param>
    public void InsertText(int val_i, int val_j, string val_text)
    {
        oRange = (Excel.Range)oSheet.Cells[val_i, val_j];
        oRange.Value = val_text;
    }
    /// <summary>
    /// 向excel写入文本（如向“B1”写入文字：InsertText(1,2,要填入的文字,字体大小)）
    /// </summary>
    /// <param name="val_i">行号</param>
    /// <param name="val_j">列号</param>
    /// <param name="val_text">文本</param>
    /// <param name="val_fontSize">字体大小</param>
    public void InsertText(int val_i, int val_j, string val_text, float val_fontSize)
    {
        oRange = oSheet.Cells[val_i, val_j];
        oRange.Value = val_text;
        oRange.Font.Size = val_fontSize;
    }
    /// <summary>
    /// 读取excel文本项（如读取“B1”的文字：ReadText("B1")）
    /// </summary>
    /// <param name="val_range">单元格</param>
    /// <returns></returns>
    public string ReadText(string val_range)
    {
        oRange = oSheet.Range[val_range];
        return oRange.Text.ToString();
    }
    /// <summary>
    /// 读取excel文本项（如读取“A1”的文字：ReadText(1,1)）
    /// </summary>
    /// <param name="val_i">行号</param>
    /// <param name="val_j">列号</param>
    /// <returns></returns>
    public string ReadText(int val_i, int val_j)
    {
        oRange = oSheet.Cells[val_i, val_j];
        return oRange.Text.ToString();
    }
    /// <summary>
    /// 合并单元格
    /// </summary>
    /// <param name="x1">行号</param>
    /// <param name="y1">列号</param>
    /// <param name="x2">行号</param>
    /// <param name="y2">列号</param>
    public void MerMergeCells(int x1, int y1, int x2, int y2)
    {
        oSheet.Range[oSheet.Cells[x1, y1], oSheet.Cells[x2, y2]].Merge();
    }
    /// <summary>
    /// 设置excel列的默认样式
    /// </summary>
    public void SetColumnDefaultStyle()
    {
        oSheet.Columns.EntireColumn.AutoFit();
        oSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
    }
    /// <summary>
    /// 保存excel文件（如要将excel保存到D:\test.xls：SaveAs）
    /// </summary>
    /// <param name="val_saveAsFilePath">文件路径</param>
    /// <returns></returns>
    public bool SaveAs(string val_saveAsFilePath)
    {
        try
        {
            oSheet.SaveAs(val_saveAsFilePath);
            return true;
        }
        catch
        {
            return false;
        }
    }
    public bool Print()
    {
        try
        {
            oSheet.PrintOut();
            return true;
        }
        catch
        {
            return false;
        }
    }
    /// <summary>
    /// 自动释放excel资源
    /// </summary>
    public void Dispose()
    {
        if (oExcel != null)
        {
            oExcel.Workbooks.Close();
            oExcel.Quit();
            KillAllExcel();
            oExcel = null;
        }
        if (oBook != null)
        {
            oBook = null;
        }
        if (oSheet != null)
        {
            oSheet = null;
        }
        if (oRange != null)
        {
            oRange = null;
        }
        GC.Collect();
    }
    /// <summary>
    /// 获取所有excel进程
    /// </summary>
    /// <returns></returns>
    private static List<Process> GetExcelProcesses()
    {
        Process[] processes = Process.GetProcesses();
        List<Process> excelProcesses = new List<Process>();
        for (int i = 0; i < processes.Length; i++)
        {
            if (processes[i].ProcessName.ToUpper() == "EXCEL")
                excelProcesses.Add(processes[i]);
        }
        return excelProcesses;
    }
    /// <summary>
    /// 杀死所有Excel进程
    /// </summary>
    private static void KillAllExcel()
    {
        List<Process> excelProcesses = GetExcelProcesses();
        for (int i = 0; i < excelProcesses.Count; i++)
        {
            excelProcesses[i].Kill();
        }
    }
    /// <summary>
    /// DataTable导出Excel
    /// </summary>
    /// <param name="sFileName"></param>
    /// <param name="dt"></param>
    public void DataTableToExcel(string fileName, DataTable dt)
    {
        int CurrentCol = 0;//当前列
        int RowCount = dt.Rows.Count + 1;//总行数
        int ColCount = dt.Columns.Count;//总列数
        StreamWriter sw = new StreamWriter(fileName, false);//文件如果存在，则自动覆盖
        try
        {
            #region XML头部
            sw.WriteLine("<?xml version=\"1.0\"?>");
            sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
            sw.WriteLine("<Workbook");
            sw.WriteLine("xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
            sw.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
            sw.WriteLine("\t<Styles>");
            sw.WriteLine("\t\t<Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Vertical=\"Center\"/><Font ss:FontName=\"宋体\" ss:Size=\"12\"/></Style>");
            sw.WriteLine("\t\t<Style ss:ID=\"s47\"><Font ss:FontName=\"宋体\" ss:Size=\"11\" ss:Color=\"#000000\"/><Interior ss:Color=\"#EBF1DE\" ss:Pattern=\"Solid\"/></Style>");
            sw.WriteLine("\t\t<Style ss:ID=\"s33\"><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Double\" ss:Weight=\"3\" ss:Color=\"#3F3F3F\"/></Borders><Font ss:FontName=\"宋体\" ss:Size=\"11\" ss:Color=\"#FFFFFF\" ss:Bold=\"1\"/><Interior ss:Color=\"#A5A5A5\" ss:Pattern=\"Solid\"/></Style>");
            sw.WriteLine("\t\t<Style ss:ID=\"s68\" ss:Parent=\"s33\"><Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/></Style>");
            sw.WriteLine("\t\t<Style ss:ID=\"s93\" ss:Parent=\"s47\"><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#3F3F3F\"/></Borders></Style>");
            sw.WriteLine("\t</Styles>");
            sw.WriteLine("\t<Worksheet ss:Name=\"Sheet1\">");
            sw.WriteLine("\t\t<Table ss:DefaultColumnWidth=\"150\" ss:DefaultRowHeight=\"20\">");
            #endregion

            #region excel标题
            sw.WriteLine("\t\t\t<Row>");
            sw.WriteLine("\t\t\t\t<Cell ss:MergeAcross=\"{0}\" ss:StyleID=\"s68\">",ColCount-1);
            sw.WriteLine("\t\t\t\t\t<Data ss:Type=\"String\">{0}</Data>",dt.TableName);
            sw.WriteLine("\t\t\t\t</Cell>");
            sw.WriteLine("\t\t\t</Row>");
            #endregion

            #region excel表头信息
            sw.WriteLine("\t\t\t<Row ss:AutoFitHeight=\"0\" ss:Height=\"15\">");
            for (CurrentCol = 0; CurrentCol < ColCount; CurrentCol++)
            {
                sw.Write("\t\t\t\t<Cell ss:StyleID=\"s93\"><Data ss:Type=\"String\">{0}</Data></Cell>", dt.Columns[CurrentCol].ColumnName.ToString().Trim());
            }
            sw.WriteLine("\t\t\t</Row>");
            #endregion

            #region excel表格内容
            foreach (DataRow row in dt.Rows)
            {
                sw.WriteLine("\t\t\t<Row ss:AutoFitHeight=\"0\" ss:Height=\"15\">");
                for (CurrentCol = 0; CurrentCol < ColCount; CurrentCol++)
                {
                    sw.Write("\t\t\t\t<Cell ss:StyleID=\"s93\"><Data ss:Type=\"String\">");
                    if (row[CurrentCol] != null)
                    {
                        sw.Write(row[CurrentCol].ToString().Trim());
                    }
                    else
                    {
                        sw.Write("");
                    }
                    sw.Write("</Data></Cell>");
                }
                sw.WriteLine("\t\t\t</Row>");
            }
            #endregion

            #region XML尾部
            sw.WriteLine("\t\t</Table>");
            sw.WriteLine("\t</Worksheet>");
            sw.WriteLine("</Workbook>");
            #endregion
        }
        catch
        { }
        finally
        {
            sw.Close();
            sw = null;
        }
    }
}