C#實(shí)現(xiàn)幾十萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出Excel及Excel各種操作實(shí)例
先上導(dǎo)出代碼
/// <summary>
/// 導(dǎo)出速度最快
/// </summary>
/// <param name="list"><列名,數(shù)據(jù)></param>
/// <param name="filepath"></param>
/// <returns></returns>
public bool NewExport(List<DictionaryEntry> list, string filepath)
{
bool bSuccess = true;
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata = null;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata = null;
Microsoft.Office.Interop.Excel.Range rangedata;
workbookdata = appexcel.Workbooks.Add();
//設(shè)置對(duì)象不可見(jiàn)
appexcel.Visible = false;
appexcel.DisplayAlerts = false;
try
{
foreach (var lv in list)
{
var keys = lv.Key as List<string>;
var values = lv.Value as List<IList<object>>;
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, workbookdata.ActiveSheet);
for (int i = 0; i < keys.Count-1; i++)
{
//給工作表賦名稱
worksheetdata.Name = keys[0];//列名的第一個(gè)數(shù)據(jù)位表名
worksheetdata.Cells[1, i + 1] = keys[i+1];
}
//因?yàn)榈谝恍幸呀?jīng)寫(xiě)了表頭,所以所有數(shù)據(jù)都應(yīng)該從a2開(kāi)始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount為實(shí)際行數(shù),最大行
int irowcount = values.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize為每次寫(xiě)行的數(shù)值,可以自己設(shè)置
int ieachsize = 10000;
//icolumnaccount為實(shí)際列數(shù),最大列數(shù)
int icolumnaccount = keys.Count-1;
//在內(nèi)存中聲明一個(gè)ieachsize×icolumnaccount的數(shù)組,ieachsize是每次最大存儲(chǔ)的行數(shù),icolumnaccount就是存儲(chǔ)的實(shí)際列數(shù)
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
icurrsize = irowcount - iparstedrow;
//用for循環(huán)給數(shù)組賦值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
{
var v = values[i + iparstedrow][j];
objval[i, j] = v != null ? v.ToString() : "";
}
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
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 = "@";
// 調(diào)用range的value2屬性,把內(nèi)存中的值賦給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 (Exception ex)
{
ErrorMsg = ex.Message;
bSuccess = false;
}
finally
{
if (appexcel != null)
{
ExcelImportHelper.KillSpecialExcel(appexcel);
}
}
return bSuccess;
}
range.NumberFormatLocal = "@"; //設(shè)置單元格格式為文本
range = (Range)worksheet.get_Range("A1", "E1"); //獲取Excel多個(gè)單元格區(qū)域:本例做為Excel表頭
range.Merge(0); //單元格合并動(dòng)作
worksheet.Cells[1, 1] = "Excel單元格賦值"; //Excel單元格賦值
range.Font.Size = 15; //設(shè)置字體大小
range.Font.Underline=true; //設(shè)置字體是否有下劃線
range.Font.Name="黑體"; 設(shè)置字體的種類
range.HorizontalAlignment=XlHAlign.xlHAlignCenter; //設(shè)置字體在單元格內(nèi)的對(duì)其方式
range.ColumnWidth=15; //設(shè)置單元格的寬度
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb(); //設(shè)置單元格的背景色
range.Borders.LineStyle=1; //設(shè)置單元格邊框的粗細(xì)
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; //設(shè)置單元格上邊框?yàn)闊o(wú)邊框
range.EntireColumn.AutoFit(); //自動(dòng)調(diào)整列寬
Range.HorizontalAlignment= xlCenter; // 文本水平居中方式
Range.VerticalAlignment= xlCenter //文本垂直居中方式
Range.WrapText=true; //文本自動(dòng)換行
Range.Interior.ColorIndex=39; //填充顏色為淡紫色
Range.Font.Color=clBlue; //字體顏色
xlsApp.DisplayAlerts=false; //對(duì)Excel的操作 不彈出提示信息
ApplicationClass xlsApp = new ApplicationClass(); // 1. 創(chuàng)建Excel應(yīng)用程序?qū)ο蟮囊粋€(gè)實(shí)例,相當(dāng)于我們從開(kāi)始菜單打開(kāi)Excel應(yīng)用程序。
if (xlsApp == null)
{
//對(duì)此實(shí)例進(jìn)行驗(yàn)證,如果為null則表示運(yùn)行此代碼的機(jī)器可能未安裝Excel
}
1. 打開(kāi)現(xiàn)有的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); Worksheet mySheet = workbook.Sheets[1] as Worksheet; //第一個(gè)sheet頁(yè) mySheet.Name = "testsheet"; //這里修改sheet名稱
2.復(fù)制sheet頁(yè)
mySheet.Copy(Type.Missing, workbook.Sheets[1]); //復(fù)制mySheet成一個(gè)新的sheet頁(yè),復(fù)制完后的名稱是mySheet頁(yè)名稱后加一個(gè)(2),這里就是testsheet(2),復(fù)制完后,Worksheet的數(shù)量增加一個(gè)
注意 這里Copy方法的兩個(gè)參數(shù),指是的復(fù)制出來(lái)新的sheet頁(yè)是在指定sheet頁(yè)的前面還是后面,上面的例子就是指復(fù)制的sheet頁(yè)在第一個(gè)sheet頁(yè)的后面。
3.刪除sheet頁(yè)
xlsApp.DisplayAlerts = false; //如果想刪除某個(gè)sheet頁(yè),首先要將此項(xiàng)設(shè)為fasle。 (xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();
4.選中sheet頁(yè)
(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing); //選中某個(gè)sheet頁(yè)
5.另存excel文件
workbook.Saved = true; workbook.SaveCopyAs(filepath);
6.釋放excel資源
workbook.Close(true, Type.Missing, Type.Missing); workbook = null; xlsApp.Quit(); xlsApp = null;
方法2:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
namespace ExcelTest
{
public class ExcelUtil
{
System.Data.DataTable table11 = new System.Data.DataTable();
public void ExportToExcel(System.Data.DataTable table, string saveFileName)
{
bool fileSaved = false;
//ExcelApp xlApp = new ExcelApp();
Application xlApp = new Application();
if (xlApp == null)
{
return;
}
Workbooks workbooks = xlApp.Workbooks;
Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1
long rows = table.Rows.Count;
/*下邊注釋的兩行代碼當(dāng)數(shù)據(jù)行數(shù)超過(guò)行時(shí),出現(xiàn)異常:異常來(lái)自HRESULT:0x800A03EC。因?yàn)椋篍xcel 2003每個(gè)sheet只支持最大行數(shù)據(jù)
//Range fchR = 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)
{
long pageRows = 60000;//定義每頁(yè)顯示的行數(shù),行數(shù)必須小于
int scount = (int)(rows / pageRows);
if (scount * pageRows < table.Rows.Count)//當(dāng)總行數(shù)不被pageRows整除時(shí),經(jīng)過(guò)四舍五入可能頁(yè)數(shù)不準(zhǔn)
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing);//添加一個(gè)sheet
}
else
{
worksheet = (Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, table.Columns.Count+ 1];
for (int i = 0; i < table.Columns.Count; i++) //寫(xiě)入字段
{
datas[0, i] = table.Columns[i].Caption;
}
Range range = 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;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
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 (int i = 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))
{
object obj = table.Rows[r][table.Columns[i].ColumnName];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加單引號(hào)是為了防止自動(dòng)轉(zhuǎn)化格式
}
}
}
Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 2, table.Columns.Count + 1]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列寬自適應(yīng)。
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 = new string[table.Rows.Count + 2, table.Columns.Count + 1];
for (int i = 0; i < table.Columns.Count; i++) //寫(xiě)入字段
{
datas[0, i] = table.Columns[i].Caption;
}
Range range = 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;
int r = 0;
for (r = 0; r < table.Rows.Count; r++)
{
for (int i = 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))
{
object obj = table.Rows[r][table.Columns[i].ColumnName];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加單引號(hào)是為了防止自動(dòng)轉(zhuǎn)化格式
}
}
//System.Windows.Forms.Application.DoEvents();
}
Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 1]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列寬自適應(yīng)。
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 (Exception ex)
{
fileSaved = false;
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
GC.Collect();//強(qiáng)行銷毀
}
}
}
方法3:
先去官網(wǎng):http://npoi.codeplex.com/下載需要引入dll(可以選擇.net2.0或者.net4.0的dll),然后在網(wǎng)站中添加引用。
導(dǎo)出代碼:
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");
// 第一列
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("第一列第一行");
// 第二列
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("第二列第一行");
// ...
// 寫(xiě)入到客戶端
System.IO.MemoryStream ms = new System.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();
導(dǎo)入代碼:
HSSFWorkbook hssfworkbook;
#region
public DataTable ImportExcelFile(string filePath)
{
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.Cell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
#endregion
用法:
首先建立一個(gè)空白的工作簿用作測(cè)試,并在其中建立空白工作表,在表中建立空白行,在行中建立單元格,并填入內(nèi)容:
//建立空白工作簿
IWorkbook workbook = new HSSFWorkbook();
//在工作簿中:建立空白工作表
ISheet sheet = workbook.CreateSheet();
//在工作表中:建立行,參數(shù)為行號(hào),從0計(jì)
IRow row = sheet.CreateRow(0);
//在行中:建立單元格,參數(shù)為列號(hào),從0計(jì)
ICell cell = row.CreateCell(0);
//設(shè)置單元格內(nèi)容
cell.SetCellValue("實(shí)習(xí)鑒定表");
設(shè)置單元格樣式:設(shè)置單元格樣式時(shí)需要注意,務(wù)必創(chuàng)建一個(gè)新的樣式對(duì)象進(jìn)行設(shè)置,否則會(huì)將工作表所有單元格的樣式一同設(shè)置,它們應(yīng)該共享的是一個(gè)樣式對(duì)象:
ICellStyle style = workbook.CreateCellStyle(); //設(shè)置單元格的樣式:水平對(duì)齊居中 style.Alignment = HorizontalAlignment.CENTER; //新建一個(gè)字體樣式對(duì)象 IFont font = workbook.CreateFont(); //設(shè)置字體加粗樣式 font.Boldweight = short.MaxValue; //使用SetFont方法將字體樣式添加到單元格樣式中 style.SetFont(font); //將新的樣式賦給單元格 cell.CellStyle = style;
設(shè)置單元格寬高:
設(shè)置單元格的高度實(shí)際是設(shè)置其所在行高,所以要在單元格所在行上設(shè)置行高,行高設(shè)置數(shù)值好像是像素點(diǎn)的1/20,所以*20以便達(dá)到設(shè)置效果;
設(shè)置單元格的寬度實(shí)際上是設(shè)置其所在列寬,所以要在單元格所在列上設(shè)置(列的設(shè)置在工作表上),寬度數(shù)值好像是字符的1/256,所以*256以便達(dá)到設(shè)置效果。
//設(shè)置單元格的高度 row.Height = 30 * 20; //設(shè)置單元格的寬度 sheet.SetColumnWidth(0, 30 * 256);
合并單元格:合并單元格實(shí)際上是聲明一個(gè)區(qū)域,該區(qū)域中的單元格將進(jìn)行合并,合并后的內(nèi)容與樣式以該區(qū)域最左上角的單元格為準(zhǔn)。
//設(shè)置一個(gè)合并單元格區(qū)域,使用上下左右定義CellRangeAddress區(qū)域 //CellRangeAddress四個(gè)參數(shù)為:起始行,結(jié)束行,起始列,結(jié)束列 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
添加公式:使用Cell的CellFormula來(lái)設(shè)置公式,是一個(gè)字符串,公式前不需要加=號(hào)。
//通過(guò)Cell的CellFormula向單元格中寫(xiě)入公式 //注:直接寫(xiě)公式內(nèi)容即可,不需要在最前加'=' ICell cell2 = sheet.CreateRow(1).CreateCell(0); cell2.CellFormula = "HYPERLINK(\"測(cè)試圖片.jpg\",\"測(cè)試圖片.jpg\")";
將工作簿寫(xiě)入文件查看效果:
//將工作簿寫(xiě)入文件
using (FileStream fs = new FileStream("生成效果.xls", FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
以上就是本文的全部?jī)?nèi)容,希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。
相關(guān)文章
如何在UpdatePanel中調(diào)用JS客戶端腳本
本文將介紹如何在UpdatePanel中調(diào)用JS客戶端腳本,需要了解的朋友可以參考下2012-12-12
C#訪問(wèn)及調(diào)用類中私有成員與方法示例代碼
訪問(wèn)一個(gè)類的私有成員不是什么好做法,大家也都知道私有成員在外部是不能被訪問(wèn)的,這篇文章主要給大家介紹了關(guān)于C#訪問(wèn)及調(diào)用類中私有成員與方法的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2018-06-06
C#動(dòng)態(tài)對(duì)象(dynamic)詳解(實(shí)現(xiàn)方法和屬性的動(dòng)態(tài))
下面小編就為大家?guī)?lái)一篇C#動(dòng)態(tài)對(duì)象(dynamic)詳解(實(shí)現(xiàn)方法和屬性的動(dòng)態(tài))。小編覺(jué)得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-02-02
C#開(kāi)發(fā)Winform程序調(diào)用存儲(chǔ)過(guò)程
這篇文章介紹了C#開(kāi)發(fā)Winform程序調(diào)用存儲(chǔ)過(guò)程的方法,文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-05-05
winform開(kāi)發(fā)使用通用多線程基類分享(以隊(duì)列形式)
多線程這個(gè)概念大家都很熟悉,對(duì)于winform的開(kāi)發(fā)人員來(lái)說(shuō),用的還是多的.但估計(jì)都是用Timer,或者backgroundWorker,為大家寫(xiě)了一個(gè)多線程的基類,只有你用到多線程拿過(guò)來(lái)就可以用了2013-12-12
如何用C#實(shí)現(xiàn)SAGA分布式事務(wù)
大家好,本篇文章主要講的是如何用C#實(shí)現(xiàn)SAGA分布式事務(wù),感興趣的同學(xué)趕快來(lái)看一看吧,對(duì)你有幫助的話記得收藏一下2022-01-01

