C realizes hundreds of thousands of levels of data export Excel and Excel various operation examples

  • 2021-12-04 19:33:52
  • OfStack

Export code up first


  /// <summary> 
      ///  The export speed is the fastest  
      /// </summary> 
      /// <param name="list">< Column name, data ></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(); 
   
        // Setting objects not visible  
        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++) 
            { 
              // Assign a name to the worksheet  
              worksheetdata.Name = keys[0];// The first part of the column name 1 Table name of data bits  
              worksheetdata.Cells[1, i + 1] = keys[i+1]; 
            } 
   
            // Because the first 1 The row has written the header, so all the data should be derived from the a2 Begin  
            rangedata = worksheetdata.get_Range("a2", miss); 
            Microsoft.Office.Interop.Excel.Range xlrang = null; 
   
            //irowcount Is the actual number of lines, the maximum line  
            int irowcount = values.Count; 
            int iparstedrow = 0, icurrsize = 0; 
   
            //ieachsize The value of each line written, which can be set by yourself  
            int ieachsize = 10000; 
   
            //icolumnaccount Is the actual number of columns, the maximum number of columns  
            int icolumnaccount = keys.Count-1; 
   
            // Declare in memory 1 A ieachsize × icolumnaccount The array of the, ieachsize Is the maximum number of rows stored at a time, icolumnaccount Is the actual number of columns stored  
            object[,] objval = new object[ieachsize, icolumnaccount]; 
            icurrsize = ieachsize; 
   
            while (iparstedrow < irowcount) 
            { 
              if ((irowcount - iparstedrow) < ieachsize) 
                icurrsize = irowcount - iparstedrow; 
   
              // Use for Loop to assign values to arrays  
              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 = "@"; 
              //  Call range Adj. value2 Property, assign the value in memory to the 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(); 
          // Save worksheet  
          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 = "@";   // Format cells to text    
  
range = (Range)worksheet.get_Range("A1", "E1");   // Get Excel Multiple cell ranges: This example is used as Excel Header    
  
range.Merge(0);   // Cell merge action    
  
worksheet.Cells[1, 1] = "Excel Cell assignment ";   //Excel Cell assignment    
  
range.Font.Size = 15;   // Set the font size    
  
range.Font.Underline=true;   // Set whether the font is underlined    
  
range.Font.Name=" Blackbody ";     Set the type of font    
  
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;   // Sets the way fonts are aligned within cells    
  
range.ColumnWidth=15;   // Set the width of the cell    
  
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();   // Set the background color of the cell    
  
range.Borders.LineStyle=1;   // Set the thickness of the cell border    
  
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());   // To border a cell    
  
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; // Set the top border of the cell to no border    
  
range.EntireColumn.AutoFit();   // Automatically adjust column width    
  
Range.HorizontalAlignment= xlCenter;   //  Horizontal centering of text    
  
Range.VerticalAlignment= xlCenter   // Vertical centering of text    
  
Range.WrapText=true;   // Word wrapping of text    
  
Range.Interior.ColorIndex=39;   // The filling color is lavender    
  
Range.Font.Color=clBlue;   // Font color    
  
xlsApp.DisplayAlerts=false;  // Right Excel Operation of   Do not pop up prompt information  
ApplicationClass xlsApp = new ApplicationClass(); // 1.  Create Excel Object of the application object 1 Instance, which is equivalent to opening it from the Start menu Excel Applications.  
if (xlsApp == null) 
{ 
// Validate this instance, if null Indicates that the machine on which this code is running may not be installed Excel 
} 

1. Open an existing Excel file


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; // No. 1 1 A sheet Page  
mySheet.Name = "testsheet"; // Modify here sheet Name  

2. Copy sheet pages


mySheet.Copy(Type.Missing, workbook.Sheets[1]);
// Duplicate mySheet Cheng 1 A new one sheet Page, and the name after copying is mySheet After the page name, add 1 A (2) , this is it testsheet(2) After copying, Worksheet Increase in the number of 1 A 

Note that the two parameters of the Copy method here indicate whether the new sheet page is copied before or after the specified sheet page. In the above example, the copied sheet page is copied after the first sheet page.

3. Delete sheet page


