一.导入导出excel常用方法：
1.用查询表的方式查询并show在数据集控件上。
public static string strcon = " provider = microsoft.jet.oledb.4.0 ; data source =c:\\08.xls;extended properties=excel 8.0";
public static dataset ds;
protected void page_load(object sender, eventargs e)
{
oledbconnection conn = new oledbconnection(strcon);
string sql = "select * from [sheet1$]";
conn.open();
oledbdataadapter mycommand = new oledbdataadapter(sql, strcon);
ds = new dataset();
mycommand.fill(ds, "[sheet1$]");
conn.close();
datagrid1.datamember = "[sheet1$]";
datagrid1.datasource = ds;
datagrid1.databind();
//excel.application excel = new excel.application();
//excel.application.workbooks.add(true);
//excel.visible = true;
}
2.一个一个单元格的进行插入
string str = @"data source=it-428e4ea4b0c7\sqlexpress;initial catalog=testbase;integrated security=true";
sqlconnection conn = new sqlconnection(str);
conn.open();
int n = 0;
for (int i = 1; i < 20; i++)
{
if (n > 3)
break;
else
if (msheet.cells.get_range("a" + i, type.missing).text.tostring() == "" && n <= 3)
{ n++; }
else
{
//循环获取excel单元格的值一次一次的插入，excutesql为执行的存储过程
excutesql(msheet.cells.get_range("b" + i, type.missing).text.tostring(),
msheet.cells.get_range("b" + (i + 1), type.missing).text.tostring(),
msheet.cells.get_range("b" + (i + 2), type.missing).text.tostring(),
conn);
i = i + 3;
}
}
conn.close();
二快速导入导出
1.我们都知道当向db里批量插入数据的时候我们会选择sqlbulkcopy
if (datatable!=null && datatable.rows.count!=0)
{
sqlbulkcopy.writetoserver(datatable);
}
这个可以看 深山老林新发的一篇sqlserver中批量插入数据方式的性能对比
下面是sqlbulkcopy的方法，这个方法有一个弊端就是当excel某一列即有文字，还有日期的时候，会出现null值，我在网上查了一些资料说连接字串加上;hdr=yes;imex=1'的时候会都当做字符处理，但是还是会出现一些bug，所以建议最好先把excel数据分析到datatable里然后再用sqlbulkcopy倒入数据库
// block copy to db from excel
//by xijun,
//step create an excel file c:\inetpub\wwwroot\test.xls , fill cell(1,1) with "data",cell(1,2) with "name"
//step create table named "data" with column ("data","name") in your db
//there the code below:
datetime t1 = datetime.now;
response.write("<br>start time:" + t1.tostring());
string excelfile = @"c:\\_hub_report.xls";
string excelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfile + ";extended properties='excel.0;hdr=yes;imex=1'";
using (oledbconnection excelconnection = new oledbconnection(excelconnectionstring))
{
excelconnection.open();
//getting source data
//非空讀入數據
oledbcommand command = new oledbcommand("select [region],[customerpn],[rma],[date],[qty],[return/pull] from [_hub_report$] ", excelconnection);
// initialize sqlbulkcopy object
using (oledbdatareader dr = command.executereader())
{
// copy data to destination
string sqlconnectionstring = @"data source=minint-gphna\sqlexpress;initial catalog=gds_service;integrated security=true";
using (sqlbulkcopy bulkcopy = new sqlbulkcopy(sqlconnectionstring))
{
bulkcopy.destinationtablename = "gds_hub_data";
//加入只加入一個列的話,那么就會其他數據庫列都默認為空。
bulkcopy.columnmappings.add("region", "region");
bulkcopy.columnmappings.add("customerpn", "customer_item_number");
bulkcopy.columnmappings.add("rma", "rma");
bulkcopy.columnmappings.add("date", "date");
bulkcopy.columnmappings.add("qty", "qty_1");
bulkcopy.columnmappings.add("return/pull", "return_pull");
//bcp.batchsize =;//每次传输的行数
//bcp.notifyafter =;//进度提示的行数
bulkcopy.batchsize =;
bulkcopy.notifyafter =;
bulkcopy.writetoserver((idatareader)dr);
}
}
//closing connection
excelconnection.close();
}
datetime t2 = datetime.now;
response.write("<br>end time:" + t2.tostring());
response.write("<br>use time:" + ((timespan)(t2 - t1)).milliseconds.tostring() + " milliseconds");
response.write("<br>inser record count :");
2.快速导出db的数据到excel
这种方法就是利用
excel.querytables
excel.querytable
querytable把数据快速导入excel里。我们在做复杂报表的时候，这个用的是比较多了，但是单单会这个没有用，它只是快速的把db里的数据放放到excel里，在做大量数据而且需要设定excel样式的时候我们会选择先用这种方法把数据导入excel一个临时sheet，再利sheet复制，sheet移动，和一些excel样式设定，以及excel一个强大的自动填充的功能，那么这些就可以让我们快速的做出花样多试的excel报表，当然这个要求我们比较熟练office的操作，包括宏的操作。
public string query_table_getdata(string sourpath)
{
string str_path = sourpath.substring(0, sourpath.length - 22);
str_path = str_path + "basic.xls";
excel.querytables m_objqrytables = null;
excel.querytable m_objqrytable = null;
excel.application m_objexcel = null;
excel.workbooks m_objbooks = null;
excel.workbook m_objbook = null;
excel.sheets m_objsheets = null;
excel.worksheet m_objsheet = null;
excel.range m_objrange = null;
m_objexcel = new excel.application();
//try
//{
m_objbooks = m_objexcel.workbooks;
m_objbooks.open(sourpath, 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);
m_objbook = (excel.workbook)m_objbooks.get_item(1);
m_objsheets = (excel.sheets)m_objbook.worksheets;
m_objsheet = (excel.worksheet)m_objsheets.get_item(1);
m_objrange = m_objsheet.get_range("a2", type.missing);
m_objqrytables = m_objsheet.querytables;
string sqlstr = "select [day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
sqlstr += ",[week01] ,[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
sqlstr += ",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
sqlstr += " from [gds_service].[dbo].[gds_service_report_base] order by groupid ,id";
//可以放在配置文件里
string conn = @"provider=sqloledb.1;data source=minint-g87phna\sqlexpress;uid=xijun_ke;password=12345678;
initial catalog=gds_service;persist security info=false;";
m_objqrytable = (excel.querytable)m_objqrytables.add("oledb;" + conn, m_objrange, sqlstr);
m_objqrytable.refreshstyle = excel.xlcellinsertionmode.xlinsertentirerows;
m_objqrytable.refresh(false);
m_objbook.saveas(str_path, type.missing, type.missing, type.missing, type.missing, type.missing, excel.xlsaveasaccessmode.xlexclusive, type.missing, type.missing, type.missing, type.missing, type.missing);
m_objbook.close(false, type.missing, type.missing);
//}
//catch (exception ee)
//{
// mp.writelog(ee.tostring());
//}
//finally
//{
m_objexcel.quit();
gc.collect();
//}
return str_path;
}
com操作excel的一些特性操作：
range.numberformatlocal = "@"; //设置单元格格式为文本
range = (range)worksheet.get_range("a1", "e1"); //获取excel多个单元格区域：本例做为excel表头
range.merge(0); //单元格合并动作
worksheet.cells[1, 1] = "excel单元格赋值"; //excel单元格赋值
range.font.size = 15; //设置字体大小
range.font.underline=true; //设置字体是否有下划线
range.font.name="黑体"; 设置字体的种类
range.horizontalalignment=xlhalign.xlhaligncenter; //设置字体在单元格内的对其方式
range.columnwidth=15; //设置单元格的宽度
range.cells.interior.color=system.drawing.color.fromargb(255,204,153).toargb(); //设置单元格的背景色
range.borders.linestyle=1; //设置单元格边框的粗细
range.borderaround(xllinestyle.xlcontinuous,xlborderweight.xlthick,xlcolorindex.xlcolorindexautomatic,system.drawing.color.black.toargb()); //给单元格加边框
range.entirecolumn.autofit(); //自动调整列宽
range.horizontalalignment= xlcenter; // 文本水平居中方式
range.verticalalignment= xlcenter //文本垂直居中方式
range.wraptext=true; //文本自动换行
range.interior.colorindex=39; //填充颜色为淡紫色
range.font.color=clblue; //字体颜色
xlsapp.displayalerts=false; //保存excel的时候，不弹出是否保存的窗口直接进行保存
workbook.savecopyas(temp);/**////填入完信息之后另存到路径及文件名字
excel宏操作，sheet和单元格操作：
/// <summary>
/// 讀取excel數據和插入公式
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void button2_click(object sender, eventargs e)
{
datetime t1 = datetime.now;
response.write("<br>start time:" + t1.tostring());
excel.application excelkk = new excel.application();
excelkk.workbooks.add(true);
int row = 2;
datatable mytable = ds.tables["[sheet1$]"];
for (int i = 0; i < mytable.columns.count; i++)
{
excelkk.cells[1, 1 + i] = mytable.columns[i].columnname.tostring();
}
for (int i = 0; i < mytable.rows.count; i++)
{
for (int j = 0; j < mytable.columns.count; j++)
{
excelkk.cells[row, j + 1] = mytable.rows[i][j].tostring();
}
row++;
}
//取得特定單元格的值
excelkk.visible = true;
this.textboxchange.text = excelkk.get_range("a2", type.missing).text.tostring();
//表的單元格合并
excel.range range1 = excelkk.get_range("a2", "d4");
range1.merge(type.missing);
//想表格中插入求和的值
excel.range range2 = excelkk.get_range("b25", type.missing);
range2.formula = "=sum(b2:b24)";
range2.calculate();
//進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。
excel.range range3 = excelkk.get_range("b25","e25");
range2.autofill(range3,excel.xlautofilltype.xlfilldefault);
//刪除表的指定行數操作
excel.range range7 = null;
range7 = excelkk.get_range(excelkk.cells[2, 2], excelkk.cells[4, 4]);
range7.select();
range7.entirerow.delete(excel.xldirection.xlup);
//獲取最大用過的行數
excel.worksheet wsheet1 = (excel.worksheet)excelkk.worksheets.get_item(1);
int n =wsheet1.usedrange.cells.columns.count;
response.write(n.tostring() + "<br>");
//messagebox.show(n.tostring());
n = wsheet1.usedrange.cells.rows.count;
response.write(n.tostring() + "<br>");
//messagebox.show(n.tostring());
//數據的複製
excel.range range4 = excelkk.get_range("a2", "b25");
excel.range range5 = excelkk.get_range("e3", "f25");
//range4.get_offset(1,4).select();
range4.copy(range5);
//停用警告信息
excelkk.displayalerts = false;
gc.collect();
}
單個sheet里求和：
excel.range range2 = excelkk.get_range("b25", type.missing);
range2.formula = "=sum(b2:b24)";
range2.calculate();
跨sheet求和：
excel.worksheet wsheet1 = (excel.worksheet)excelsql.worksheets.get_item(1);
excel.range range3 =wsheet1.get_range("a23", type.missing);
range3.formula = "=sheet3!b8+sheet3!b12";
range3.calculate();
虽然我们拥有强大的npoi，不过我还是写出来，希望对大家理解office有一定的用处。
以上所述是小编给大家介绍的c#多种操作excel的方法比较，希望对大家有所帮助。在此也非常感谢大家对网站的支持！
