正文
按Excel的模板导出数据
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
思路：把Excel模板上传到服务器；然后读取文档，写入数据，另存为新的文件
然后就是导出的时候处理数据定义的字符串，按数据导出；注意读取的数据流要处理下，不然会报异常
public Stream GetExcelStream(op_client_template_mapping model)//model 配置文件模型
{
string fileUrl = _BillFileService.GetHttp() + "/" + model.filePath + "/" + model.fileNames;
return WebRequest.Create(fileUrl).GetResponse().GetResponseStream();
}
//文件里转换成能处理的流
public MemoryStream StreamToMemoryStream(Stream instream)
{
MemoryStream outstream = new MemoryStream();
const int bufferLen = ;
byte[] buffer = new byte[bufferLen];
int count = ;
while ((count = instream.Read(buffer, , bufferLen)) > )
{
outstream.Write(buffer, , count);
}
return outstream;
}
导出数据大概
public void AsposeToExcel<T>(List<T> data, ExcelModel excelModel, op_client_template_mapping mapping, string path)
{
try
{
Stream sm = GetExcelStream(mapping);
var ms = StreamToMemoryStream(sm);
ms.Seek(, SeekOrigin.Begin); int buffsize = (int)ms.Length; //rs.Length 此流不支持查找,先转为MemoryStream
byte[] bytes = new byte[buffsize]; ms.Read(bytes, , buffsize);
Workbook workbook = new Workbook(ms);
ms.Flush(); ms.Close();
sm.Flush(); sm.Close();
Worksheet worksheet = workbook.Worksheets[]; //工作表 if (excelModel.Title != null && !string.IsNullOrEmpty(excelModel.Title?.cloumnName))
{
SetTitle(worksheet, excelModel.Title.cloumnName, excelModel.Title.Letter + excelModel.Title.Index);
}
SetCellData(worksheet, data, excelModel); workbook.Save(path);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
} private static void SetTitle(Worksheet sheet, string title, string index)
{
sheet.Cells[index].PutValue(title);
} /// <summary>
/// 填充字段
/// </summary>
private static void SetCellData<T>(Worksheet sheet, List<T> data, ExcelModel excelModel)
{
var noData = excelModel.ExcelCloumns.Where(it => it.colunmEnglish.Equals(""));//没有绑定的数据取默认值
int i = ;
foreach (T item in data)
{
foreach (PropertyDescriptor pd in TypeDescriptor.GetProperties(typeof(T)))
{
var column = excelModel.ExcelCloumns.Where(it => it.colunmEnglish.Equals(pd.Name)).FirstOrDefault();
if (column != null)//存在该字段就赋值
{
int row = column.Index + i;
if (pd.PropertyType.ToString() == "System.DateTime")
{
sheet.Cells[column.Letter + row].PutValue(pd.GetValue(item).ToString());//（column.Letter + row）等于 A2，C2这样赋值
}
else
{
sheet.Cells[column.Letter + row].PutValue(pd.GetValue(item));
}
}
}
foreach (var it in noData)//赋值默认值
{
int row = it.Index + i;
if (it.cloumnName == "序号")
{
sheet.Cells[it.Letter + row].PutValue(i+);
}
else
{
sheet.Cells[it.Letter + row].PutValue(it.DefaultValue ?? "");
}
}
i++;
}
sheet.AutoFitColumns();
}
#endregion
