帮助类
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web; namespace EpPlus.Demo.Models
{
public class EpPlusHelper
{
ExcelPackage package;
ExcelWorksheet worksheet; /// <summary>
/// 构造函数
/// </summary>
/// <param name="sheetName"></param>
/// <param name="path"></param>
public EpPlusHelper(string sheetName = "", string path = "")
{
try
{
if (!string.IsNullOrEmpty(path))
{
package = new ExcelPackage(new FileInfo(path));
}
else
{
package = new ExcelPackage();
} if (package.Workbook.Worksheets.Count > 0)
{
worksheet = package.Workbook.Worksheets[0];
}
else
{
CreateSheet(DateTime.Now.ToString("yyyyMMdd"));
} if (!string.IsNullOrWhiteSpace(sheetName))
{
worksheet.Name = sheetName;
}
}
catch (Exception ex)
{
throw ex;
}
} /// <summary>
/// 创建工作薄
/// </summary>
/// <param name="sheetName"></param>
public void CreateSheet(string sheetName)
{
try
{
worksheet = package.Workbook.Worksheets.Add(sheetName);//创建worksheet
}
catch (Exception ex)
{
throw ex;
}
} /// <summary>
/// 切换工作薄
/// </summary>
/// <param name="index"></param>
public void ChangeSheet(int index)
{
try
{
worksheet = package.Workbook.Worksheets[index];
}
catch (Exception ex)
{
throw ex;
}
} /// <summary>
/// 切换工作簿
/// </summary>
/// <param name="sheetName"></param>
public void ChangeSheet(string sheetName)
{
try
{
worksheet = package.Workbook.Worksheets[sheetName];
}
catch (Exception ex)
{
throw ex; }
} /// <summary>
/// 保存excel
/// </summary>
/// <param name="password"></param>
public void SaveExcel(HttpResponseBase response, string excelName)
{
try
{
if (package != null)
{
if (!string.IsNullOrEmpty(excelName))
{
//package.Save();
//package.SaveAs();
response.BinaryWrite(package.GetAsByteArray());
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx");
}
else
{
response.BinaryWrite(package.GetAsByteArray());
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("content-disposition", "attachment; filename=" + (DateTime.Now.ToString("yyyyMMddHHmmss")) + ".xlsx");
}
}
}
catch (Exception ex)
{
throw ex;
}
} /// <summary>
/// 通过索引赋值，索引从1开始
/// </summary>
/// <param name="x">行</param>
/// <param name="y">列</param>
/// <param name="value"></param>
public void SetValue(int x, int y, string value)
{
worksheet.Cells[x, y].Value = value;//直接指定行列数进行赋值
} /// <summary>
/// 单元格赋值
/// </summary>
/// <param name="cell">单元格，如：A1</param>
/// <param name="value"></param>
public void SetValue(string cell, string value)
{
worksheet.Cells[cell].Value = value;//直接指定单元格进行赋值
} /// <summary>
/// 设置样式
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="isWrapText">是否换行</param>
/// <param name="horizontal">水平格式</param>
/// <param name="vertical">垂直格式</param>
/// <param name="isBold">是否粗体</param>
/// <param name="size">文字大小</param>
/// <param name="height">行高</param>
/// <param name="isShowGridLines">是否显示网格线</param>
public void SetStyle(int x, int y, bool isWrapText = true, ExcelHorizontalAlignment horizontal = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment vertical = ExcelVerticalAlignment.Center, bool isBold = false, int size = 12, int height = 15, bool isShowGridLines = false)
{
//worksheet.Cells[x, y].Style.Numberformat.Format = "#,##0.00";//这是保留两位小数 worksheet.Cells[x, y].Style.HorizontalAlignment = horizontal;//水平居中
worksheet.Cells[x, y].Style.VerticalAlignment = vertical;//垂直居中
//worksheet.Cells[1, 4, 1, 5].Merge = true;//合并单元格
worksheet.Cells.Style.WrapText = isWrapText;//自动换行 worksheet.Cells[x, y].Style.Font.Bold = isBold;//字体为粗体
//worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White);//字体颜色
//worksheet.Cells[1, 1].Style.Font.Name = "微软雅黑";//字体
worksheet.Cells[x, y].Style.Font.Size = size;//字体大小 //worksheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
//worksheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色 worksheet.Cells[x, y].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//设置单元格所有边框
worksheet.Cells[x, y].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//单独设置单元格底部边框样式和颜色（上下左右均可分开设置）
worksheet.Cells[x, y].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191)); //worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
worksheet.Row(x).Height = height;//设置行高
//worksheet.Row(1).CustomHeight = true;//自动调整行高
//worksheet.Column(1).Width = 15;//设置列宽 worksheet.View.ShowGridLines = isShowGridLines;//去掉sheet的网格线
//worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
//worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray);//设置背景色
//worksheet.BackgroundImage.Image = Image.FromFile(@"firstbg.jpg");//设置背景图片
} public void SetMergeCell(int x1, int y1, int x2, int y2)
{
worksheet.Cells[x1, y1, x2, y2].Merge = true;//合并单元格
} public void TableToExcel(DataTable dt, string title)
{
DataColumnCollection columns = dt.Columns; int addIndex = 1; //表格标题
if (!string.IsNullOrEmpty(title))
{
addIndex = 2;
SetMergeCell(1, 1, 1, columns.Count);
SetStyle(1, 1, false, ExcelHorizontalAlignment.Center, ExcelVerticalAlignment.Center, true, 16, 25);
//worksheet.Cells.Merge(1, 1, 1, columns.Count);
SetValue(1, 1, title);
} //表头
if (columns.Count > 0)
{
int columnIndex = 1; foreach (DataColumn dc in columns)
{
SetStyle(addIndex, columnIndex);
SetValue(addIndex, columnIndex, dc.ColumnName);
columnIndex += 1;
}
} //数据
if (dt.Rows.Count > 0)
{
int rowIndex = 1 + addIndex; foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < columns.Count; i++)
{
SetStyle(rowIndex, i + 1);
SetValue(rowIndex, i + 1, dr[i].ToString());
} rowIndex += 1;
}
}
}
}
}
demo
public void ExcelDemo02()
{
EpPlusHelper ep = new EpPlusHelper(Server.MapPath("/ExcelModel/model.xlsx")); ep.SetValue(3, 2, "龙富工业区");
ep.SetValue(3, 4, "2018.6.1 0:00-2018.6.30 24:00"); DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("Address", typeof(string));
dt.Columns.Add("平台", typeof(string));
dt.Columns.Add("DeviceType", typeof(string));
dt.Columns.Add("DeviceStatus", typeof(string));
dt.Columns.Add("Count", typeof(string));
dt.Columns.Add("Remark", typeof(string)); for (int i = 1; i < 10; i++)
{
DataRow dr = dt.NewRow(); dr["ID"] = i.ToString();
dr["Address"] = "地址" + i.ToString();
dr["平台"] = "平台" + i.ToString();
dr["DeviceType"] = "类型" + i.ToString();
dr["DeviceStatus"] = "状况" + i.ToString();
dr["Count"] = i.ToString();
dr["Remark"] = "备注" + i.ToString(); dt.Rows.Add(dr);
} int index = 6; foreach (DataRow dr in dt.Rows)
{
ep.SetValue(index, 1, Convert.ToString(dr["ID"]));
ep.SetStyle(index, 1); ep.SetValue(index, 2, Convert.ToString(dr["Address"]));
ep.SetStyle(index, 2); ep.SetValue(index, 3, Convert.ToString(dr["平台"]));
ep.SetStyle(index, 3); ep.SetValue(index, 4, Convert.ToString(dr["DeviceType"]));
ep.SetStyle(index, 4); ep.SetValue(index, 5, Convert.ToString(dr["DeviceStatus"]));
ep.SetStyle(index, 5); ep.SetValue(index, 6, Convert.ToString(dr["Count"]));
ep.SetStyle(index, 6); ep.SetValue(index, 7, Convert.ToString(dr["Remark"]));
ep.SetStyle(index, 7); index++;
} ep.SaveExcel(Response,"自定义名称");
}