Export the code first
/// <summary>
/// Fastest export speed /// </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;
appexcel = new ();
miss = ;
appexcel = new ();
workbookdata = null;
worksheetdata = null;
rangedata;
workbookdata = ();
//Set the object is not visible = false;
= false;
try
{
foreach (var lv in list)
{
var keys = as List<string>;
var values = as List<IList<object>>;
worksheetdata = ()(miss, );
for (int i = 0; i < -1; i++)
{
//Give the worksheet a name = keys[0];//The first data bit table name of the column name [1, i + 1] = keys[i+1];
}
//Because the first row has already written the table header, all data should start from a2 rangedata = worksheetdata.get_Range("a2", miss);
xlrang = null;
//irowcount is the actual number of rows, the maximum row int irowcount = ;
int iparstedrow = 0, icurrsize = 0;
//ieachsize is the value of each line written, you can set it yourself int ieachsize = 10000;
//icolumnaccount is the actual number of columns, the maximum number of columns int icolumnaccount = -1;
//Declare an array of ieachsize×icolumnaccount in memory. ieachsize is the maximum number of rows stored at each time, and iicolumnaccount 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 the array 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 ? () : "";
}
}
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);
= "@";
// Call range's value2 property to assign the value in memory to excel xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
}
(()["Sheet1"]).Delete();
(()["Sheet2"]).Delete();
(()["Sheet3"]).Delete();
//Save the worksheet (filepath, miss, miss, miss, miss, miss, , miss, miss, miss);
(false, miss, miss);
();
();
(workbookdata);
();
(appexcel);
();
}
catch (Exception ex)
{
ErrorMsg = ;
bSuccess = false;
}
finally
{
if (appexcel != null)
{
(appexcel);
}
}
return bSuccess;
}
= "@"; //Set the cell format to text
range = (Range)worksheet.get_Range("A1", "E1"); //Get multiple cell areas of Excel: This example is used as the Excel table header
(0); //Cell merge action
[1, 1] = "Excel cell assignment"; //Excel cell assignment
= 15; //Set font size
=true; //Set whether the font has underline
="Bold"; Set the type of font
=; //Set the way the font is used in the cell
=15; //Set the width of the cell
=(255,204,153).ToArgb(); //Set the background color of the cell
=1; //Set the thickness of the cell border
(,,,()); //Add a border to the cell
.get_Item().LineStyle = ; //Set the border on the cell to have no border
(); //Automatically adjust column width
= xlCenter; // Text horizontal centering method
= xlCenter //The vertical centering method of text
=true; // Text wrapping automatically
=39; //Fill color is lilac
=clBlue; //Font color
=false; //No prompt message appears when operating ExcelApplicationClass xlsApp = new ApplicationClass(); // 1. Create an instance of the Excel application object, which is equivalent to opening the Excel application from the Start menu.if (xlsApp == null)
{
//Verify this instance. If null, it means that the machine running this code may not have Excel installed}
1. Open the existing Excel file
Workbook workbook = (excelFilePath, , , , , , , , , , , , , , ); Worksheet mySheet = [1] as Worksheet; //The first sheet page = "testsheet"; //Modified heresheetname
2. Copy the sheet page
(, [1]); //Copy mySheet into a new sheet page. The name after copying is to add one (2) after the name of mySheet page. Here is the testsheet (2). After copying, the number of Worksheets will be increased by one
Note: The two parameters of the Copy method here refer to whether the copying of the new sheet page is before or after the specified sheet page. The above example refers to the copied sheet page behind the first sheet page.
3. Delete the sheet page
= false; //If you want to delete a sheet, you must first set this item to fasle.([1] as Worksheet).Delete();
4. Select the sheet page
Copy the codeThe code is as follows:
([1] as Worksheet).Select(); //Select a certain sheet page
5. Save excel file
= true; (filepath);
6. Free excel resources
(true, , ); workbook = null; (); xlsApp = null;
Method 2:
using System;
using ;
using ;
using ;
using ;
using ;
namespace ExcelTest
{
public class ExcelUtil
{
table11 = new ();
public void ExportToExcel( table, string saveFileName)
{
bool fileSaved = false;
//ExcelApp xlApp = new ExcelApp();
Application xlApp = new Application();
if (xlApp == null)
{
return;
}
Workbooks workbooks = ;
Workbook workbook = ();
Worksheet worksheet = (Worksheet)[1];//Get sheet1
long rows = ;
/*The two lines of code commented below appear when the number of data lines exceeds the line: The exception comes from HRESULT:0x800A03EC. Because: Excel 2003 only supports maximum row data in each sheet
//Range fchR = worksheet.get_Range([1, 1], [+2, +1]);
//fchR.Value2 = datas;*/
if (rows > 65535)
{
long pageRows = 60000;//Define the number of rows displayed on each page, the number of rows must be less than
int scount = (int)(rows / pageRows);
if (scount * pageRows < )//When the total number of rows is not divided by pageRows, the number of pages may be inaccurate after rounding {
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = ;
worksheet = ()(
missing, missing, missing, missing);//Add a sheet
}
else
{
worksheet = (Worksheet)[sc];//Get sheet1 }
string[,] datas = new string[pageRows + 1, + 1];
for (int i = 0; i < ; i++) //Write fields {
datas[0, i] = [i].Caption;
}
Range range = worksheet.get_Range([1, 1], [1, ]);
= 15;//15 represents gray = true;
= 9;
int init = (((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= )
{
result = ;
}
else
{
result = ((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < ; i++)
{
if ([i].DataType == typeof(string) || [i].DataType == typeof(Decimal) || [i].DataType == typeof(DateTime))
{
object obj = [r][[i].ColumnName];
datas[index, i] = obj == null ? "" : "'" + ().Trim();//Insert single quotes before() to prevent automatic conversion of formats
}
}
}
Range fchR = worksheet.get_Range([1, 1], [index + 2, + 1]);
fchR.Value2 = datas;
();//Column width adaptation.
range = worksheet.get_Range([1, 1], [index + 1, ]);
//15 represents gray
= 9;
= 14.25;
= 1;
= 1;
}
}
else
{
string[,] datas = new string[ + 2, + 1];
for (int i = 0; i < ; i++) //Write fields {
datas[0, i] = [i].Caption;
}
Range range = worksheet.get_Range([1, 1], [1, ]);
= 15;//15 represents gray = true;
= 9;
int r = 0;
for (r = 0; r < ; r++)
{
for (int i = 0; i < ; i++)
{
if ([i].DataType == typeof(string) || [i].DataType == typeof(Decimal) || [i].DataType == typeof(DateTime))
{
object obj = [r][[i].ColumnName];
datas[r + 1, i] = obj == null ? "" : "'" + ().Trim();//Insert single quotes before() to prevent automatic conversion of formats
}
}
//();
}
Range fchR = worksheet.get_Range([1, 1], [ + 2, + 1]);
fchR.Value2 = datas;
();//Column width adaptation.
range = worksheet.get_Range([1, 1], [ + 1, ]);
//15 represents gray
= 9;
= 14.25;
= 1;
= 1;
}
if (saveFileName != "")
{
try
{
= true;
(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
}
}
else
{
fileSaved = false;
}
();
();//Forcibly destroyed
}
}
}
Method 3:
Go to the official website first:/Downloading requires introducing dlls (you can choose .net2.0 or .net4.0 dlls), and then adding references to the website.
Export code:
book = new ();
sheet = ("test_01");
// Column 1 row = (0);
(0).SetCellValue("First Column, First Row");
// Second column row2 = (1);
(0).SetCellValue("Second Column First Row");
// ...
// Write to the client ms = new ();
(ms);
("Content-Disposition", ("attachment; filename={0}.xls", ("yyyyMMddHHmmssfff")));
(());
book = null;
();
();
Import code:
HSSFWorkbook hssfworkbook;
#region
public DataTable ImportExcelFile(string filePath)
{
#region//Initialization information try
{
using (FileStream file = new FileStream(filePath, , ))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
sheet = (0);
rows = ();
DataTable dt = new DataTable();
for (int j = 0; j < ((0).LastCellNum); j++)
{
((((int)'A') + j).ToString());
}
while (())
{
HSSFRow row = (HSSFRow);
DataRow dr = ();
for (int i = 0; i < ; i++)
{
cell = (i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = ();
}
}
(dr);
}
return dt;
}
#endregion
usage:
First, create a blank workbook to use 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 content:
//Create a blank workbookIWorkbook workbook = new HSSFWorkbook();
//In the workbook: Create a blank worksheetISheet sheet = ();
//In the worksheet: Create row, the parameter is the row number, count from 0IRow row = (0);
//In the row: Create a cell, the parameter is column number, count from 0ICell cell = (0);
//Set cell content("Internship Appraisal Form");
Set cell style: When setting cell styles, be sure to create a new style object to set, otherwise the styles of all cells in the worksheet will be set together. They should share a style object:
ICellStyle style = (); //Set the cell style: horizontal alignment center = ; // Create a new font style objectIFont font = (); //Set font bold style = ; //Use SetFont method to add font style to cell style(font); // Assign new style to cells = style;
Set cell width and height:
Setting the height of a cell is actually to set the height of the row where it is located, so you need to set the height of the row on the row where the cell is located. The value of the row height setting seems to be 1/20 of the pixel point, so *20 to achieve the setting effect;
Setting the width of a cell is actually to set the width of the column where it is located, so you need to set it on the column where the cell is located (the column is set on the worksheet). The width value seems to be 1/256 of the character, so *256 to achieve the setting effect.
//Set the height of the cell = 30 * 20; //Set the width of the cell(0, 30 * 256);
Merge cells:Merged cells are actually declaring a region, and the cells in that region will be merged. The combined content and style shall prevail in the upper left corner of the region.
//Set a merge cell area and define the CellRangeAddress area using up, down, left and right//The four parameters of CellRangeAddress are: start row, end row, start column, end column, end column(new CellRangeAddress(0, 0, 0, 10));
Add formula:Use Cell's CellFormula to set the formula, which is a string, and there is no need to add an = sign before the formula.
//Write formula into cell via CellFormula//Note: Just write the formula content directly, there is no need to add '=' in the first placeICell cell2 = (1).CreateCell(0); = "HYPERLINK(\"Test Picture.jpg\",\"Test Picture.jpg\")";
Write the workbook to the file to view the effect:
//Write the workbook to the fileusing (FileStream fs = new FileStream("Generate effect.xls", , ))
{
(fs);
}
The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.