public class ExcelHelp
{<!-- -->
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">Data list</param>
/// <param name="file">File address</param>
/// <param name="sheetnum">sheet number</param>
/// <param name="titles">Header name list</param>
/// <param name="startNumber">Start line number</param>
/// <exception cref="Exception"></exception>
public void ListToExcel<T>(List<T> list, string file, string sevefile, int sheetnum, List<string> titles, int startNumber)
{<!-- -->
DataTable dt = new DataTable();
dt = ListToDataTable<T>(list, titles);
// Export(dt, file);
string endText = "Tabulation: Review: Quality countersign: Date:";
ExportToExcel(dt, 4, "SMT material station table placement station 1", endText, file);
List<string> strings = new List<string>() {<!-- --> "q", "w", "e", "R" };
OtherToExcel(file, strings, 1);
}
/// <summary>
/// Write array, write line number
/// </summary>
/// <param name="data"></param>
/// <param name="lineNumber"></param>
public void OtherToExcel(string file, List<string> data, int lineNumber)
{<!-- -->
HSSFWorkbook workbook;
ISheet sheet;
using (FileStream stream = System.IO.File.OpenRead(file))
{<!-- -->
workbook = new HSSFWorkbook(stream);
sheet = workbook.GetSheetAt(0);
}
//Format
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");//Date format
var headerRow = sheet.CreateRow(lineNumber);
//CellStyle
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//center left and right
headStyle.VerticalAlignment = VerticalAlignment.Center;// Centered up and down
//Define font
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (string column in data)
{<!-- -->
headerRow.CreateCell(data.IndexOf(column)).SetCellValue(column);
headerRow.GetCell(data.IndexOf(column)).CellStyle = headStyle;
// sheet.SetColumnWidth(data.IndexOf(column), (arrColWidth[column.Ordinal] + 1) * 256);//Set column width
}
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{<!-- -->
workbook.Write(fs);
}
}
/// <summary>
/// Get Sheet table data
/// </summary>
/// <param name="Sheet"></param>
public void GetSheetData(ISheet Sheet)
{<!-- -->
IRow row;
// 1. Get the number of rows
var rowCount = Sheet.LastRowNum;
//Start getting data from the fourth row (subscript 3), the first three rows are the headers
// If you start from the first line, then i=0 is fine
for (int i = 5; i <= rowCount; i + + )
{<!-- -->
//var dataTable = new DataTable_Model();
// Get specific rows
row = Sheet.GetRow(i);
if (row != null)
{<!-- -->
// 2. Get the number of columns corresponding to the row
var column = row.LastCellNum;
for (int j = 0; j < column; j + + )
{<!-- -->
// 3. Get the cell data corresponding to a certain row and column
var cellValue = row.GetCell(j).ToString();
// 4. Output cell data
Console.Write(cellValue + " ");
}
// newline
Console.WriteLine();
}
}
}
/// <summary>
/// Convert the generic list List to DataTable
/// </summary>
/// <typeparam name="T">Generic entity</typeparam>
/// <param name="list">List to be converted</param>
/// <param name="titles">Titles</param>
/// <returns></returns>
public DataTable ListToDataTable<T>(List<T> list, List<string> titles)
{<!-- -->
DataTable dt = new DataTable();
Type listType = typeof(T);
PropertyInfo[] properties = listType.GetProperties();
//Title row
if (titles != null & amp; & amp; properties.Length == titles.Count())
{<!-- -->
for (int i = 0; i < properties.Length; i + + )
{<!-- -->
PropertyInfo property = properties[i];
dt.Columns.Add(new DataColumn(titles[i], property.PropertyType));
}
}
else
{<!-- -->
for (int i = 0; i < properties.Length; i + + )
{<!-- -->
PropertyInfo property = properties[i];
dt.Columns.Add(new DataColumn(property.Name, property.PropertyType));
}
}
//Content row
foreach (T item in list)
{<!-- -->
DataRow dr = dt.NewRow();
for (int i = 0; i < dt.Columns.Count; i + + )
{<!-- -->
dr[i] = properties[i].GetValue(item, null);
}
dt.Rows.Add(dr);
}
return dt;
}
/// <summary>
/// Generate table
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="startLineNumber">From which line to start, the number needs to be -1 </param>
/// <param name="HeaderText">Header text</param>
/// <param name="EndText">Bottom text</param>
/// <param name="fileName">File name</param>
public void ExportToExcel(DataTable dt, int startLineNumber, string HeaderText, string EndText, string fileName)
{<!-- -->
var workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet1" : dt.TableName);
//Right-click the file "Properties" information
#region file attribute information
{<!-- -->
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "File author information";
si.ApplicationName = "Create program information";
si.LastAuthor = "Last saver information";
si.Comments = "Author information";
si.Title = "Title information";
si.Subject = "Subject information";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
//Format
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");//Date format
//Get column width
var arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{<!-- -->
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (var i = 0; i < dt.Rows.Count; i + + )
{<!-- -->
for (var j = 0; j < dt.Columns.Count; j + + )
{<!-- -->
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{<!-- -->
arrColWidth[j] = intTemp + 10;
}
}
}
int rowIndex = 0 + startLineNumber;
foreach (DataRow row in dt.Rows)
{<!-- -->
#region table header column header
if (rowIndex == 65535 || rowIndex == startLineNumber)
{<!-- -->
if (rowIndex != startLineNumber)
{<!-- -->
sheet = workbook.CreateSheet();//If it exceeds 65535 lines, create a new Sheet
}
#region Header and style
{<!-- -->
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(HeaderText);
//CellStyle
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//center left and right
headStyle.VerticalAlignment = VerticalAlignment.Center;// Centered up and down
//Set the background color of the cell (the cell style will override the column or row style)
headStyle.FillForegroundColor = (short)11;
//Define font
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));//Merge area
}
#endregion
#region Column headers and styles
{<!-- -->
var headerRow = sheet.CreateRow(rowIndex);
//CellStyle
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//center left and right
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;// Centered up and down
//Define font
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{<!-- -->
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);//Set column width
}
}
#endregion
rowIndex + + ;//Data row RowIndex is 2 (table header and column header occupy one row)
}
#endregion
#region content
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{<!-- -->
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{<!-- -->
case "System.String"://String type
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://date type
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//Formatted display
break;
case "System.Boolean"://Boolean type
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://integer
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://Floating point type
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://null value processing
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");//Set cell formula: newCell.SetCellFormula("SUM($B0:$D0)")
break;
}
}
#endregion
rowIndex + + ;
}
#region Table footer and style
if (!string.IsNullOrEmpty(EndText))
{<!-- -->
{<!-- -->
var headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(EndText);
//CellStyle
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//center left and right
headStyle.VerticalAlignment = VerticalAlignment.Center;// Centered up and down
//Set the background color of the cell (the cell style will override the column or row style)
headStyle.FillForegroundColor = (short)11;
//Define font
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, dt.Columns.Count - 1));//Merge area
}
}
#endregion
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{<!-- -->
workbook.Write(fs);
}
}
}