C#實(shí)現(xiàn)幾十萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出Excel及Excel各種操作實(shí)例

來(lái)源：本站原創(chuàng)|時(shí)間：2020-01-10|欄目：C#教程|點(diǎn)擊：次

先上導(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è)

復(fù)制代碼代碼如下:

(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í)有所幫助，也希望大家多多支持我們。

上一篇：分享兩種實(shí)現(xiàn)Winform程序的多語(yǔ)言支持的多種解決方案

欄目：C#教程

下一篇：C#知識(shí)整理

本文標(biāo)題：C#實(shí)現(xiàn)幾十萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出Excel及Excel各種操作實(shí)例

本文地址：http://www.jygsgssxh.com/a1/C_jiaocheng/5945.html

更多C#教程

雷火电竞-中国电竞赛事及体育赛事平台

C#教程

C#實(shí)現(xiàn)幾十萬(wàn)級(jí)數(shù)據(jù)導(dǎo)出Excel及Excel各種操作實(shí)例

您可能感興趣的文章

閱讀排行

本欄相關(guān)

隨機(jī)閱讀