datatable生成excel和excel插入图片示例详解

excel知识点

一、添加引用和命名空间

添加microsoft.office.interop.excel引用，它的默认路径是c:\program files\microsoft visual studio 9.0\visual studio tools for office\pia\office12\microsoft.office.interop.excel.dll
代码中添加引用using microsoft.office.interop.excel;

二、excel类的简单介绍

此命名空间下关于excel类的结构分别为：
applicationclass - 就是我们的excel应用程序。
workbook - 就是我们平常见的一个个excel文件，经常是使用workbooks类对其进行操作。
worksheet - 就是excel文件中的一个个sheet页。
worksheet.cells[row, column] - 就是某行某列的单元格，注意这里的下标row和column都是从1开始的，跟我平常用的数组或集合的下标有所不同。
知道了上述基本知识后，利用此类来操作excel就清晰了很多。

三、excel的操作

任何操作excel的动作首先肯定是用excel应用程序，首先要new一个applicationclass 实例，并在最后将此实例释放。

applicationclass xlsapp = new applicationclass(); // 1. 创建excel应用程序对象的一个实例，相当于我们从开始菜单打开excel应用程序。
if (xlsapp == null)
{
//对此实例进行验证，如果为null则表示运行此代码的机器可能未安装excel
}

1. 打开现有的excel文件

workbook workbook = xlsapp.workbooks.open(excelfilepath, 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);
worksheet mysheet = workbook.sheets[1] as worksheet; //第一个sheet页
mysheet.name = "testsheet"; //这里修改sheet名称

2.复制sheet页

mysheet.copy(type.missing, workbook.sheets[1]); //复制mysheet成一个新的sheet页，复制完后的名称是mysheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，worksheet的数量增加一个

注意 这里copy方法的两个参数，指是的复制出来新的sheet页是在指定sheet页的前面还是后面，上面的例子就是指复制的sheet页在第一个sheet页的后面。

3.删除sheet页

xlsapp.displayalerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。
(xlsapp.activeworkbook.sheets[1] as worksheet).delete();

4.选中sheet页

(xlsapp.activeworkbook.sheets[1] as worksheet).select(type.missing); //选中某个sheet页

5.另存excel文件

workbook.saved = true;
workbook.savecopyas(filepath);

6.释放excel资源

workbook.close(true, type.missing, type.missing);
workbook = null;
xlsapp.quit();
xlsapp = null;

一般的我们传入一个datatable生成excel代码

/// <summary>
///
/// </summary>
/// <param name="dt"></param>
protected void exportexcel(datatable dt)
{
    if (dt == null||dt.rows.count==0) return;
    microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application();

    if (xlapp == null)
    {
        return;
    }
    system.globalization.cultureinfo currentci = system.threading.thread.currentthread.currentculture;
    system.threading.thread.currentthread.currentculture = new system.globalization.cultureinfo("en-us");
    microsoft.office.interop.excel.workbooks workbooks = xlapp.workbooks;
    microsoft.office.interop.excel.workbook workbook = workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
    microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1];
    microsoft.office.interop.excel.range range;
    long totalcount = dt.rows.count;
    long rowread = 0;
    float percent = 0;
    for (int i = 0; i < dt.columns.count; i++)
    {
        worksheet.cells[1, i + 1] = dt.columns[i].columnname;
        range = (microsoft.office.interop.excel.range)worksheet.cells[1, i + 1];
        range.interior.colorindex = 15;
        range.font.bold = true;
    }
    for (int r = 0; r < dt.rows.count; r++)
    {
        for (int i = 0; i < dt.columns.count; i++)
        {
            worksheet.cells[r + 2, i + 1] = dt.rows[r][i].tostring();
        }
        rowread++;
        percent = ((float)(100 * rowread)) / totalcount;
    }
    xlapp.visible = true;
}

如果要在excel中插入图片，我们需要把代码加入一行即可，如下所示

