方法一(拷贝直接可以使用适合大批量资料 上万笔)
MicrosoftOfficeInteropExcelApplication appexcel = new MicrosoftOfficeInteropExcelApplication();
SaveFileDialog savefiledialog = new SaveFileDialog();
SystemReflectionMissing miss = SystemReflectionMissingValue;
appexcel = new MicrosoftOfficeInteropExcelApplication();
MicrosoftOfficeInteropExcelWorkbook workbookdata;
MicrosoftOfficeInteropExcelWorksheet worksheetdata;
MicrosoftOfficeInteropExcelRange rangedata;
//设置对象不可见
appexcelVisible = false;
SystemGlobalizationCultureInfo currentci = SystemThreadingThreadCurrentThreadCurrentCulture;
SystemThreadingThreadCurrentThreadCurrentCulture = new SystemGlobalizationCultureInfo(enus);
workbookdata = appexcelWorkbooksAdd(miss);
worksheetdata = (MicrosoftOfficeInteropExcelWorksheet)workbookdataWorksheetsAdd(miss miss miss miss);
//给工作表赋名称
worksheetdataName = saved;
for (int i = ; i < dtColumnsCount; i++)
{
worksheetdataCells[ i + ] = dtColumns[i]ColumnNameToString();
}
//因为第一行已经写了表头所以所有数据都应该从a开始
rangedata = worksheetdataget_Range(a miss);
MicrosoftOfficeInteropExcelRange xlrang = null;
//irowcount为实际行数最大行
int irowcount = dtRowsCount;
int iparstedrow = icurrsize = ;
//ieachsize为每次写行的数值可以自己设置
int ieachsize = ;
//icolumnaccount为实际列数最大列数
int icolumnaccount = dtColumnsCount;
//在内存中声明一个ieachsize×icolumnaccount的数组ieachsize是每次最大存储的行数icolumnaccount就是存储的实际列数
object[] objval = new object[ieachsize icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount iparstedrow) < ieachsize)
icurrsize = irowcount iparstedrow;
//用for循环给数组赋值
for (int i = ; i < icurrsize; i++)
{
for (int j = ; j < icolumnaccount; j++)
objval[i j] = dtRows[i + iparstedrow][j]ToString();
SystemWindowsFormsApplicationDoEvents();
}
string X = A + ((int)(iparstedrow + ))ToString();
string col = ;
if (icolumnaccount <= )
{
col = ((char)(A + icolumnaccount ))ToString() + ((int)(iparstedrow + icurrsize + ))ToString();
}
else
{
col = ((char)(A + (icolumnaccount / )))ToString() + ((char)(A + (icolumnaccount % )))ToString() + ((int)(iparstedrow + icurrsize + ))ToString();
}
xlrang = worksheetdataget_Range(X col);
// 调用range的value属性把内存中的值赋给excel
xlrangValue = objval;
iparstedrow = iparstedrow + icurrsize;
}
//保存工作表
SystemRuntimeInteropServicesMarshalReleaseComObject(xlrang);
xlrang = null;
//调用方法关闭excel进程
appexcelVisible = true;
方法二(自己建函数适合大批量资料 上万笔)
using SystemIO;
private void dataTableToCsv(DataTable table string file)
{
string title = ;
FileStream fs = new FileStream(file FileModeOpenOrCreate);
//FileStream fs = FileOpen(file FileModeOpen FileAccessRead);
StreamWriter sw = new StreamWriter(new BufferedStream(fs) SystemTextEncodingDefault);
for (int i = ; i < tableColumnsCount; i++)
{
title += tableColumns[i]ColumnName + \t; //栏位自动跳到下一单元格
}
title = titleSubstring( titleLength ) + \n;
swWrite(title);
foreach (DataRow row in tableRows)
{
string line = ;
for (int i = ; i < tableColumnsCount; i++)
{
line += row[i]ToString()Trim() + \t; //内容自动跳到下一单元格
}
line = lineSubstring( lineLength ) + \n;
swWrite(line);
}
swClose();
fsClose();
}
dataTableToCsv(dt @c:\xls); //调用函数
SystemDiagnosticsProcessStart(@c:\xls); //打开excel文件
方法三:(可以自己调整单元格的格式适合小批量的数量)
try
{
//没有数据的话就不往下执行
if (dataGridViewRowsCount == )
return;
//实例化一个ExcelApplication对象
MicrosoftOfficeInteropExcelApplication excel = new MicrosoftOfficeInteropExcelApplication();
//让后台执行设置为不可见为true的话会看到打开一个Excel然后数据在往里写
//excelVisible = false;
excelVisible = true;
//新增加一个工作簿Workbook是直接保存不会弹出保存对话框加上Application会弹出保存对话框值为false会报错
excelApplicationWorkbooksAdd(true);
//生成Excel中列头名称
for (int i = ; i < dataGridViewColumnsCount; i++)
{
excelCells[ i + ] = dataGridViewColumns[i]HeaderText;
}
//把DataGridView当前页的数据保存在Excel中
for (int i = ; i < dataGridViewRowsCount ; i++)
{
for (int j = ; j < dataGridViewColumnsCount; j++)
{
if (dataGridView[j i]ValueType == typeof(string))
{
excelCells[i + j + ] = + dataGridView[j i]ValueToString();
}
else
{
excelCells[i + j + ] = dataGridView[j i]ValueToString();
}
}
}
//设置禁止弹出保存和覆盖的询问提示框
excelDisplayAlerts = false;
excelAlertBeforeOverwriting = false;
////保存工作簿
//excelApplicationWorkbooksAdd(true)Save();
////保存excel文件
//excelSave(D: + \\KKHMDxls);
////确保Excel进程关闭
//excelQuit(); //可以直接打开文件
//excel = null;
}
catch (Exception ex)
{
MessageBoxShow(exMessage 错误提示);
}
Exceloutput((DataTable)dataGridViewDataSource);
