快速写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:	est.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("	<Styles>");
            sw.WriteLine("		<Style ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Center"/><Font ss:FontName="宋体" ss:Size="12"/></Style>");
            sw.WriteLine("		<Style ss:ID="s47"><Font ss:FontName="宋体" ss:Size="11" ss:Color="#000000"/><Interior ss:Color="#EBF1DE" ss:Pattern="Solid"/></Style>");
            sw.WriteLine("		<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("		<Style ss:ID="s68" ss:Parent="s33"><Alignment ss:Horizontal="Center" ss:Vertical="Center"/></Style>");
            sw.WriteLine("		<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("	</Styles>");
            sw.WriteLine("	<Worksheet ss:Name="Sheet1">");
            sw.WriteLine("		<Table ss:DefaultColumnWidth="150" ss:DefaultRowHeight="20">");
            #endregion

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

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

            #region excel表格内容
            foreach (DataRow row in dt.Rows)
            {
                sw.WriteLine("			<Row ss:AutoFitHeight="0" ss:Height="15">");
                for (CurrentCol = 0; CurrentCol < ColCount; CurrentCol++)
                {
                    sw.Write("				<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("			</Row>");
            }
            #endregion

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