1.ExcelUtils
public class ExcelUtils
{
public static string ExcelContentType
{
get
{
return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="headDict"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static byte[] ExportExcel<T>(List<T> data, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false)
{
DataTable dt = ListToDataTable<T>(data);
byte[] result = null;
List<string> keyList = new List<string>();
if (showSrNo)
{
keyList.Add("RowNum");
dt.Columns.Add("RowNum");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["RowNum"] = i + 1;
}
}
//通过键的集合取
foreach (string key in headDict.Keys)
{
keyList.Add(key);
}
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetName.IsNullOrEmpty() ? "Sheet1" : sheetName);
if (showSrNo)
{
headDict.Add("RowNum", "序号");
}
for (int i = 0; i < keyList.Count; i++)
{
sheet.Cells[1, i + 1].Value = headDict[keyList[i]];
}
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < keyList.Count; j++)
{
sheet.Cells[i + 2, j + 1].Value = dt.Rows[i][keyList[j]].ToString();
}
}
}
ExcelRange cells = sheet.Cells[1, 1, 1 + dt.Rows.Count, keyList.Count];
cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
cells.AutoFitColumns();//自适应列宽
result = package.GetAsByteArray();
}
return result;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">数据</param>
/// <param name="headDict">头部信息</param>
/// <param name="imgColumnList">图片列</param>
/// <param name="sheetName">Sheet名</param>
/// <param name="showSrNo">是否要加上序号</param>
/// <returns></returns>
public static byte[] ExportExcel<T>(List<T> data, Dictionary<string, string> headDict, List<string> imgColumnList, string sheetName = "", bool showSrNo = false)
{
if (imgColumnList == null)
{
imgColumnList = new List<string>();
}
DataTable dt = ListToDataTable<T>(data);
byte[] result = null;
List<string> keyList = new List<string>();
if (showSrNo)
{
keyList.Add("RowNum");
dt.Columns.Add("RowNum");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["RowNum"] = i + 1;
}
}
//通过键的集合取
foreach (string key in headDict.Keys)
{
keyList.Add(key);
}
IWorkbook workbook = new XSSFWorkbook();
//设置宽度
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
style.Alignment = HorizontalAlignment.Center;//水平对齐;
if (showSrNo)
{
headDict.Add("RowNum", "序号");
}
ISheet sheet = sheetName.IsNullOrEmpty() ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < keyList.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(headDict[keyList[i]]);
cell.CellStyle = style;
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < keyList.Count; j++)
{
if (imgColumnList.Contains(keyList[j]))
{
//插入图片
byte[] bytes = HttpMethods.GetImage(dt.Rows[i][keyList[j]].ToString());
if (bytes != null)
{
ICell cell = row1.CreateCell(j);
cell.CellStyle = style;
try
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 100, j, i + 1, j + 1, i + 2);
//##处理照片位置,【图片左上角为(col, row)第row+1行col+1列,右下角为( col +1, row +1)第 col +1+1行row +1+1列,宽为100,高为50
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
catch
{
cell.SetCellValue(dt.Rows[i][keyList[j]].ToString());
}
}
}
else
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][keyList[j]].ToString());
cell.CellStyle = style;
}
}
}
//自适应列宽
for (int i = 0; i < keyList.Count; i++)
{
sheet.AutoSizeColumn(i, true);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
result = ms.GetBuffer();
ms.Close();
};
return result;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="heading">工作簿Worksheet</param>
/// <param name="showSrNo">//是否显示行编号</param>
/// <param name="columnsToTake">要导出的列</param>
/// <returns></returns>
public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.Format("{0}Data", heading));
int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 3; //开始的行
//是否显示行编号
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
//Add Content Into the Excel File
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// autofit width of cells with small content
int columnIndex = 1;
foreach (DataColumn item in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
if (maxLength < 150)
{
workSheet.Column(columnIndex).AutoFit();
}
columnIndex++;
}
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
{
if (i == 0 && showSrNo)
{
continue;
}
if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
{
workSheet.DeleteColumn(i + 1);
}
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
result = package.GetAsByteArray();
}
return result;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="heading"></param>
/// <param name="isShowSlNo"></param>
/// <param name="ColumnsToTake"></param>
/// <returns></returns>
public static byte[] ExportExcel<T>(List<T> data, string heading = "", bool isShowSlNo = false, params string[] ColumnsToTake)
{
return ExportExcel(ListToDataTable<T>(data), heading, isShowSlNo, ColumnsToTake);
}
/// <summary>
/// List转DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <returns></returns>
public static DataTable ListToDataTable<T>(List<T> data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable dataTable = new DataTable();
for (int i = 0; i < properties.Count; i++)
{
PropertyDescriptor property = properties[i];
dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
}
object[] values = new object[properties.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = properties[i].GetValue(item);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
}
2. 导出按钮
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
List<AGVPoint> point = pointLogic.GetAllPoint();
Dictionary<string, string> headDict = new Dictionary<string, string>();
headDict.Add("PointCode", "坐标点");
headDict.Add("Register", "寄存器地址");
byte[] bytes = ExcelUtils.ExportExcel<AGVPoint>(point, headDict, "Sheet1", false);
//保存文件
SaveFileDialog saveFileDialog = new SaveFileDialog();
//设置文件类型
//书写规则例如:txt files(*.txt)|*.txt
saveFileDialog.Filter = "xlsx files(*.xlsx)|*.xlsx";
//设置默认文件名(可以不设置)
saveFileDialog.FileName = "Point" + DateTime.Now.ToString("yyyyMMddHHmmss");
//主设置默认文件extension(可以不设置)
saveFileDialog.DefaultExt = "xlsx";
//获取或设置一个值,该值指示如果用户省略扩展名,文件对话框是否自动在文件名中添加扩展名。(可以不设置)
saveFileDialog.AddExtension = true;
//设置默认文件类型显示顺序(可以不设置)
saveFileDialog.FilterIndex = 2;
//保存对话框是否记忆上次打开的目录
saveFileDialog.RestoreDirectory = true;
// Show save file dialog box
DialogResult result = saveFileDialog.ShowDialog();
//点了保存按钮进入
if (result == DialogResult.OK)
{
System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog.OpenFile();//输出文件
fs.Write(bytes, 0, bytes.Length);
fs.Close();
}
}