C realizes hundreds of thousands of levels of data export Excel and Excel various operation examples
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 Name2. 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 ANote 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;
}
#endregionUsage:
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