C#实现几十万级数据导出Excel及Excel各种操作实例
先上导出代码
///<summary> ///导出速度最快 ///</summary> ///<paramname="list"><列名,数据></param> ///<paramname="filepath"></param> ///<returns></returns> publicboolNewExport(List<DictionaryEntry>list,stringfilepath) { boolbSuccess=true; Microsoft.Office.Interop.Excel.Applicationappexcel=newMicrosoft.Office.Interop.Excel.Application(); System.Reflection.Missingmiss=System.Reflection.Missing.Value; appexcel=newMicrosoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbookworkbookdata=null; Microsoft.Office.Interop.Excel.Worksheetworksheetdata=null; Microsoft.Office.Interop.Excel.Rangerangedata; workbookdata=appexcel.Workbooks.Add(); //设置对象不可见 appexcel.Visible=false; appexcel.DisplayAlerts=false; try { foreach(varlvinlist) { varkeys=lv.KeyasList<string>; varvalues=lv.ValueasList<IList<object>>; worksheetdata=(Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss,workbookdata.ActiveSheet); for(inti=0;i<keys.Count-1;i++) { //给工作表赋名称 worksheetdata.Name=keys[0];//列名的第一个数据位表名 worksheetdata.Cells[1,i+1]=keys[i+1]; } //因为第一行已经写了表头,所以所有数据都应该从a2开始 rangedata=worksheetdata.get_Range("a2",miss); Microsoft.Office.Interop.Excel.Rangexlrang=null; //irowcount为实际行数,最大行 intirowcount=values.Count; intiparstedrow=0,icurrsize=0; //ieachsize为每次写行的数值,可以自己设置 intieachsize=10000; //icolumnaccount为实际列数,最大列数 inticolumnaccount=keys.Count-1; //在内存中声明一个ieachsize×icolumnaccount的数组,ieachsize是每次最大存储的行数,icolumnaccount就是存储的实际列数 object[,]objval=newobject[ieachsize,icolumnaccount]; icurrsize=ieachsize; while(iparstedrow<irowcount) { if((irowcount-iparstedrow)<ieachsize) icurrsize=irowcount-iparstedrow; //用for循环给数组赋值 for(inti=0;i<icurrsize;i++) { for(intj=0;j<icolumnaccount;j++) { varv=values[i+iparstedrow][j]; objval[i,j]=v!=null?v.ToString():""; } } stringX="A"+((int)(iparstedrow+2)).ToString(); stringcol=""; if(icolumnaccount<=26) { col=((char)('A'+icolumnaccount-1)).ToString()+((int)(iparstedrow+icurrsize+1)).ToString(); } else { col=((char)('A'+(icolumnaccount/26-1))).ToString()+((char)('A'+(icolumnaccount%26-1))).ToString()+((int)(iparstedrow+icurrsize+1)).ToString(); } xlrang=worksheetdata.get_Range(X,col); xlrang.NumberFormat="@"; //调用range的value2属性,把内存中的值赋给excel xlrang.Value2=objval; iparstedrow=iparstedrow+icurrsize; } } ((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet1"]).Delete(); ((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet2"]).Delete(); ((Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets["Sheet3"]).Delete(); //保存工作表 workbookdata.SaveAs(filepath,miss,miss,miss,miss,miss,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,miss,miss,miss); workbookdata.Close(false,miss,miss); appexcel.Workbooks.Close(); appexcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookdata); System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel.Workbooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel); GC.Collect(); } catch(Exceptionex) { ErrorMsg=ex.Message; bSuccess=false; } finally { if(appexcel!=null) { ExcelImportHelper.KillSpecialExcel(appexcel); } } returnbSuccess; }
range.NumberFormatLocal="@";//设置单元格格式为文本 range=(Range)worksheet.get_Range("A1","E1");//获取Excel多个单元格区域:本例做为Excel表头 range.Merge(0);//单元格合并动作 worksheet.Cells[1,1]="Excel单元格赋值";//Excel单元格赋值 range.Font.Size=15;//设置字体大小 range.Font.Underline=true;//设置字体是否有下划线 range.Font.Name="黑体";设置字体的种类 range.HorizontalAlignment=XlHAlign.xlHAlignCenter;//设置字体在单元格内的对其方式 range.ColumnWidth=15;//设置单元格的宽度 range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();//设置单元格的背景色 range.Borders.LineStyle=1;//设置单元格边框的粗细 range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());//给单元格加边框 range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle=Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;//设置单元格上边框为无边框 range.EntireColumn.AutoFit();//自动调整列宽 Range.HorizontalAlignment=xlCenter;//文本水平居中方式 Range.VerticalAlignment=xlCenter//文本垂直居中方式 Range.WrapText=true;//文本自动换行 Range.Interior.ColorIndex=39;//填充颜色为淡紫色 Range.Font.Color=clBlue;//字体颜色 xlsApp.DisplayAlerts=false;//对Excel的操作不弹出提示信息 ApplicationClassxlsApp=newApplicationClass();//1.创建Excel应用程序对象的一个实例,相当于我们从开始菜单打开Excel应用程序。 if(xlsApp==null) { //对此实例进行验证,如果为null则表示运行此代码的机器可能未安装Excel }
1.打开现有的Excel文件
Workbookworkbook=xlsApp.Workbooks.Open(excelFilePath,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); WorksheetmySheet=workbook.Sheets[1]asWorksheet;//第一个sheet页 mySheet.Name="testsheet";//这里修改sheet名称
2.复制sheet页
mySheet.Copy(Type.Missing,workbook.Sheets[1]); //复制mySheet成一个新的sheet页,复制完后的名称是mySheet页名称后加一个(2),这里就是testsheet(2),复制完后,Worksheet的数量增加一个
注意这里Copy方法的两个参数,指是的复制出来新的sheet页是在指定sheet页的前面还是后面,上面的例子就是指复制的sheet页在第一个sheet页的后面。
3.删除sheet页
xlsApp.DisplayAlerts=false;//如果想删除某个sheet页,首先要将此项设为fasle。 (xlsApp.ActiveWorkbook.Sheets[1]asWorksheet).Delete();
4.选中sheet页
(xlsApp.ActiveWorkbook.Sheets[1]asWorksheet).Select(Type.Missing);//选中某个sheet页
5.另存excel文件
workbook.Saved=true; workbook.SaveCopyAs(filepath);
6.释放excel资源
workbook.Close(true,Type.Missing,Type.Missing); workbook=null; xlsApp.Quit(); xlsApp=null;
方法2:
usingSystem; usingSystem.Collections.Generic; usingSystem.Linq; usingSystem.Text; usingMicrosoft.Office.Interop.Excel; usingSystem.Data; namespaceExcelTest { publicclassExcelUtil { System.Data.DataTabletable11=newSystem.Data.DataTable(); publicvoidExportToExcel(System.Data.DataTabletable,stringsaveFileName) { boolfileSaved=false; //ExcelAppxlApp=newExcelApp(); ApplicationxlApp=newApplication(); if(xlApp==null) { return; } Workbooksworkbooks=xlApp.Workbooks; Workbookworkbook=workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheetworksheet=(Worksheet)workbook.Worksheets[1];//取得sheet1 longrows=table.Rows.Count; /*下边注释的两行代码当数据行数超过行时,出现异常:异常来自HRESULT:0x800A03EC。因为:Excel2003每个sheet只支持最大行数据 //RangefchR=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[table.Rows.Count+2,gridview.Columns.View.VisibleColumns.Count+1]); //fchR.Value2=datas;*/ if(rows>65535) { longpageRows=60000;//定义每页显示的行数,行数必须小于 intscount=(int)(rows/pageRows); if(scount*pageRows<table.Rows.Count)//当总行数不被pageRows整除时,经过四舍五入可能页数不准 { scount=scount+1; } for(intsc=1;sc<=scount;sc++) { if(sc>1) { objectmissing=System.Reflection.Missing.Value; worksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add( missing,missing,missing,missing);//添加一个sheet } else { worksheet=(Worksheet)workbook.Worksheets[sc];//取得sheet1 } string[,]datas=newstring[pageRows+1,table.Columns.Count+1]; for(inti=0;i<table.Columns.Count;i++)//写入字段 { datas[0,i]=table.Columns[i].Caption; } Rangerange=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,table.Columns.Count]); range.Interior.ColorIndex=15;//15代表灰色 range.Font.Bold=true; range.Font.Size=9; intinit=int.Parse(((sc-1)*pageRows).ToString()); intr=0; intindex=0; intresult; if(pageRows*sc>=table.Rows.Count) { result=table.Rows.Count; } else { result=int.Parse((pageRows*sc).ToString()); } for(r=init;r<result;r++) { index=index+1; for(inti=0;i<table.Columns.Count;i++) { if(table.Columns[i].DataType==typeof(string)||table.Columns[i].DataType==typeof(Decimal)||table.Columns[i].DataType==typeof(DateTime)) { objectobj=table.Rows[r][table.Columns[i].ColumnName]; datas[index,i]=obj==null?"":"'"+obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } } } RangefchR=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[index+2,table.Columns.Count+1]); fchR.Value2=datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 range=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[index+1,table.Columns.Count]); //15代表灰色 range.Font.Size=9; range.RowHeight=14.25; range.Borders.LineStyle=1; range.HorizontalAlignment=1; } } else { string[,]datas=newstring[table.Rows.Count+2,table.Columns.Count+1]; for(inti=0;i<table.Columns.Count;i++)//写入字段 { datas[0,i]=table.Columns[i].Caption; } Rangerange=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,table.Columns.Count]); range.Interior.ColorIndex=15;//15代表灰色 range.Font.Bold=true; range.Font.Size=9; intr=0; for(r=0;r<table.Rows.Count;r++) { for(inti=0;i<table.Columns.Count;i++) { if(table.Columns[i].DataType==typeof(string)||table.Columns[i].DataType==typeof(Decimal)||table.Columns[i].DataType==typeof(DateTime)) { objectobj=table.Rows[r][table.Columns[i].ColumnName]; datas[r+1,i]=obj==null?"":"'"+obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式 } } //System.Windows.Forms.Application.DoEvents(); } RangefchR=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[table.Rows.Count+2,table.Columns.Count+1]); fchR.Value2=datas; worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 range=worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[table.Rows.Count+1,table.Columns.Count]); //15代表灰色 range.Font.Size=9; range.RowHeight=14.25; range.Borders.LineStyle=1; range.HorizontalAlignment=1; } if(saveFileName!="") { try { workbook.Saved=true; workbook.SaveCopyAs(saveFileName); fileSaved=true; } catch(Exceptionex) { fileSaved=false; } } else { fileSaved=false; } xlApp.Quit(); GC.Collect();//强行销毁 } } }
方法3:
先去官网:http://npoi.codeplex.com/下载需要引入dll(可以选择.net2.0或者.net4.0的dll),然后在网站中添加引用。
导出代码:
NPOI.HSSF.UserModel.HSSFWorkbookbook=newNPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheetsheet=book.CreateSheet("test_01"); //第一列 NPOI.SS.UserModel.IRowrow=sheet.CreateRow(0); row.CreateCell(0).SetCellValue("第一列第一行"); //第二列 NPOI.SS.UserModel.IRowrow2=sheet.CreateRow(1); row2.CreateCell(0).SetCellValue("第二列第一行"); //... //写入到客户端 System.IO.MemoryStreamms=newSystem.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition",string.Format("attachment;filename={0}.xls",DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.BinaryWrite(ms.ToArray()); book=null; ms.Close(); ms.Dispose();
导入代码:
HSSFWorkbookhssfworkbook; #region publicDataTableImportExcelFile(stringfilePath) { #region//初始化信息 try { using(FileStreamfile=newFileStream(filePath,FileMode.Open,FileAccess.Read)) { hssfworkbook=newHSSFWorkbook(file); } } catch(Exceptione) { throwe; } #endregion NPOI.SS.UserModel.Sheetsheet=hssfworkbook.GetSheetAt(0); System.Collections.IEnumeratorrows=sheet.GetRowEnumerator(); DataTabledt=newDataTable(); for(intj=0;j<(sheet.GetRow(0).LastCellNum);j++) { dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString()); } while(rows.MoveNext()) { HSSFRowrow=(HSSFRow)rows.Current; DataRowdr=dt.NewRow(); for(inti=0;i<row.LastCellNum;i++) { NPOI.SS.UserModel.Cellcell=row.GetCell(i); if(cell==null) { dr[i]=null; } else { dr[i]=cell.ToString(); } } dt.Rows.Add(dr); } returndt; } #endregion
用法:
首先建立一个空白的工作簿用作测试,并在其中建立空白工作表,在表中建立空白行,在行中建立单元格,并填入内容:
//建立空白工作簿 IWorkbookworkbook=newHSSFWorkbook(); //在工作簿中:建立空白工作表 ISheetsheet=workbook.CreateSheet(); //在工作表中:建立行,参数为行号,从0计 IRowrow=sheet.CreateRow(0); //在行中:建立单元格,参数为列号,从0计 ICellcell=row.CreateCell(0); //设置单元格内容 cell.SetCellValue("实习鉴定表");
设置单元格样式:设置单元格样式时需要注意,务必创建一个新的样式对象进行设置,否则会将工作表所有单元格的样式一同设置,它们应该共享的是一个样式对象:
ICellStylestyle=workbook.CreateCellStyle(); //设置单元格的样式:水平对齐居中 style.Alignment=HorizontalAlignment.CENTER; //新建一个字体样式对象 IFontfont=workbook.CreateFont(); //设置字体加粗样式 font.Boldweight=short.MaxValue; //使用SetFont方法将字体样式添加到单元格样式中 style.SetFont(font); //将新的样式赋给单元格 cell.CellStyle=style;
设置单元格宽高:
设置单元格的高度实际是设置其所在行高,所以要在单元格所在行上设置行高,行高设置数值好像是像素点的1/20,所以*20以便达到设置效果;
设置单元格的宽度实际上是设置其所在列宽,所以要在单元格所在列上设置(列的设置在工作表上),宽度数值好像是字符的1/256,所以*256以便达到设置效果。
//设置单元格的高度 row.Height=30*20; //设置单元格的宽度 sheet.SetColumnWidth(0,30*256);
合并单元格:合并单元格实际上是声明一个区域,该区域中的单元格将进行合并,合并后的内容与样式以该区域最左上角的单元格为准。
//设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域 //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列 sheet.AddMergedRegion(newCellRangeAddress(0,0,0,10));
添加公式:使用Cell的CellFormula来设置公式,是一个字符串,公式前不需要加=号。
//通过Cell的CellFormula向单元格中写入公式 //注:直接写公式内容即可,不需要在最前加'=' ICellcell2=sheet.CreateRow(1).CreateCell(0); cell2.CellFormula="HYPERLINK(\"测试图片.jpg\",\"测试图片.jpg\")";
将工作簿写入文件查看效果:
//将工作簿写入文件 using(FileStreamfs=newFileStream("生成效果.xls",FileMode.Create,FileAccess.Write)) { workbook.Write(fs); }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。