C#导出数据到Excel文件的方法
程序员文章站
2022-05-15 14:21:02
本文实例讲述了c#导出数据到excel文件的方法。分享给大家供大家参考。具体实现方法如下:
///
/// 导出到excel类,...
本文实例讲述了c#导出数据到excel文件的方法。分享给大家供大家参考。具体实现方法如下:
/// <summary> /// 导出到excel类,项目需引用microsodt.office.interop.excel, /// 类文件需using system.data与system.windows.forms命名空间 /// </summary> public class ctoexcel { /// <summary> /// 导出到excel /// </summary> /// <param name="filename">默认文件名</param> /// <param name="listview">数据源,一个页面上的listview控件</param> /// <param name="titlerowcount">标题占据的行数,为0表示无标题</param> public void exportexcel(string filename, system.windows.forms.listview listview,int titlerowcount) { string savefilename = ""; //bool filesaved = false; savefiledialog savedialog = new savefiledialog(); savedialog.defaultext = "xls"; savedialog.filter = "excel文件|*.xls"; savedialog.filename = filename; savedialog.showdialog(); savefilename = savedialog.filename; if (savefilename.indexof(":") < 0) return; //被点了取消 microsoft.office.interop.excel.application xlapp; try { xlapp = new microsoft.office.interop.excel.application(); } catch (exception) { messagebox.show("无法创建excel对象,可能您的机子未安装excel"); return; } finally { } 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];//取得sheet1 //写title if(titlerowcount!=0) mergecells(worksheet, 1, 1, titlerowcount, listview.columns.count, listview.tag.tostring()); //写入列标题 for (int i = 0; i <= listview.columns.count - 1; i++) { worksheet.cells[titlerowcount+1, i + 1] = listview.columns[i].text; } //写入数值 for (int r = 0; r <= listview.items.count - 1; r++) { for (int i = 0; i <= listview.columns.count - 1; i++) { worksheet.cells[r + titlerowcount+2, i + 1] = listview.items[r].subitems[i].text; } system.windows.forms.application.doevents(); } worksheet.columns.entirecolumn.autofit();//列宽自适应 //if (microsoft.office.interop.cmbxtype.text != "notification") //{ // excel.range rg = worksheet.get_range(worksheet.cells[2, 2], worksheet.cells[ds.tables[0].rows.count + 1, 2]); // rg.numberformat = "00000000"; //} if (savefilename != "") { try { workbook.saved = true; workbook.savecopyas(savefilename); //filesaved = true; } catch (exception ex) { //filesaved = false; messagebox.show("导出文件时出错,文件可能正被打开!n" + ex.message); } } //else //{ // filesaved = false; //} xlapp.quit(); gc.collect();//强行销毁 // if (filesaved && system.io.file.exists(savefilename)) system.diagnostics.process.start(savefilename); //打开excel messagebox.show(filename + "导出到excel成功", "提示", messageboxbuttons.ok); } /// <summary> /// datatable导出到excel /// </summary> /// <param name="filename">默认的文件名</param> /// <param name="datatable">数据源,一个datatable数据表</param> /// <param name="titlerowcount">标题占据的行数,为0则表示无标题</param> public void exportexcel(string filename,system.data.datatable datatable,int titlerowcount) { string savefilename = ""; //bool filesaved = false; savefiledialog savedialog = new savefiledialog(); savedialog.defaultext = "xls"; savedialog.filter = "excel文件|*.xls"; savedialog.filename = filename; savedialog.showdialog(); savefilename = savedialog.filename; if (savefilename.indexof(":") < 0) return; //被点了取消 microsoft.office.interop.excel.application xlapp; try { xlapp = new microsoft.office.interop.excel.application(); } catch (exception) { messagebox.show("无法创建excel对象,可能您的机子未安装excel"); return; } finally { } 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];//取得sheet1 //写title if(titlerowcount!=0) mergecells(worksheet, 1, 1, titlerowcount, datatable.columns.count, datatable.tablename); //写入列标题 for (int i = 0; i <= datatable.columns.count - 1; i++) { worksheet.cells[titlerowcount+1, i + 1] = datatable.columns[i].columnname; } //写入数值 for (int r = 0; r <= datatable.rows.count - 1; r++) { for (int i = 0; i <= datatable.columns.count - 1; i++) { worksheet.cells[r +titlerowcount+ 2, i + 1] = datatable.rows[r][i].tostring(); } system.windows.forms.application.doevents(); } worksheet.columns.entirecolumn.autofit();//列宽自适应 //if (microsoft.office.interop.cmbxtype.text != "notification") //{ // excel.range rg = worksheet.get_range(worksheet.cells[2, 2], worksheet.cells[ds.tables[0].rows.count + 1, 2]); // rg.numberformat = "00000000"; //} if (savefilename != "") { try { workbook.saved = true; workbook.savecopyas(savefilename); //filesaved = true; } catch (exception ex) { //filesaved = false; messagebox.show("导出文件时出错,文件可能正被打开!n" + ex.message); } } //else //{ // filesaved = false; //} xlapp.quit(); gc.collect();//强行销毁 // if (filesaved && system.io.file.exists(savefilename)) system.diagnostics.process.start(savefilename); //打开excel messagebox.show(filename + "导出到excel成功", "提示", messageboxbuttons.ok); } /// <summary> /// 合并单元格,并赋值,对指定worksheet操作 /// </summary> /// <param name="sheetindex">worksheet索引</param> /// <param name="beginrowindex">开始行索引</param> /// <param name="begincolumnindex">开始列索引</param> /// <param name="endrowindex">结束行索引</param> /// <param name="endcolumnindex">结束列索引</param> /// <param name="text">合并后range的值</param> public void mergecells(microsoft.office.interop.excel.worksheet worksheet, int beginrowindex, int begincolumnindex, int endrowindex, int endcolumnindex, string text) { microsoft.office.interop.excel.range range = worksheet.get_range(worksheet.cells[beginrowindex, begincolumnindex], worksheet.cells[endrowindex, endcolumnindex]); range.clearcontents(); //先把range内容清除,合并才不会出错 range.mergecells = true; range.value2 = text; range.horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhaligncenter; range.verticalalignment = microsoft.office.interop.excel.xlvalign.xlvaligncenter; } }
希望本文所述对大家的c#程序设计有所帮助。
下一篇: C#使用shell32获取文件属性的方法
推荐阅读
-
Python3 中把txt数据文件读入到矩阵中的方法
-
php使用指定编码导出mysql数据到csv文件的方法
-
Linux下将excel数据导入到mssql数据库中的方法
-
ThinkPHP框架实现导出excel数据的方法示例【基于PHPExcel】
-
C#将Sql数据保存到Excel文件中的方法
-
C#使用RenderControl将GridView控件导出到EXCEL的方法
-
asp.net实现Gradview绑定数据库数据并导出Excel的方法
-
asp.net实现数据从DataTable导入到Excel文件并创建表的方法
-
asp.net实现从Txt文件读取数据到数据视图的方法
-
mysql 导出select语句结果到excel文件遇到问题及解决方法