C#实现几十万级数据导出Excel及Excel各种操作实例

2024-03-01 07:20:02 273

先上导出代码

///<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);
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

C#实现几十万级数据导出Excel及Excel各种操作实例

热门推荐

随机推荐