C# WinForm导出Excel方法介绍
.net开发人员首选的方法,通过com组件调用office软件本身来实现文件的创建和读写,但是数据量较大的时候异常缓慢;如下代码所示已经做了优化,将一个二维对象数组赋值到一个单元格区域中(下面的代码中只能用于导出列数不多于26列的数据导出):
office pia
public static void exporttoexcel(dataset dataset, string outputpath)
{
excel.applicationclass excel = new excel.applicationclass();
excel.workbook workbook = excel.workbooks.add(type.missing);
int sheetindex = 0;
foreach (system.data.datatable dt in dataset.tables)
{
object[,] data = new object[dt.rows.count + 1, dt.columns.count];
for (int j = 0; j < dt.columns.count; j++)
{
data[0, j] = dt.columns[j].columnname;
}
for (int j = 0; j < dt.columns.count; j++)
{
for (int i = 0; i < dt.rows.count; i++)
{
data[i + 1, j] = dt.rows[i][j];
}
}
string finalcolletter = string.empty;
string colcharset = "abcdefghijklmnopqrstuvwxyz";
int colcharsetlen = colcharset.length;
if (dt.columns.count > colcharsetlen)
{
finalcolletter = colcharset.substring(
(dt.columns.count - 1) / colcharsetlen - 1, 1);
}
finalcolletter += colcharset.substring(
(dt.columns.count - 1) % colcharsetlen, 1);
excel.worksheet sheet = (excel.worksheet)workbook.sheets.add(
workbook.sheets.get_item(++sheetindex),
type.missing, 1, excel.xlsheettype.xlworksheet);
sheet.name = dt.tablename;
string range = string.format("a1:{0}{1}", finalcolletter, dt.rows.count + 1);
sheet.get_range(range, type.missing).value2 = data;
((excel.range)sheet.rows[1, type.missing]).font.bold = true;
}
workbook.saveas(outputpath, excel.xlfileformat.xlworkbooknormal, type.missing,
type.missing, type.missing, type.missing, excel.xlsaveasaccessmode.xlexclusive,
type.missing, type.missing, type.missing, type.missing, type.missing);
workbook.close(true, type.missing, type.missing);
workbook = null;
excel.quit();
killspecialexcel(excel);
excel = null;
gc.collect();
gc.waitforpendingfinalizers();
}
[dllimport("user32.dll", setlasterror = true)]
static extern int getwindowthreadprocessid(intptr hwnd, out int processid);
static void killspecialexcel(excel.application app)
{
try
{
if (app != null)
{
int processid;
getwindowthreadprocessid(new intptr(app.hwnd), out processid);
system.diagnostics.process.getprocessbyid(processid).kill();
}
}
catch (exception ex)
{
throw ex;
}
}
文件流
这种方法的效率明显高于第一种,而且也不需要安装office,但是导出的xls文件并不符合excel的格式标准,在打开生成的xls文件时会提示:the file you are trying to open is in a different format that specified by the file extension. verify that the file is not corrupted and is from a trusted source before opening the file.
public static void exporttoexcel(system.data.dataset ds, string path)
{
streamwriter sw = null;
try
{
long totalcount = ds.tables[0].rows.count;
sw = new streamwriter(path, false, encoding.unicode);
stringbuilder sb = new stringbuilder();
for (int i = 0; i < ds.tables[0].columns.count; i++)
{
sb.append(ds.tables[0].columns[i].columnname + "\t");
}
sb.append(environment.newline);
for (int i = 0; i < ds.tables[0].rows.count; i++)
{
for (int j = 0; j < ds.tables[0].columns.count; j++)
{
sb.append(ds.tables[0].rows[i][j].tostring() + "\t");
}
sb.append(environment.newline);
}
sw.write(sb.tostring());
sw.flush();
}
catch (ioexception ioe)
{
throw ioe;
}
finally
{
if (sw != null)
{
sw.close();
}
}
}