庆军之导出excel 利用npoi基于模板导出excel
不想废话。本来想用反射，试了一把。写的让我崩溃。最后小僧启发了我，我改用JObject来处理绑定的问题。源代码如下。垃圾代码，自己记录下。
public class WriteExcelDataDefine
{
public int RowIndex { get; set; }
public int CellIndex { get; set; }
/// <summary>
/// 0空格不管
/// 3子对象
/// 4绑定对象
/// 5常量
/// </summary>
public byte WriteType { get; set; }
/// <summary>
/// 0 单行
/// 1 绑定多行
/// </summary>
public byte WriteMode { get; set; }
public string DataType { get; set; }//data.fuck data 如果是固定字符 不填
public string DataContent { get; set; }//data.fuck fuck 如果是固定字符 sss sss
/// <summary>
/// 占用行数
/// </summary>
public int MaxWriteRowCount { get; set; } = -1;
/// <summary>
/// 数据源行
/// </summary>
public int SourceCount { get; set; } = 1;
}
/// <summary>
/// 解析Excel到数据绑定1
/// </summary>
/// <param name="filepath"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public List<WriteExcelDataDefine> TemplatePareExcelDefineSetup1(string filepath)
{
if (!File.Exists(filepath))
{
throw new Exception("模板不存在");
}
IWorkbook workbook = null;
//string extension = Path.GetExtension(url);
using (FileStream fs = System.IO.File.OpenRead(filepath))
{
//把xlsx文件中的数据写入workbook中
workbook = new XSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0);//目前只支持一个sheet，并且在第一个
List<WriteExcelDataDefine> writeExcelDatas = new List<WriteExcelDataDefine>(100);
//Dictionary<Point, string> excelBind = new Dictionary<Point, string>(100);
for (int rowindex = 0; rowindex <= sheet.LastRowNum; rowindex++)
{
IRow onerow = sheet.GetRow(rowindex);
if (onerow == null)
{
continue;
}
for (int colindex = 0; colindex < onerow.LastCellNum; colindex++)
{
var onecellvalue = onerow.GetCell(colindex)?.ToString();
if (string.IsNullOrWhiteSpace(onecellvalue))
{
continue;
}
if (onecellvalue.StartsWith("#[")
&& onecellvalue.EndsWith("]"
))
{
if (onecellvalue.IndexOf(".") > 0)//可能是子对象
{
var datatypedefines = onecellvalue.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
if (datatypedefines.Length != 2)//不支持多层路径暂时或者写错了
{
writeExcelDatas.Add(new WriteExcelDataDefine()
{
RowIndex = rowindex,
CellIndex = colindex,
DataContent = onecellvalue,
DataType = "",
MaxWriteRowCount = 1,
WriteType = 5
});
}
else//
{
writeExcelDatas.Add(new WriteExcelDataDefine()
{
RowIndex = rowindex,
CellIndex = colindex,
DataContent = datatypedefines[1].Replace("]", ""),
DataType = datatypedefines[0].Replace("#[", ""),
MaxWriteRowCount = -1,
WriteType = 3
});
}
}
else
{
writeExcelDatas.Add(new WriteExcelDataDefine()
{
RowIndex = rowindex,
CellIndex = colindex,
DataContent = onecellvalue.Replace("#[", "").Replace("]", ""),
DataType = "",
MaxWriteRowCount = -1,
WriteType = 4
});
}
}
else//常量只能为一行 应该是非绑定值
{
writeExcelDatas.Add(new WriteExcelDataDefine()
{
RowIndex = rowindex,
CellIndex = colindex,
DataContent = onecellvalue,
DataType = "",
MaxWriteRowCount = 1,
WriteType = 5
});
}
}
}
//删掉没有必要转换的常量行
var orderbyRows = writeExcelDatas.GroupBy(q => q.RowIndex).OrderBy(q => q.Key);
List<int> removeRowIndex = new List<int>(orderbyRows.Count());
foreach (var item in orderbyRows)//按行来分析
{
if (item.All(q => q.WriteType == 5))
{
removeRowIndex.Add(item.First().RowIndex);
}
}
if (removeRowIndex.Count > 0)
{
foreach (var item in removeRowIndex)
{
writeExcelDatas.RemoveAll(q => q.RowIndex == item);
}
}
return writeExcelDatas;
}
public void DataBindPareExcelDefineSetup2(string filepath,
List<WriteExcelDataDefine> excelDataDefines, object datas)
{
JToken data = JToken.FromObject(datas);
//dynamic New = JObject.FromObject(data);
//DataTable dt = new DataTable();
//Dictionary<string,string>
//var t = typeof(T);
Dictionary<string, int> mTableDefines = new Dictionary<string, int>(excelDataDefines.Count);
var orderbyRows = excelDataDefines.GroupBy(q => q.RowIndex).OrderBy(q => q.Key);
foreach (var item in orderbyRows)//按行来分析
{
var orderbyCells = item.OrderBy(q => q.CellIndex);//按列排序
//找到所有的数据绑定
//var alldatabindCells = orderbyCells.Where(q=>q.WriteType == 3 && q.WriteType == 4).Distinct().ToList();
//var alltps = t.GetProperties();
//var tName = t.GetName();
//判断绑定对象的类型
//if (TestIEnumerable(t))//需要循环
//{
setbindPObjects(data,
mTableDefines, orderbyCells);
var maxrowCount = item.Max(q => q.MaxWriteRowCount);
foreach (var cellname in item)
{
cellname.MaxWriteRowCount = maxrowCount;
}
}//重新设置行
IWorkbook workbook = null;
//string extension = Path.GetExtension(url);
using (FileStream fs = System.IO.File.OpenRead(filepath))
{
//把xlsx文件中的数据写入workbook中
workbook = new XSSFWorkbook(fs);
}
ISheet sheet = workbook.GetSheetAt(0);//目前只支持一个sheet，并且在第一个
int currentrowIndex = 0;//模板行所在的位置
int addrowCount = 0;
//开始渲染
foreach (var item in orderbyRows)//按行来分析
{
//addrowCount += 1;
var onerowitem = item.First();
for (int datarowindex = 0; datarowindex < onerowitem.MaxWriteRowCount; datarowindex++)
{
currentrowIndex = addrowCount + onerowitem.RowIndex;
//重新计算了模板行的位置
IRow writeRow = null;
if (datarowindex == 0)//模板行所在，直接用
{
writeRow = sheet.GetRow(currentrowIndex);
}
else
{
sheet.CreateRow(currentrowIndex + 1);//新行
writeRow = sheet.CopyRow(currentrowIndex, currentrowIndex+1);//复制上一行到新行
}
//按列排序 写列的数据
foreach (var cellitem in item.OrderBy(q => q.CellIndex))
{
//写数据
if (cellitem.WriteType == 0) continue;
var writeCell = writeRow.GetCell(cellitem.CellIndex);
if (cellitem.WriteType == 5)//常量
{
writeCell.SetCellValue(cellitem.DataContent ?? "");
continue;
}//写入常量
if (cellitem.WriteType == 4)//绑定对象
{
if (cellitem.WriteMode == 0)//对象不是数组
{
if (data.Type == JTokenType.Array)
{
writeCell.SetCellValue(((JArray)data)[0][cellitem.DataContent]?.ToString());
}
else
{
writeCell.SetCellValue(((JObject)data)[cellitem.DataContent]?.ToString());
}
}
else//多行
{
if (data.Type == JTokenType.Array)
{
//获取当前行
//var currentjrow = 1;
if (cellitem.SourceCount < datarowindex)
{
writeCell.SetCellValue(((JArray)data)[0][cellitem.DataContent]?.ToString());
}
else
{
writeCell.SetCellValue(((JArray)data)[datarowindex][cellitem.DataContent]?.ToString());
}
}
else
{
writeCell.SetCellValue(((JObject)data)[cellitem.DataContent]?.ToString());
}
}
}
if (cellitem.WriteType == 3)//子对象
{
if (cellitem.WriteMode == 0)//单行
{
if (data.Type == JTokenType.Array)
{
var bindsonObj = ((JArray)data)[0][cellitem.DataType];
if (bindsonObj.Type == JTokenType.Array)
{
//获取当前行
writeCell.SetCellValue(((JArray)bindsonObj)[0][cellitem.DataContent]?.ToString());
}
else
{
writeCell.SetCellValue(bindsonObj[cellitem.DataContent]?.ToString());
}
//writeCell.SetCellValue(((JArray)data)[0][cellitem.DataContent].ToString());
}
else
{
var bindsonObj = ((JObject)data)[cellitem.DataType];
if (bindsonObj.Type == JTokenType.Array)
{
writeCell.SetCellValue(((JArray)bindsonObj)[0][cellitem.DataContent]?.ToString());
//获取当前行
}
else
{
writeCell.SetCellValue(bindsonObj[cellitem.DataContent]?.ToString());
}
}
}
else//多行
{
if (data.Type == JTokenType.Array)
{
var bindsonObj = ((JArray)data)[0][cellitem.DataType];
if (bindsonObj.Type == JTokenType.Array)
{
if (cellitem.SourceCount < datarowindex)
{
writeCell.SetCellValue(((JArray)bindsonObj)[0][cellitem.DataContent]?.ToString());
// writeCell.SetCellValue(((JArray)data)[0][cellitem.DataContent].ToString());
}
else
{
writeCell.SetCellValue(((JArray)bindsonObj)[datarowindex][cellitem.DataContent]?.ToString());
}
}
else
{
writeCell.SetCellValue(bindsonObj[cellitem.DataContent]?.ToString());
}
//writeCell.SetCellValue(((JArray)data)[0][cellitem.DataContent].ToString());
}
else
{
var bindsonObj = ((JObject)data)[cellitem.DataType];
if (bindsonObj.Type == JTokenType.Array)
{
//获取当前行
if (cellitem.SourceCount < datarowindex)
{
writeCell.SetCellValue(((JArray)bindsonObj)[0][cellitem.DataContent]?.ToString());
// writeCell.SetCellValue(((JArray)data)[0][cellitem.DataContent].ToString());
}
else
{
writeCell.SetCellValue(((JArray)bindsonObj)[datarowindex][cellitem.DataContent]?.ToString());
}
}
else
{
writeCell.SetCellValue(bindsonObj[cellitem.DataContent]?.ToString());
}
}
}
}
}
if (datarowindex > 0)
addrowCount += 1;
}
//addrowCount -= 1;
}
using (FileStream file2007 = new FileStream("d:\\test20230518" + DateTime.Now.Ticks.ToString() + ".xlsx", FileMode.Create))
{
workbook.Write(file2007);
file2007.Close();
workbook.Close();
}
//MemoryStream memoryStream = new MemoryStream();
//workbook.Write(memoryStream);
//var buf = memoryStream.ToArray();
////memoryStream = (MemoryStream)null;
////返回待下载文件
//Stream stream = new MemoryStream(buf);
//stream.Flush();
//stream.Position = 0L;
//stream.Seek(0L, SeekOrigin.Begin);
////string str = ".xlsx";
//return stream;
}
private bool setEmpateArrayObj(JToken data,
string datapath,
WriteExcelDataDefine cellitem)
{
if (data.Type == JTokenType.Array)
{
var allCount = data.Count();
return setEmpateObj(data.ElementAt(0), datapath, cellitem, allCount);
}
return false;
}
private bool setEmpateObj(JToken data,
string datapath,
WriteExcelDataDefine cellitem,
int allcount=1)
{
if (data.Type == JTokenType.Object
&& data[datapath] != null)
{
cellitem.WriteMode = (byte)(allcount > 1 ? 1 : 0);
cellitem.SourceCount = allcount;
cellitem.MaxWriteRowCount = allcount;//多行
return true;
}
return false;
}
private void setbindPObjects(JToken data,
Dictionary<string, int> mTableDefines,
IOrderedEnumerable<WriteExcelDataDefine> orderbyCells)
{
var bindObjName = data.GetType().Name;
foreach (var cellitem in orderbyCells)
{
if (cellitem.WriteType == 5) continue;//常量跳过
if (cellitem.WriteType == 4
&&
(setEmpateObj(data, cellitem.DataContent, cellitem)
|| setEmpateArrayObj(data, cellitem.DataContent, cellitem)))
{
continue;
}//绑定对象
if (cellitem.WriteType == 3)
{
if (!string.IsNullOrWhiteSpace(cellitem.DataType)
&& !string.IsNullOrWhiteSpace(cellitem.DataContent))
{
JToken sondata = null;
if (data.Type == JTokenType.Array
&& data.Count() > 0)
{
sondata = ((JArray)data)[0][cellitem.DataType];
}
else if (data.Type == JTokenType.Object)
{
sondata = ((JObject)data)[cellitem.DataType];
}
if (sondata != null
&&
(setEmpateObj(sondata, cellitem.DataContent, cellitem)
|| setEmpateArrayObj(sondata, cellitem.DataContent, cellitem)))
{
continue;
}
}
cellitem.DataType = "";
cellitem.DataContent = "";
cellitem.WriteType = 5;
cellitem.WriteMode = 0;
cellitem.SourceCount = 1;
cellitem.MaxWriteRowCount = 1;//多行
}//子对象
}
}