xlsApp.DisplayAlerts = false; // If you want to delete a sheet Page, you first set this item to fasle .  
(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete(); 

4. Select the sheet page


(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing); // Select a sheet Page  

5. Save the excel file


workbook.Saved = true; 
workbook.SaveCopyAs(filepath); 

6. Release excel resources


workbook.Close(true, Type.Missing, Type.Missing); 
workbook = null; 
xlsApp.Quit(); 
xlsApp = null;

Method 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];// Acquire sheet1

      long rows = table.Rows.Count;

      /* When the number of data lines exceeds the number of lines, an exception occurs in the two lines of code commented below: the exception comes from HRESULT:0x800A03EC . Because: Excel 2003 Each sheet Only the largest rows of data are supported 

      //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;// Define the number of rows displayed per page , Number of rows must be less than 

        int scount = (int)(rows / pageRows);

        if (scount * pageRows < table.Rows.Count)// When the total number of rows is not pageRows When divisible, pass by 4 Shed 5 Inaccurate number of possible pages 
        {
          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);// Add 1 A sheet

          }

          else
          {
            worksheet = (Worksheet)workbook.Worksheets[sc];// Acquire sheet1
          }

          string[,] datas = new string[pageRows + 1, table.Columns.Count+ 1];

for (int i = 0; i < table.Columns.Count; i++) // Write field 
          {
            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 Represents grey 
          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();// In obj.ToString() Single quotation marks are placed before them to prevent automatic format conversion 

              }

            }
          }

          Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 2, table.Columns.Count + 1]);

          fchR.Value2 = datas;
          worksheet.Columns.EntireColumn.AutoFit();// Column width adaptation. 

          range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, table.Columns.Count]);

          //15 Represents grey 

          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++) // Write field      
        {
          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 Represents grey 
        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();// In obj.ToString() Single quotation marks are placed before them to prevent automatic format conversion 

            }

          }

          //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();// Column width adaptation. 

        range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, table.Columns.Count]);

        //15 Represents grey 

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

Method 3:

Go to official website first: http://npoi.codeplex.com/Download needs to introduce dll (you can choose dll of. net2.0 or. net4.0), and then add references to the website.

Export code:


NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");

//  No. 1 1 Column 
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(" No. 1 1 Column 1 Row ");

//  No. 1 2 Column 
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue(" No. 1 2 Column 1 Row ");

// ...

//  Write to client  
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();

Import code:


HSSFWorkbook hssfworkbook; 
#region 
public DataTable ImportExcelFile(string filePath) 
{ 
  #region// Initialization information  
  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

Usage:

First, create a blank workbook as a test, and create a blank worksheet in it, create a blank row in the table, create a cell in the row, and fill in the contents:


range.NumberFormatLocal = "@";   // Format cells to text    
  
range = (Range)worksheet.get_Range("A1", "E1");   // Get Excel Multiple cell ranges: This example is used as Excel Header    
  
range.Merge(0);   // Cell merge action    
  
worksheet.Cells[1, 1] = "Excel Cell assignment ";   //Excel Cell assignment    
  
range.Font.Size = 15;   // Set the font size    
  
range.Font.Underline=true;   // Set whether the font is underlined    
  
range.Font.Name=" Blackbody ";     Set the type of font    
  
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;   // Sets the way fonts are aligned within cells    
  
range.ColumnWidth=15;   // Set the width of the cell    
  
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();   // Set the background color of the cell    
  
range.Borders.LineStyle=1;   // Set the thickness of the cell border    
  
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());   // To border a cell    
  
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; // Set the top border of the cell to no border    
  
range.EntireColumn.AutoFit();   // Automatically adjust column width    
  
Range.HorizontalAlignment= xlCenter;   //  Horizontal centering of text    
  
Range.VerticalAlignment= xlCenter   // Vertical centering of text    
  
Range.WrapText=true;   // Word wrapping of text    
  
Range.Interior.ColorIndex=39;   // The filling color is lavender    
  
Range.Font.Color=clBlue;   // Font color    
  
xlsApp.DisplayAlerts=false;  // Right Excel Operation of   Do not pop up prompt information  
ApplicationClass xlsApp = new ApplicationClass(); // 1.  Create Excel Object of the application object 1 Instance, which is equivalent to opening it from the Start menu Excel Applications.  
if (xlsApp == null) 
{ 
// Validate this instance, if null Indicates that the machine on which this code is running may not be installed Excel 
} 
0

Setting cell style: When setting cell style, you should pay attention to creating a new style object for setting, otherwise, the style 1 of all cells in the worksheet will be set together, and they should share a style object:


range.NumberFormatLocal = "@";   // Format cells to text    
  
range = (Range)worksheet.get_Range("A1", "E1");   // Get Excel Multiple cell ranges: This example is used as Excel Header    
  
range.Merge(0);   // Cell merge action    
  
worksheet.Cells[1, 1] = "Excel Cell assignment ";   //Excel Cell assignment    
  
range.Font.Size = 15;   // Set the font size    
  
range.Font.Underline=true;   // Set whether the font is underlined    
  
range.Font.Name=" Blackbody ";     Set the type of font    
  
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;   // Sets the way fonts are aligned within cells    
  
range.ColumnWidth=15;   // Set the width of the cell    
  
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();   // Set the background color of the cell    
  
range.Borders.LineStyle=1;   // Set the thickness of the cell border    
  
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());   // To border a cell    
  
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; // Set the top border of the cell to no border    
  
range.EntireColumn.AutoFit();   // Automatically adjust column width    
  
Range.HorizontalAlignment= xlCenter;   //  Horizontal centering of text    
  
Range.VerticalAlignment= xlCenter   // Vertical centering of text    
  
Range.WrapText=true;   // Word wrapping of text    
  
Range.Interior.ColorIndex=39;   // The filling color is lavender    
  
Range.Font.Color=clBlue;   // Font color    
  
xlsApp.DisplayAlerts=false;  // Right Excel Operation of   Do not pop up prompt information  
ApplicationClass xlsApp = new ApplicationClass(); // 1.  Create Excel Object of the application object 1 Instance, which is equivalent to opening it from the Start menu Excel Applications.  
if (xlsApp == null) 
{ 
// Validate this instance, if null Indicates that the machine on which this code is running may not be installed Excel 
} 
1

Set cell width and height:

Setting the height of a cell is actually setting the height of its row, so it is necessary to set the row height on the row where the cell is located. The value of setting the row height seems to be 1/20 of the pixel point, so * 20 in order to achieve the setting effect;

Setting the width of a cell is actually setting the width of the column in which it is located, so to set it on the column in which the cell is located (the column is set on the worksheet), the width value seems to be 1/256 of the character, so * 256 for the setting effect.


range.NumberFormatLocal = "@";   // Format cells to text    
  
range = (Range)worksheet.get_Range("A1", "E1");   // Get Excel Multiple cell ranges: This example is used as Excel Header    
  
range.Merge(0);   // Cell merge action    
  
worksheet.Cells[1, 1] = "Excel Cell assignment ";   //Excel Cell assignment    
  
range.Font.Size = 15;   // Set the font size    
  
range.Font.Underline=true;   // Set whether the font is underlined    
  
range.Font.Name=" Blackbody ";     Set the type of font    
  
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;   // Sets the way fonts are aligned within cells    
  
range.ColumnWidth=15;   // Set the width of the cell    
  
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();   // Set the background color of the cell    
  
range.Borders.LineStyle=1;   // Set the thickness of the cell border    
  
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());   // To border a cell    
  
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; // Set the top border of the cell to no border    
  
range.EntireColumn.AutoFit();   // Automatically adjust column width    
  
Range.HorizontalAlignment= xlCenter;   //  Horizontal centering of text    
  
Range.VerticalAlignment= xlCenter   // Vertical centering of text    
  
Range.WrapText=true;   // Word wrapping of text    
  
Range.Interior.ColorIndex=39;   // The filling color is lavender    
  
Range.Font.Color=clBlue;   // Font color    
  
xlsApp.DisplayAlerts=false;  // Right Excel Operation of   Do not pop up prompt information  
ApplicationClass xlsApp = new ApplicationClass(); // 1.  Create Excel Object of the application object 1 Instance, which is equivalent to opening it from the Start menu Excel Applications.  
if (xlsApp == null) 
{ 
// Validate this instance, if null Indicates that the machine on which this code is running may not be installed Excel 
} 
2

Merging Cells: Merging cells is actually declaring a range, and the cells in this range will be merged, and the merged contents and styles will be subject to the cells in the upper left corner of this range.


// Settings 1 A range of merged cells, defined from top to bottom, left to right CellRangeAddress Region 
//CellRangeAddress4 The parameters are: start row, end row, start column and end column 
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));

Add formula: Use CellFormula of Cell to set the formula, which is a string, and there is no need to add = before the formula.


range.NumberFormatLocal = "@";   // Format cells to text    
  
range = (Range)worksheet.get_Range("A1", "E1");   // Get Excel Multiple cell ranges: This example is used as Excel Header    
  
range.Merge(0);   // Cell merge action    
  
worksheet.Cells[1, 1] = "Excel Cell assignment ";   //Excel Cell assignment    
  
range.Font.Size = 15;   // Set the font size    
  
range.Font.Underline=true;   // Set whether the font is underlined    
  
range.Font.Name=" Blackbody ";     Set the type of font    
  
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;   // Sets the way fonts are aligned within cells    
  
range.ColumnWidth=15;   // Set the width of the cell    
  
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();   // Set the background color of the cell    
  
range.Borders.LineStyle=1;   // Set the thickness of the cell border    
  
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());   // To border a cell    
  
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; // Set the top border of the cell to no border    
  
range.EntireColumn.AutoFit();   // Automatically adjust column width    
  
Range.HorizontalAlignment= xlCenter;   //  Horizontal centering of text    
  
Range.VerticalAlignment= xlCenter   // Vertical centering of text    
  
Range.WrapText=true;   // Word wrapping of text    
  
Range.Interior.ColorIndex=39;   // The filling color is lavender    
  
Range.Font.Color=clBlue;   // Font color    
  
xlsApp.DisplayAlerts=false;  // Right Excel Operation of   Do not pop up prompt information  
ApplicationClass xlsApp = new ApplicationClass(); // 1.  Create Excel Object of the application object 1 Instance, which is equivalent to opening it from the Start menu Excel Applications.  
if (xlsApp == null) 
{ 
// Validate this instance, if null Indicates that the machine on which this code is running may not be installed Excel 
} 
4

Write the workbook to a file to see the effect:


range.NumberFormatLocal = "@";   // Format cells to text    
  
range = (Range)worksheet.get_Range("A1", "E1");   // Get Excel Multiple cell ranges: This example is used as Excel Header    
  
range.Merge(0);   // Cell merge action    
  
worksheet.Cells[1, 1] = "Excel Cell assignment ";   //Excel Cell assignment    
  
range.Font.Size = 15;   // Set the font size    
  
range.Font.Underline=true;   // Set whether the font is underlined    
  
range.Font.Name=" Blackbody ";     Set the type of font    
  
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;   // Sets the way fonts are aligned within cells    
  
range.ColumnWidth=15;   // Set the width of the cell    
  
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();   // Set the background color of the cell    
  
range.Borders.LineStyle=1;   // Set the thickness of the cell border    
  
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());   // To border a cell    
  
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone; // Set the top border of the cell to no border    
  
range.EntireColumn.AutoFit();   // Automatically adjust column width    
  
Range.HorizontalAlignment= xlCenter;   //  Horizontal centering of text    
  
Range.VerticalAlignment= xlCenter   // Vertical centering of text    
  
Range.WrapText=true;   // Word wrapping of text    
  
Range.Interior.ColorIndex=39;   // The filling color is lavender    
  
Range.Font.Color=clBlue;   // Font color    
  
xlsApp.DisplayAlerts=false;  // Right Excel Operation of   Do not pop up prompt information  
ApplicationClass xlsApp = new ApplicationClass(); // 1.  Create Excel Object of the application object 1 Instance, which is equivalent to opening it from the Start menu Excel Applications.  
if (xlsApp == null) 
{ 
// Validate this instance, if null Indicates that the machine on which this code is running may not be installed Excel 
} 
5

Related articles: