public class EPPlus
{
public static string ExcelContentType
{
get
{
return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
}
#region 示例
//public FileContentResult Export()
//{
// var queryJson = Request.Params["queryJson"] + "";
// DataTable dt = new EternalService().GetMenuList(JObject.Parse(queryJson));
// goto ddd;
// ddd:
// byte[] filecontent = ExcelExportHelper.ExportExcel(dt, "", false);
// return File(filecontent, ExcelExportHelper.ExcelContentType, "MyMenu.xlsx");
//}
#endregion
/// <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)
{
//建立Excel
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.Format("{0}Data", heading));
//workSheet.Workbook.Properties.Title = "inventory";//设置excel的标题
workSheet.Workbook.Properties.Author = "凉生";//作者
//workSheet.Workbook.Properties.Comments = "this is a test";//备注
workSheet.Workbook.Properties.Company = "xin";//公司
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++;
}
}
//在Excel文件中添加内容
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// 小单元格的内容自动调整宽度
int columnIndex = 1;
foreach (DataColumn item in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row + 1, columnIndex, workSheet.Dimension.End.Row, columnIndex];
string maxValue = columnCells.Max(cell => (cell.Value ?? "").ToString());
int maxLength = maxValue.Count();
if (maxLength < 150) workSheet.Column(columnIndex).AutoFit();//自动调整宽度
else workSheet.Column(columnIndex).Style.WrapText = true; //自动换行
if (Regex.IsMatch(maxValue, @"^[+-]?\d*[.]?\d*$"))//验证是否为数字字符串
{
workSheet.Column(columnIndex).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
}
if (item.DataType.Equals(Type.GetType("System.DateTime")))//验证是否是时间
{
workSheet.Column(columnIndex).AutoFit();
workSheet.Column(columnIndex).Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
}
columnIndex++;
}
// 格式标题-粗体，黄色的黑色
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 = ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// 格式化单元格-添加边框
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);
}
// 删除忽略的列
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;
}
#region 示例
// public ActionResult UploadExcle(HttpPostedFileBase file)
//{
// if (file == null) return Content("没有文件！", "text/plain");
// var sourcePath = Path.Combine(Server.MapPath("~/tempFiles/"), Path.GetFileName(file.FileName));
// //建立临时传输文件夹
// if (!Directory.Exists(Path.GetDirectoryName(sourcePath)))
// Directory.CreateDirectory(sourcePath);
// file.SaveAs(sourcePath);
// DataSet dt = ExcelToTable.ReadExcelToDataSet(sourcePath);
// //处理数据
// //。。。。。。
// //删除文件
// System.IO.File.Delete(sourcePath);
// return Content("");
//}
#endregion
/// <summary>
/// 导入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataSet ReadExcelToDataSet(string filePath)
{
DataSet ds = new DataSet("ds");
DataRow dr;
object objCellValue;
string cellValue;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
using (ExcelPackage package = new ExcelPackage())
{
package.Load(fs);
foreach (var sheet in package.Workbook.Worksheets)
{
if (sheet.Dimension == null) continue;
var columnCount = sheet.Dimension.End.Column;//获取worksheet的列数
var rowCount = sheet.Dimension.End.Row; //获取worksheet的行数
if (rowCount > 0)
{
DataTable dt = new DataTable(sheet.Name);
for (int j = 0; j < columnCount; j++)//设置DataTable列名
{
objCellValue = sheet.Cells[1, j + 1].Value;
cellValue = objCellValue == null ? "" : objCellValue.ToString();
dt.Columns.Add(cellValue, typeof(string));
}
for (int i = 2; i <= rowCount; i++)
{
dr = dt.NewRow();
for (int j = 1; j <= columnCount; j++)
{
objCellValue = sheet.Cells[i, j].Value;
cellValue = objCellValue == null ? "" : objCellValue.ToString();
dr[j - 1] = cellValue;
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
}
}
return ds;
}
}
