Export the Code First
/// <summary>
/// Export 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();
/ / Set the object is 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 data bit table name of the column name
worksheetdata.Cells[1, i + 1] = keys[i+1];
}
//Because the first line has already written the header, all data should start from a2
Rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount is the actual number of rows, the largest row
Int irowcount = values.Count;
Int iparstedrow = 0, icurrsize = 0;
//ieachsize is the value of each line, you can set it yourself.
Int ieachsize = 10000;
//icolumnaccount is the actual number of columns, the maximum number of columns
Int icolumnaccount = keys.Count-1;
/ / In the memory to declare an array of ieachsize × icolumnaccount, ieachsize is the maximum number of rows stored each 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 the 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 ? 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 the value2 attribute of range, the value in memory is assigned to 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 the 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 = "@"; //Set the cell format to text
Range = (Range)worksheet.get_Range("A1", "E1"); //Get Excel multiple cell ranges: This example is used as an 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 the font to be underlined
range.Font.Name="black body"; set the type of font
range.HorizontalAlignment=XlHAlign.xlHAlignCenter; //Set the font in the cell for its way
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());
/ / Add a border to the cell
range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;
/ / Set the cell top border to no border
range.EntireColumn.AutoFit(); //Automatically adjust the column width
Range.HorizontalAlignment= xlCenter; // text horizontally centered
Range.VerticalAlignment= xlCenter //Text vertical centering mode
Range.WrapText=true; //Text wrap
Range.Interior.ColorIndex=39; //fill color is lavender
Range.Font.Color=clBlue; //font color
xlsApp.DisplayAlerts=false; //Action on Excel does not pop up a message
ApplicationClass 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 it is null, it means that the machine running this code may not have Excel installed.
}
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; //First sheet page mySheet.Name = "testsheet"; //Modify the sheet name here
2. Copy the sheet page
mySheet.Copy(Type.Missing, workbook.Sheets[1]); / / Copy mySheet into a new sheet page, after copying the name is mySheet page name followed by a (2), here is testsheet (2), after copying, the number of Worksheet is increased by one
Note the two parameters of the copy method here, that is, whether to copy the new sheet page before or after the specified sheet page, the above example refers to the copy of the sheet page after the first sheet page.
3. Delete Sheet page
xlsApp.DisplayAlerts = false; //If you want to delete a sheet page, first set this to fasle. (xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();
4. Select the sheet page
Copy the Code code as follows:
(Xlsapp.activeworkbook.sheets[1] as Worksheet). Select (Type.Missing); Select a sheet page
5. Save Excel File
Workbook. Saved = true; Workbook. SaveCopyAs (filepath);
6. Releasing 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];//Get sheet1
Long rows = table.Rows.Count;
/* Two lines of code commented below. When the number of data lines exceeds the line, an exception occurs: the exception is from HRESULT: 0x800A03EC. Because: Excel 2003 only supports the largest row data per sheet
//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, the 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 divisible by pageRows, the possible number of pages may not be allowed after rounding
{
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 a sheet
}
Else
{
Worksheet = (Worksheet)workbook.Worksheets[sc];//Get 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 for gray
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();//Add single quotes before obj.ToString() to prevent automatic conversion format
}
}
}
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 adaptive.
Range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, table.Columns.Count]);
//15 stands for gray
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 for gray
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();//Add single quotes before obj.ToString() to prevent automatic conversion format
}
}
//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 adaptive.
Range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, table.Columns.Count]);
//15 stands for gray
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();//Forcibly destroy
}
}
}
Method 3:
Go to the official website: http://www.php.cn/The download requires the introduction of a DLL (you can select the. net2.0 or. net4.0 dll), and then add references to the Web site.
Export code:
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");
// first row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("first column first row");
// The second column
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("first column first row");
// ...
// write to the 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:
Start by creating a blank workbook to use as a test, create a blank worksheet in it, create a blank row in the table, create a cell in the row, and fill in the contents:
/ / Create a blank workbook
IWorkbook workbook = new HSSFWorkbook();
/ / In the workbook: create a blank worksheet
ISheet sheet = workbook.CreateSheet();
/ / In the work table: create a line, the parameter is the line number, from 0
IRow row = sheet.CreateRow(0);
/ / In the line: create a cell, the parameter is the column number, from 0
ICell cell = row.CreateCell(0);
/ / Set the cell content
cell.SetCellValue("internship identification table");
Set cell styles: Be careful when setting cell styles, be sure to create a new style object to set up, or set the style of all the cells in the worksheet together, and they should share a style object:
ICellStyle style = workbook.CreateCellStyle(); / / Set the style of the cell: horizontal alignment centered style.Alignment = HorizontalAlignment.CENTER; / / Create a new font style object IFont font = workbook.CreateFont(); / / Set the font bold style font.Boldweight = short.MaxValue; / / Use the SetFont method to add font styles to the cell style style.SetFont(font); / / Assign a new style to the cell cell.CellStyle = style;
Set Cell width Height:
Set the height of the cell is actually set its row high, so to set the row height on the cell row, the row height setting value seems to be 1/20 pixels, so *20 to achieve the setting effect;
The width of the cell is actually set to its column width, so to set it on the cell's column (the column's setting on the worksheet), the width value appears to be 1/256 of the character, so *256 to achieve the set effect.
Sets the height of the cell row. Height = 30 * 20;//Sets the width of the cell sheet. Setcolumnwidth (0, 30 * 256);
Merge Cells: The merged cell is actually declaring an area in which the cells are merged, and the merged content and style are based on the cell in the upper-left corner of the region.
/ / Set a merged cell area, use the up and down left and right to define the CellRangeAddress area //CellRangeAddress four parameters are: start line, end line, start column, end column sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
Add formula: use the cell's Cellformula to set the formula, which is a string that does not need to be added before the formula.
/ / Write the formula to the cell through Cell's CellFormula //Note: You can write the formula directly, you don't need to add '=' at the front. ICell cell2 = sheet.CreateRow(1).CreateCell(0); cell2.CellFormula = "HYPERLINK(\"test image.jpg\",\"test image.jpg\")";
To write a workbook to a file to see the effect:
/ / Write the workbook to the file
Using (FileStream fs = new FileStream("Build Effects.xls", FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
The above is C # to achieve hundreds of thousands of levels of data export Excel and Excel various operating instance code in detail, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