protected void exportexcel(datatable dt)
{
    if (dt == null || dt.rows.count == 0) return;
    microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application();

    if (xlapp == null)
    {
        return;
    }
    system.globalization.cultureinfo currentci = system.threading.thread.currentthread.currentculture;
    system.threading.thread.currentthread.currentculture = new system.globalization.cultureinfo("en-us");
    microsoft.office.interop.excel.workbooks workbooks = xlapp.workbooks;
    microsoft.office.interop.excel.workbook workbook = workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
    microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1];
    microsoft.office.interop.excel.range range;
    long totalcount = dt.rows.count;
    long rowread = 0;
    float percent = 0;
    for (int i = 0; i < dt.columns.count; i++)
    {
        worksheet.cells[1, i + 1] = dt.columns[i].columnname;
        range = (microsoft.office.interop.excel.range)worksheet.cells[1, i + 1];
        range.interior.colorindex = 15;
    }
    for (int r = 0; r < dt.rows.count; r++)
    {
        for (int i = 0; i < dt.columns.count; i++)
        {
            try
            {
                worksheet.cells[r + 2, i + 1] = dt.rows[r][i].tostring();
            }
            catch
            {
                worksheet.cells[r + 2, i + 1] = dt.rows[r][i].tostring().replace("=", "");
            }
        }
        rowread++;
        percent = ((float)(100 * rowread)) / totalcount;
    }

    worksheet.shapes.addpicture("c:\\users\\spring\\desktop\\1.gif", microsoft.office.core.msotristate.msofalse, microsoft.office.core.msotristate.msoctrue, 100, 200, 200, 300);
    worksheet.shapes.addtexteffect(microsoft.office.core.msopresettexteffect.msotexteffect1, "123456", "red", 15, microsoft.office.core.msotristate.msofalse, microsoft.office.core.msotristate.msotrue, 150, 200);
    xlapp.visible = true;
}

我们调用如下:

public void generateexcel()
{
    datatable dt = new datatable();
    dt.columns.add("name", typeof(string));
    dt.columns.add("age", typeof(string));
    datarow dr = dt.newrow();
    dr["name"] = "spring";
    dr["age"] = "20";
    dt.rows.add(dr);
    dt.acceptchanges();
    exportexcel(dt);
}

其中如下代码的作用是

worksheet.shapes.addpicture("c:\\users\\spring\\desktop\\1.gif", microsoft.office.core.msotristate.msofalse, microsoft.office.core.msotristate.msoctrue, 100, 200, 200, 300);

在excel的指定位置加入图片

worksheet.shapes.addtexteffect(microsoft.office.core.msopresettexteffect.msotexteffect1, "123456", "red", 15, microsoft.office.core.msotristate.msofalse, microsoft.office.core.msotristate.msotrue, 150, 200);
 

在excel的指定位置加入文本框，和里面的内容.

我们可以这样来设计一个excelbase的基类:

先创建一个excelbe.cs：

public class excelbe
 {
     private int _row = 0;
     private int _col = 0;
     private string _text = string.empty;
     private string _startcell = string.empty;
     private string _endcell = string.empty;
     private string _interiorcolor = string.empty;
     private bool _ismerge = false;
     private int _size = 0;
     private string _fontcolor = string.empty;
     private string _format = string.empty;

     public excelbe(int row, int col, string text, string startcell, string endcell, string interiorcolor, bool ismerge, int size, string fontcolor, string format)
     {
         _row = row;
         _col = col;
         _text = text;
         _startcell = startcell;
         _endcell = endcell;
         _interiorcolor = interiorcolor;
         _ismerge = ismerge;
         _size = size;
         _fontcolor = fontcolor;
         _format = format;
     }

     public excelbe()
     { }

     public int row
     {
         get { return _row; }
         set { _row = value; }
     }

     public int col
     {
         get { return _col; }
         set { _col = value; }
     }

     public string text
     {
         get { return _text; }
         set { _text = value; }
     }

     public string startcell
     {
         get { return _startcell; }
         set { _startcell = value; }
     }

     public string endcell
     {
         get { return _endcell; }
         set { _endcell = value; }
     }

     public string interiorcolor
     {
         get { return _interiorcolor; }
         set { _interiorcolor = value; }
     }

     public bool ismerge
     {
         get { return _ismerge; }
         set { _ismerge = value; }
     }

     public int size
     {
         get { return _size; }
         set { _size = value; }
     }

     public string fontcolor
     {
         get { return _fontcolor; }
         set { _fontcolor = value; }
     }

     public string formart
     {
         get { return _format; }
         set { _format = value; }
     }

 }

