我要评论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);
}
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论