public IHttpActionResult DownloadTaskApprovedList(string taskId) { var responseMessage = new HttpResponseMessage(HttpStatusCode.OK); var task = TemplateConfigInstanceOperator.Instance.GetModel(taskId); //var attas = AttachmentOperator.Instance.GetAttachmentListByTask(task.ID, "UploadTaskAttach"); var tasks = TemplateTaskOperator.Instance.GetList(taskId); //string fileName = System.IO.Path.Combine(CommonFilePath, task.TemplateConfigInstanceName + "_审批流程.xlsx"); var ds = DbHelper.RunSqlReturnDS(string.Format(@" EXEC P_GetTaskApprovedList '{0}' ", taskId), "DBConnectionString"); if (ds == null || ds.Tables.Count < 1) { throw new BizException("没有可下载的文件"); } //数据导出列 string[] columnnames = new string[] { "序号", "接收人部门", "接收人", "填报日期", "审批日期", "审批流程" }; ImportExcelCriteria iecriteria = new ImportExcelCriteria(); iecriteria.data = ds.Tables[0]; //导出的数据 iecriteria.ColumnNames = columnnames; //excel显示列 iecriteria.TempleName = "TaskApprovedListTemplate.xlsx"; //模板名字 iecriteria.ExcelName = task.TemplateConfigInstanceName + "_审批流程"; //文件名字 iecriteria.RowIndex = 1; //从第几行插入数据 iecriteria.CellIndex = 0; //从第几列插入数据 iecriteria.RecordStatus = "Create"; //新增 iecriteria.IsAddHj = false; Dictionary <string, string> dictionary = ExcelData(iecriteria); var ms = new MemoryStream(); using (FileStream fileStream = new FileStream(dictionary["filePath"], FileMode.Open, FileAccess.Read, FileShare.Read)) { // 读取文件的 byte[] byte[] bytes = new byte[fileStream.Length]; fileStream.Read(bytes, 0, bytes.Length); fileStream.Close(); // 把 byte[] 转换成 Stream ms = new MemoryStream(bytes); } if (File.Exists(dictionary["filePath"])) { File.Delete(dictionary["filePath"]); } //return new FileResult(string.Format("{0}{1}", Guid.NewGuid(), ".xlsx"), ms.ToArray(), Request); //Request.setCharacterEncoding() return(new FileResult(string.Format("{0}_审批流程{1}", task.TemplateConfigInstanceName, ".xlsx"), ms.ToArray(), Request)); }
private Dictionary <string, string> ExcelData(ImportExcelCriteria criteria) { string fileName = criteria.ExcelName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx"; // 文件名称 string urlPath = Path.Combine(CommonFilePath, "File//Download//") + fileName; // 文件下载的URL地址,供给前台下载 string filePath = Path.Combine(CommonFilePath, "File//ExcelTemplate//") + fileName; // 文件路径 Dictionary <string, string> idictionary = new Dictionary <string, string>(); idictionary.Add("urlPath", urlPath); idictionary.Add("filePath", filePath); // 1.检测是否存在文件夹,若不存在就建立个文件夹 string directoryName = Path.GetDirectoryName(filePath); if (!Directory.Exists(directoryName)) { Directory.CreateDirectory(directoryName); } ISheet sheet = null;//申明标签页 string url = string.Empty; //把文件内容导入到工作薄当中,然后关闭文件 if (criteria.RecordStatus == "Edit") { url = criteria.TempleName; } else { url = Path.Combine(CommonFilePath, "File//ExcelTemplate//") + criteria.TempleName; } FileStream fs = File.OpenRead(url); IWorkbook workbook = new XSSFWorkbook(fs); //设置单元格样式 ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; fs.Close(); if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet IDataFormat dataformat = workbook.CreateDataFormat(); //如果有数据就进行导出操作 if (criteria.data.Rows.Count > 0) { //读取数据 for (int i = 0; i < criteria.data.Rows.Count; i++) { //创建行 IRow row = sheet.CreateRow(i + criteria.RowIndex); if (criteria.IsAddHj) { ICell cell = row.CreateCell(0); cell.SetCellValue("合计"); style.DataFormat = dataformat.GetFormat("#,##0.00"); cell.CellStyle = style; } for (int j = 0; j < criteria.ColumnNames.Length; j++) { ICell cell = row.CreateCell(j + criteria.CellIndex); cell.CellStyle = style; string columnName = criteria.ColumnNames[j]; switch (criteria.data.Rows[i][columnName].GetType().Name) { case "String": //字符串类型 var stringvalue = criteria.data.Rows[i][columnName]; cell.SetCellType(CellType.String); cell.SetCellValue(stringvalue.ToString()); break; case "Decimal": //钱型 //设置 数字格式,样式:123,234.00 style.DataFormat = dataformat.GetFormat("#,##0.00"); //分段添加,号 var decimalzhi = criteria.data.Rows[i][columnName]; cell.CellStyle = style; cell.SetCellValue(Convert.ToDouble(decimalzhi)); break; case "Int16": //整型 case "Int32": case "Int64": case "Byte": var intzhi = criteria.data.Rows[i][columnName]; cell.SetCellValue(Convert.ToInt32(intzhi)); break; case "DBNull": //空值处理 cell.SetCellValue(""); break; case "DateTime": //日期类型 var datetimezhi = Convert.ToDateTime(criteria.data.Rows[i][columnName]).ToString("yyyy-MM-dd"); cell.SetCellValue(datetimezhi); break; } } } } } FileStream file = new FileStream(filePath, FileMode.Create); workbook.Write(file); file.Close(); // 5。返回路径 return(idictionary); }