接下来创建excelbase.cs:

public class excelbase
{
    private microsoft.office.interop.excel.application app = null;
    private microsoft.office.interop.excel.workbook workbook = null;
    private microsoft.office.interop.excel.worksheet worksheet = null;
    private microsoft.office.interop.excel.range worksheet_range = null;

    public excelbase()
    {
        createdoc();
    }

    public void createdoc()
    {
        try
        {
            app = new microsoft.office.interop.excel.application();
            app.visible = true;
            workbook = app.workbooks.add(1);
            worksheet = (microsoft.office.interop.excel.worksheet)workbook.sheets[1];
        }
        catch (exception e)
        {
            console.write("error");
        }
        finally
        {
        }
    }

    public void insertdata(excelbe be)
    {
        worksheet.cells[be.row, be.col] = be.text;
        worksheet_range = worksheet.get_range(be.startcell, be.endcell);
        worksheet_range.mergecells = be.ismerge;
        worksheet_range.interior.color = getcolorvalue(be.interiorcolor);
        worksheet_range.borders.color = system.drawing.color.black.toargb();
        worksheet_range.columnwidth = be.size;
        worksheet_range.font.color = string.isnullorempty(be.fontcolor) ? system.drawing.color.white.toargb() : system.drawing.color.black.toargb();
        worksheet_range.numberformat = be.formart;
    }

    private int getcolorvalue(string interiorcolor)
    {
        switch (interiorcolor)
        {
            case "yellow":
                return system.drawing.color.yellow.toargb();
            case "gray":
                return system.drawing.color.gray.toargb();
            case "gainsboro":
                return system.drawing.color.gainsboro.toargb();
            case "turquoise":
                return system.drawing.color.turquoise.toargb();
            case "peachpuff":
                return system.drawing.color.peachpuff.toargb();

            default:
                return system.drawing.color.white.toargb();
        }
    }
}

调用的代码如下:

private void btnrun_click(object sender, eventargs e)
{
    excelbase excel = new excelbase();
    //creates the main header
    excelbe be = null;
    be = new excelbe (5, 2, "total of products", "b5", "d5", "yellow", true, 10, "n",null);
    excel.insertdata(be);
    //creates subheaders
    be = new excelbe (6, 2, "sold product", "b6", "b6", "gray", true, 10, "",null);
    excel.insertdata(be);
    be=new excelbe(6, 3, "", "c6", "c6", "gray", true, 10, "",null);
    excel.insertdata(be);
    be=new excelbe (6, 4, "initial total", "d6", "d6", "gray", true, 10, "",null);
    excel.insertdata(be);
    //add data to cells
    be=new excelbe (7, 2, "114287", "b7", "b7",null,false,10,"", "#,##0");
    excel.insertdata(be);
    be=new excelbe (7, 3, "", "c7", "c7", null,false,10,"",null);
    excel.insertdata(be);
    be = new excelbe(7, 4, "129121", "d7", "d7", null, false, 10, "", "#,##0");
    excel.insertdata(be);
    //add percentage row
    be = new excelbe(8, 2, "", "b8", "b8", null, false, 10, "", "");
    excel.insertdata(be);
    be = new excelbe(8, 3, "=b7/d7", "c8", "c8", null, false, 10, "", "0.0%");
    excel.insertdata(be);
    be = new excelbe(8, 4, "", "d8", "d8", null, false, 10, "", "");
    excel.insertdata(be);
    //add empty divider
    be = new excelbe(9, 2, "", "b9", "d9", "gainsboro", true, 10, "",null);
    excel.insertdata(be);  

}