按Excel的模板导出数据
思路：先配置模板数据，再Excel模板上传到服务器；然后下载excel模板；读取文档，写入数据，另存为新的文件
sql语句模板
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 = 1024; byte[] buffer = new byte[bufferLen]; int count = 0; while ((count = instream.Read(buffer, 0, bufferLen)) > 0) { outstream.Write(buffer, 0, count); } return outstream; }
excel模板
#region Excel模板 public class ExcelModel { /// <summary> /// 标题位置 /// </summary> public ExcelCloumn Title { get; set; } /// <summary> /// 列 /// </summary> public List<ExcelCloumn> ExcelCloumns { get; set; } } public class ExcelCloumn { /// <summary> /// 列名(单号) /// </summary> public string cloumnName { get; set; } /// <summary> /// 字母（A） /// </summary> public string Letter { get; set; } /// <summary> /// 字母开始 （A1的取1） /// </summary> public int Index { get; set; } /// <summary> /// 字段名 /// </summary> public string colunmEnglish { get; set; } /// <summary> /// 默认值 /// </summary> public string DefaultValue { get; set; } } #endregion
按excel的模板文件存放位置以及获取模板
/// <summary> /// 模板文件 /// </summary> public class op_client_template_mapping { public int index { get; set; } /// <summary> /// templateId /// </summary> public int templateId { get; set; } /// <summary> /// 模板类型 /// </summary> public string categoryName { get; set; } /// <summary> /// 模板名称 /// </summary> public string templateName { get; set; } /// <summary> /// 模板文件 /// </summary> public string customerXML { get; set; } /// <summary> /// 创建时间 /// </summary> public DateTime cdate { get; set; } /// <summary> /// 创建人 /// </summary> public string cinput { get; set; } /// <summary> /// 是否是默认的 /// </summary> public bool is_sys { get; set; } /// <summary> /// 公式 2018.09.28新增 /// </summary> public string formula { get; set; } /// <summary> /// 文件存放路径 /// </summary> public string filePath { get; set; } /// <summary> /// 文件名 /// </summary> public string fileNames { get; set; } /// <summary> /// 文件数据 /// </summary> public byte[] filedata { get; set; } }
处理下载的excel文件流
public MemoryStream StreamToMemoryStream(Stream instream) { MemoryStream outstream = new MemoryStream(); const int bufferLen = 1024; byte[] buffer = new byte[bufferLen]; int count = 0; while ((count = instream.Read(buffer, 0, bufferLen)) > 0) { outstream.Write(buffer, 0, count); } return outstream; }
处理模板数据，我保存的是字符串
#region 处理字符串 public ExcelModel HandelString(string customerXML, List<ColunmData> colunmData) { ExcelModel excelModel = new ExcelModel(); if (customerXML.Contains("<标题>") && customerXML.Contains("</标题>")) { string title = SubString(customerXML, "标题"); excelModel.Title = HandelEqe(title, colunmData); } if (customerXML.Contains("<头部>") && customerXML.Contains("</头部>")) { string cloumns = SubString(customerXML, "头部"); string[] data = cloumns.Replace("\r", "").Split('\n'); List<ExcelCloumn> excelCloumn = new List<ExcelCloumn>(); for (int i = 0; i < data.Count(); i++) { excelCloumn.Add(HandelEqe(data[i], colunmData)); } excelModel.ExcelCloumns = excelCloumn; } return excelModel; } /// <summary> /// 截取字符串 /// </summary> private string SubString(string customerXML, string name) { int stateIndex = customerXML.IndexOf("<" + name + ">") + 4; int endIndex = customerXML.IndexOf("</" + name + ">"); return customerXML.Substring(stateIndex, endIndex - stateIndex).Trim(); } private ExcelCloumn HandelEqe(string cloumn, List<ColunmData> colunmData) { ExcelCloumn model = new ExcelCloumn(); if (cloumn.Contains("=")) { string[] str = cloumn.Split('='); model.cloumnName = str[0].Trim(); if (str.Length > 1) { Regex reg = new Regex(@"[1-9]\d*"); model.Index = Common.Utils.ObjToInt(reg.Match(str[1]).Value, 1); model.Letter = str[1].Replace(model.Index.ToString(), "").Trim(); } if (str.Length > 2) { model.DefaultValue = str[2].Trim(); } } else { model.cloumnName = cloumn.Trim(); } model.colunmEnglish = colunmData.Where(it => it.colunmName == model.cloumnName).FirstOrDefault()?.colunmEnglish ?? ""; return model; } #endregion
导出数据大概
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(0, SeekOrigin.Begin); int buffsize = (int)ms.Length; //rs.Length 此流不支持查找,先转为MemoryStream byte[] bytes = new byte[buffsize]; ms.Read(bytes, 0, buffsize); Workbook workbook = new Workbook(ms); ms.Flush(); ms.Close(); sm.Flush(); sm.Close(); Worksheet worksheet = workbook.Worksheets[0]; //工作表 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 = 0; 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+1); } else { sheet.Cells[it.Letter + row].PutValue(it.DefaultValue ?? ""); } } i++; } sheet.AutoFitColumns(); } #endregion
