datatable用execl导出2.docxVIP

C#将DataTable海量数据导出到Excel 分类：C#2010-08-31 14:443290人阅读评论(2)收藏举报c#excelstringobjectnullexception将DataTable中的大量数据导出到Excel表格中。但每张Excel表单只能有65536行，所以当DataTable数据多于65536行时，Excel要考虑分页功能。通过查阅相关资料，得到导出方法。代码在Office 2003 环境下通过。末验证其它Office版本。[c-sharp]view plaincopyprint?using Excel = Microsoft.Office.Interop.Excel; public Excel.Application m_xlApp = null; /// summary /// 将DataTable数据导出到Excel表/// /summary /// param name=tmpDataTable要导出的DataTable/param /// param name=strFileNameExcel的保存路径及名称/param public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName) { if (tmpDataTable == null) { return; } long rowNum = tmpDataTable.Rows.Count;//行数int columnNum = tmpDataTable.Columns.Count;//列数Excel.Application m_xlApp = new Excel.Application(); m_xlApp.DisplayAlerts = false;//不显示更改提示m_xlApp.Visible = false; Excel.Workbooks workbooks = m_xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 try { if (rowNum 65536)//单张Excel表格最大行数{ long pageRows = 65535;//定义每页显示的行数,行数必须小于65536 int scount = (int)(rowNum / pageRows);//导出数据生成的表单数if (scount * pageRows rowNum)//当总行数不被pageRows整除时，经过四舍五入可能页数不准{ scount = scount + 1; } for (int sc = 1; sc = scount; sc++) { if (sc 1) { object missing = System.Reflection.Missing.Value; worksheet = (Excel.Worksheet)workbook.Worksheets.Add( missing, missing, missing, missing);//添加一个sheet } else { worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1 } string[,] datas = new string[pageRows + 1, columnNum]; for (int i = 0; i columnNum; i++) //写入字段{ datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息} Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]); range.Interior.ColorIndex = 15;//15代表灰色range.Font.Bold = true; range.Font.Size = 9; int init = int.Parse(((sc - 1) * pageRows).ToString()); int r = 0; int index = 0; int result; if (pageRows * sc = rowNum) { result = (int)rowNu