EPPlus samples for .Net 5.x
sample 1.采用Epplus处理Excel,实现简单的二级联动。
程序中用到的连个类:省份、城市
public class Province
{
public int ProvinceId { get; set; }
public string ProvinceName { get; set; }
public List<Province> GetList()
{
return new List<Province>() {
new Province() {ProvinceId=1,ProvinceName="北京市" },
new Province() {ProvinceId=2,ProvinceName="河北省" },
new Province() {ProvinceId=3,ProvinceName="河南省" }
};
}
}
public class City
{
public int CityId { get; set; }
public int ProvinceId { get; set; }
public string CityName { get; set; }
public List<City> GetList()
{
return new List<City>() {
new City() {CityId=1,ProvinceId=1,CityName="东城区" },
new City() {CityId=2,ProvinceId=1,CityName="西城区" },
new City() {CityId=3,ProvinceId=1,CityName="朝阳区" },
new City() {CityId=4,ProvinceId=1,CityName="海淀区" },
new City() {CityId=5,ProvinceId=1,CityName="丰台区" },
new City() {CityId=6,ProvinceId=1,CityName="昌平区" },
new City() {CityId=7,ProvinceId=1,CityName="房山区" },
new City() {CityId=8,ProvinceId=1,CityName="大兴区" },
new City() {CityId=9,ProvinceId=1,CityName="通州区" },
new City() {CityId=10,ProvinceId=1,CityName="石景山区" },
new City() {CityId=11,ProvinceId=2,CityName="石家庄" },
new City() {CityId=12,ProvinceId=2,CityName="张家口" },
new City() {CityId=13,ProvinceId=2,CityName="承德" },
new City() {CityId=14,ProvinceId=2,CityName="秦皇岛" },
new City() {CityId=15,ProvinceId=2,CityName="沧州" },
new City() {CityId=16,ProvinceId=2,CityName="衡水" },
new City() {CityId=17,ProvinceId=2,CityName="邯郸" },
new City() {CityId=18,ProvinceId=3,CityName="郑州" },
new City() {CityId=19,ProvinceId=3,CityName="商丘" },
new City() {CityId=20,ProvinceId=3,CityName="驻马店" },
new City() {CityId=21,ProvinceId=3,CityName="新乡" },
new City() {CityId=22,ProvinceId=3,CityName="焦作" }
};
}
}
自动在网站根路径下创建Output.xlsx代码
FileInfo output = new FileInfo(Server.MapPath("~/Output.xlsx"));
if (output.Exists)
{
output.Delete();
output = new FileInfo(Server.MapPath("~/Output.xlsx"));
}
using (var package = new ExcelPackage(output))
{
// 添加工作表 为下拉框提供数据源
var sheet = package.Workbook.Worksheets.Add("SourceSheet");
List<Province> ls_p = new Province().GetList();
List<City> ls_c = new City().GetList();
for (int i = 0; i < ls_p.Count; i++)
{
var item = ls_p[i];
sheet.Cells[1, i + 1].Value = item.ProvinceName;
sheet.Cells[1, i + 1].Style.Font.Bold = true;
List<City> ls_temp_c = (from p in ls_c
where p.ProvinceId == item.ProvinceId
select p).ToList();
int tempCityCount = ls_temp_c.Count;
for (int j = 0; j < tempCityCount; j++)
{
var tempCoty = ls_temp_c[j];
sheet.Cells[j + 2, i + 1].Value = tempCoty.CityName;
}
if (tempCityCount > 0)
{
var tempName = package.Workbook.Names.Add(item.ProvinceName, sheet.Cells[2, i + 1, tempCityCount + 1, i + 1]);
//var tempName = sheet.Names.Add(item.ProvinceName, sheet.Cells[2, i + 1, tempCityCount + 1, i + 1]);
}
}
//sheet.Names.Add("省份", sheet.Cells[1, 1, 1, ls_p.Count]); //名称管理器 使用范围sheet 这里指SourceSheet
package.Workbook.Names.Add("省份", sheet.Cells[1, 1, 1, ls_p.Count]);
// 添加工作表 展示下拉框
var homeSheet = package.Workbook.Worksheets.Add("HomeSheet");
homeSheet.Cells["A1"].Style.Font.Bold = true;
homeSheet.Cells["B1"].Style.Font.Bold = true;
homeSheet.Cells["A1"].Value = "省份";
homeSheet.Cells["B1"].Value = "城市";
//数据有效性或者数据验证
//省份下拉框
var validation = homeSheet.DataValidations.AddListValidation("A2:A10");
validation.ShowErrorMessage = true;
validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
validation.ErrorTitle = "An invalid value was entered";
validation.Error = "Select a value from the list";
validation.Formula.ExcelFormula = "省份";
//城市下拉框
var validation2 = homeSheet.DataValidations.AddListValidation("B2:B10");
validation2.ShowErrorMessage = true;
validation2.ErrorStyle = ExcelDataValidationWarningStyle.warning;
validation2.ErrorTitle = "An invalid value was entered";
validation2.Error = "Select a value from the list";
validation2.Formula.ExcelFormula = "INDIRECT($A2)";
package.SaveAs(output);
}
结果