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

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

先上导出代码先上导出代码

/// <summary>

/// 导出速度最快

/// </summary>

/// <param name="list"><列名，数据></param>

/// <param name="filepath"></param>

/// <returns></returns>

Microsoft.Office.Interop.Excel.Workbook workbookdata = null;

Microsoft.Office.Interop.Excel.Worksheet worksheetdata = null;

Microsoft.Office.Interop.Excel.Range rangedata;

workbookdata = appexcel.Workbooks.Add();

//设置对象不可见

appexcel.Visible = false;

appexcel.DisplayAlerts = false;

try

{

foreach (var lv in list)

//给工作表赋名称

worksheetdata.Name = keys[0];//列名的第一个数据位表名

worksheetdata.Cells[1, i + 1] = keys[i+1];

}

//因为第一行已经写了表头，所以所有数据都应该从a2开始

rangedata = worksheetdata.get_Range("a2", miss);

Microsoft.Office.Interop.Excel.Range xlrang = null;

//irowcount为实际行数，最大行

int irowcount = values.Count;

int iparstedrow = 0, icurrsize = 0;

icurrsize = ieachsize;

while (iparstedrow < irowcount)

{

if ((irowcount - iparstedrow) < ieachsize)

icurrsize = irowcount - iparstedrow;

//用for循环给数组赋值

for (int i = 0; i < icurrsize; i++)

{

for (int j = 0; j < icolumnaccount; j++)

{

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

((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);

ErrorMsg = ex.Message;

bSuccess = false;

}

finally

{

if (appexcel != null)

{

ExcelImportHelper.KillSpecialExcel(appexcel);

}

}

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 =

Range.Font.Color=clBlue; //字体颜色

xlsApp.DisplayAlerts=false; //对Excel的操作 不弹出提示信息

ApplicationClass xlsApp = new ApplicationClass(); // 1. 创建Excel应用程序对象的一个实例，相当于我们从开始菜单打开Excel应用程序。

if (xlsApp == null)

{

//对此实例进行验证，如果为null则表示运行此代码的机器可能未安装Excel

}

1. 打开现有的Excel文件

Workbook workbook = 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);

的后面。

3.删除sheet页

xlsApp.DisplayAlerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。

(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();

4.选中sheet页

代码如下:

(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing); //选中某个sheet页

5.另存excel文件

workbook.Saved = true;