C#导出Excel的示例详解
程序员文章站
2023-12-16 13:02:46
本文实例为大家分享了c#导出excel的具体代码,供大家参考,具体内容如下
using system;
using system.collections.gen...
本文实例为大家分享了c#导出excel的具体代码,供大家参考,具体内容如下
using system; using system.collections.generic; using system.text; using system.data; using system.windows.forms; using system.reflection; namespace dms { /// <summary> /// c#操作excel类 /// </summary> class exceloperate { //法一 //public bool datasettoexcel(dataset dataset, bool isshowexcle) //{ // datatable datatable = dataset.tables[0]; // int rownumber = datatable.rows.count; // int columnnumber = datatable.columns.count; // if (rownumber == 0) // { // messagebox.show("没有任何数据可以导入到excel文件!"); // return false; // } // //建立excel对象 // microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application(); // excel.application.workbooks.add(true); // excel.visible = isshowexcle;//是否打开该excel文件 // //填充数据 // for (int c = 0; c < rownumber; c++) // { // for (int j = 0; j < columnnumber; j++) // { // excel.cells[c + 1, j + 1] = datatable.rows[c].itemarray[j]; // } // } // return true; //} //法二 //public bool datasettoexcel(dataset dataset, bool isshowexcle) //{ // datatable datatable = dataset.tables[0]; // int rownumber = datatable.rows.count; // int rowindex = 1; // int colindex = 0; // if (rownumber == 0) // { // return false; // } // //建立excel对象 // microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application(); // excel.application.workbooks.add(true); // excel.visible = isshowexcle; // //生成字段名称 // foreach (datacolumn col in datatable.columns) // { // colindex++; // excel.cells[1, colindex] = col.columnname; // } // //填充数据 // foreach (datarow row in datatable.rows) // { // rowindex++; // colindex = 0; // foreach (datacolumn col in datatable.columns) // { // colindex++; // excel.cells[rowindex, colindex] = row[col.columnname]; // } // } // return true; //} //法三(速度最快) /// <summary> /// 将数据集中的数据导出到excel文件 /// </summary> /// <param name="dataset">输入数据集</param> /// <param name="isshowexcle">是否显示该excel文件</param> /// <returns></returns> public bool datasettoexcel(dataset dataset, bool isshowexcle) { datatable datatable = dataset.tables[0]; int rownumber = datatable.rows.count;//不包括字段名 int columnnumber = datatable.columns.count; int colindex = 0; if (rownumber == 0) { return false; } //建立excel对象 microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application(); //excel.application.workbooks.add(true); microsoft.office.interop.excel.workbook workbook = excel.workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet); microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1]; excel.visible = isshowexcle; //microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)excel.worksheets[1]; microsoft.office.interop.excel.range range; //生成字段名称 foreach (datacolumn col in datatable.columns) { colindex++; excel.cells[1, colindex] = col.columnname; } object[,] objdata = new object[rownumber, columnnumber]; for (int r = 0; r < rownumber; r++) { for (int c = 0; c < columnnumber; c++) { objdata[r, c] = datatable.rows[r][c]; } //application.doevents(); } // 写入excel range = worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, columnnumber]); //range.numberformat = "@";//设置单元格为文本格式 range.value2 = objdata; worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, 1]).numberformat = "yyyy-m-d h:mm"; return true; } //法四 //public bool datasettoexcel(dataset dataset, bool isshowexcle) //{ // datatable datatable = dataset.tables[0]; // int rownumber = datatable.rows.count; // int columnnumber = datatable.columns.count; // string stringbuffer = ""; // if (rownumber == 0) // { // messagebox.show("没有任何数据可以导入到excel文件!"); // return false; // } // //建立excel对象 // microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application(); // excel.application.workbooks.add(true); // excel.visible = isshowexcle;//是否打开该excel文件 // //填充数据 // for (int i = 0; i < rownumber; i++) // { // for (int j = 0; j < columnnumber; j++) // { // stringbuffer += datatable.rows[i].itemarray[j].tostring(); // if (j < columnnumber - 1) // { // stringbuffer += "\t"; // } // } // stringbuffer += "\n"; // } // clipboard.clear(); // clipboard.setdataobject(stringbuffer); // ((microsoft.office.interop.excel.range)excel.cells[1, 1]).select(); // ((microsoft.office.interop.excel.worksheet)excel.activeworkbook.activesheet).paste(missing.value, missing.value); // clipboard.clear(); // return true; //} //public bool datasettoexcel(dataset dataset, string filename, bool isshowexcle) //{ // datatable datatable = dataset.tables[0]; // int rownumber = datatable.rows.count; // int columnnumber = datatable.columns.count; // if (rownumber == 0) // { // messagebox.show("没有任何数据可以导入到excel文件!"); // return false; // } // //建立excel对象 // microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application(); // microsoft.office.interop.excel.workbook workbook = excel.application.workbooks.add(true); // excel.visible = false;//是否打开该excel文件 // //填充数据 // for (int i = 0; i < rownumber; i++) // { // for (int j = 0; j < columnnumber; j++) // { // excel.cells[i + 1, j + 1] = datatable.rows[i].itemarray[j]; // } // } // //string filename = path + "\\" + datetime.now.tostring().replace(':', '_') + ".xls"; // workbook.saveas(filename, missing.value, missing.value, missing.value, missing.value, missing.value, microsoft.office.interop.excel.xlsaveasaccessmode.xlnochange, missing.value, missing.value, missing.value, missing.value, missing.value); // try // { // workbook.saved = true; // excel.usercontrol = false; // //excelapp.quit(); // } // catch (exception exception) // { // messagebox.show(exception.message); // } // finally // { // workbook.close(microsoft.office.interop.excel.xlsaveaction.xlsavechanges, missing.value, missing.value); // excel.quit(); // } // if (isshowexcle) // { // system.diagnostics.process.start(filename); // } // return true; //} //public bool datasettoexcel(dataset dataset, string filename, bool isshowexcle) //{ // datatable datatable = dataset.tables[0]; // int rownumber = datatable.rows.count;//不包括字段名 // int columnnumber = datatable.columns.count; // int colindex = 0; // if (rownumber == 0) // { // messagebox.show("没有任何数据可以导入到excel文件!"); // return false; // } // //建立excel对象 // microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application(); // //excel.application.workbooks.add(true); // microsoft.office.interop.excel.workbook workbook = excel.workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet); // microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1]; // excel.visible = isshowexcle; // //microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)excel.worksheets[1]; // worksheet.name = "挠度数据"; // microsoft.office.interop.excel.range range; // //生成字段名称 // foreach (datacolumn col in datatable.columns) // { // colindex++; // excel.cells[1, colindex] = col.columnname; // } // object[,] objdata = new object[rownumber, columnnumber]; // for (int r = 0; r < rownumber; r++) // { // for (int c = 0; c < columnnumber; c++) // { // objdata[r, c] = datatable.rows[r][c]; // } // //application.doevents(); // } // // 写入excel // range = worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, columnnumber]); // //range.numberformat = "@";//设置单元格为文本格式 // range.value2 = objdata; // worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, 1]).numberformat = "yyyy-m-d h:mm"; // //string filename = path + "\\" + datetime.now.tostring().replace(':', '_') + ".xls"; // workbook.saveas(filename, missing.value, missing.value, missing.value, missing.value, missing.value, microsoft.office.interop.excel.xlsaveasaccessmode.xlnochange, missing.value, missing.value, missing.value, missing.value, missing.value); // try // { // workbook.saved = true; // excel.usercontrol = false; // //excelapp.quit(); // } // catch (exception exception) // { // messagebox.show(exception.message); // } // finally // { // workbook.close(microsoft.office.interop.excel.xlsaveaction.xlsavechanges, missing.value, missing.value); // excel.quit(); // } // //if (isshowexcle) // //{ // // system.diagnostics.process.start(filename); // //} // return true; //} /// <summary> /// 将数据集中的数据保存到excel文件 /// </summary> /// <param name="dataset">输入数据集</param> /// <param name="filename">保存excel文件的绝对路径名</param> /// <param name="isshowexcle">是否打开excel文件</param> /// <returns></returns> public bool datasettoexcel(dataset dataset, string filename, bool isshowexcle) { datatable datatable = dataset.tables[0]; int rownumber = datatable.rows.count;//不包括字段名 int columnnumber = datatable.columns.count; int colindex = 0; if (rownumber == 0) { messagebox.show("没有任何数据可以导入到excel文件!"); return false; } //建立excel对象 microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application(); //excel.application.workbooks.add(true); microsoft.office.interop.excel.workbook workbook = excel.workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet); microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1]; excel.visible = false; //microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)excel.worksheets[1]; microsoft.office.interop.excel.range range; //生成字段名称 foreach (datacolumn col in datatable.columns) { colindex++; excel.cells[1, colindex] = col.columnname; } object[,] objdata = new object[rownumber, columnnumber]; for (int r = 0; r < rownumber; r++) { for (int c = 0; c < columnnumber; c++) { objdata[r, c] = datatable.rows[r][c]; } //application.doevents(); } // 写入excel range = worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, columnnumber]); //range.numberformat = "@";//设置单元格为文本格式 range.value2 = objdata; worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, 1]).numberformat = "yyyy-m-d h:mm"; //string filename = path + "\\" + datetime.now.tostring().replace(':', '_') + ".xls"; workbook.saveas(filename, missing.value, missing.value, missing.value, missing.value, missing.value, microsoft.office.interop.excel.xlsaveasaccessmode.xlnochange, missing.value, missing.value, missing.value, missing.value, missing.value); try { workbook.saved = true; excel.usercontrol = false; //excelapp.quit(); } catch (exception exception) { messagebox.show(exception.message); } finally { workbook.close(microsoft.office.interop.excel.xlsaveaction.xlsavechanges, missing.value, missing.value); excel.quit(); } if (isshowexcle) { system.diagnostics.process.start(filename); } return true; } } }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。