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);
}
