Excel下载并导出

/// <summary>
/// 1-Excel导出下载文件
/// </summary>
/// <returns></returns>
[HttpGet]
[Route("api/DataBase/DownloadFile/")]
public IHttpActionResult DownloadFile(auto_filled_user auto_filled_user)
{
string name = ExcelExport(auto_filled_user);
var browser = string.Empty;
if (HttpContext.Current.Request.UserAgent != null)
{
browser = HttpContext.Current.Request.UserAgent.ToUpper();
}
string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "UploadExcel", "" + name + ".xlsx");
HttpResponseMessage httpResponseMessage = new HttpResponseMessage(HttpStatusCode.OK);
FileStream fileStream = File.OpenRead(filePath);
httpResponseMessage.Content = new StreamContent(fileStream);
httpResponseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
httpResponseMessage.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName =
browser.Contains("FIREFOX")
? Path.GetFileName(filePath)
: HttpUtility.UrlEncode(Path.GetFileName(filePath))
//FileName = HttpUtility.UrlEncode(Path.GetFileName(filePath))
};
return ResponseMessage(httpResponseMessage);
}

 

 

/// <summary>
/// 2-数据导出
/// </summary>
public string ExcelExport(auto_filled_user auto_filled_user)
{
try
{
string domain = auto_filled_user == null ? "" : auto_filled_user.domain.ToString();
int userid = auto_filled_user == null ? 0 : auto_filled_user.userId;
DataTable dt = new DataTable();
string businessname = "";
using (SqlConnection conn = new SqlConnection(connstr))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string sql = @"SELECT
o.ordergoodsid,
o.ordergoodscode,
b.businessname,
p.productname,
o.number,
o.amount,
o.orderdate,
o.receiver,
o.tel,
o.deliveryplace,
CASE o.paytype
WHEN '0' THEN
'微信支付'
WHEN '1' THEN
'支付宝支付'
END AS paytype,
o.expresscompany,
o.expressnum
FROM
ordergoods o
LEFT OUTER JOIN product p ON o.productid = p.productid
LEFT OUTER JOIN business b ON p.businessid = b.businessid
WHERE
o.orderstate = '1' ";
if (domain == "0")
{
string businessid = context.business.SingleOrDefault(s => s.userid == userid) == null ? "" : context.business.SingleOrDefault(s => s.userid == userid).businessid;
businessname = context.business.SingleOrDefault(s => s.userid == userid) == null ? "" : context.business.SingleOrDefault(s => s.userid == userid).businessname;
//查询自己的数据
sql += " and p.businessid = '" + businessid + "'";
}
SqlCommand com = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(com);
adapter.Fill(dt);
}
string date = DateTime.Now.ToString("yyyyMMddHHmmss");
string name = businessname + "待发货订单" + date;
string fileName = HttpContext.Current.Server.MapPath("~/UploadExcel/" + name + ".xlsx");
DataTableExcel(dt, fileName);
return name;

}
catch (Exception ex)
{
return "";
}
}

 

 

/// <summary>
/// 3-导出数据到Ecxcel
/// </summary>
/// <param name="dataTable">数据来源</param>
/// <param name="fileName">存储路径</param>
public static void DataTableExcel(DataTable dataTable, string fileName)
{
//创建EXCEL工作薄
IWorkbook workBook = new XSSFWorkbook();
//创建sheet文件表
ISheet sheet = workBook.CreateSheet("Sheet1");
#region 创建Excel表头
//创建表头
IRow header = sheet.CreateRow(0);

header.Height = 18 * 20;//设置行高
var cellTitel = header.CreateCell(0);
//给标题头添加格式
ICellStyle styleTitle = workBook.CreateCellStyle();
styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
styleTitle.VerticalAlignment = VerticalAlignment.Center;
IFont fontTitle = workBook.CreateFont();//新建一个字体样式对象
fontTitle.Boldweight = 500;//设置字体加粗样式
fontTitle.FontName = "微软雅黑";
fontTitle.FontHeightInPoints = 12;//设置字体大小
styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中

for (int i = 0; i < dataTable.Columns.Count; i++)
{
//给表创建列单元格并填充列名称，此处只做首行列Columns创建和填充
ICell cell = header.CreateCell(i);
cell.SetCellValue(SetColumnName(dataTable.Columns[i].ColumnName, sheet));
cell.CellStyle = styleTitle;//设置表头的格式
}
#endregion
#region 填充Excel单元格中的数据
//给工作薄中非表头填充数据，遍历行数据并进行创建和填充表格
for (int i = 0; i < dataTable.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);//表示从整张数据表的第二行开始创建并填充数据，第一行已经创建。
for (int j = 0; j < dataTable.Columns.Count; j++)//遍历并创建每个单元格cell，将行数据填充在创建的单元格中。
{
//将数据读到cell单元格中
ICell cell = row.CreateCell(j);
if (j == 0)
{
ICellStyle locked = workBook.CreateCellStyle();
locked.IsLocked = true;//第一列被锁定
cell.CellStyle = locked;
sheet.ProtectSheet("password");
}
else
{
ICellStyle unlocked = workBook.CreateCellStyle();
unlocked.IsLocked = false;//没被锁定
cell.CellStyle = unlocked;
}
cell.SetCellValue(dataTable.Rows[i][j].ToString());//对数据为null的情况进行处理
}
}
#endregion

#region 工作流创建Excel文件
//工作流写入，通过流的方式进行创建生成文件
MemoryStream stream = new MemoryStream();
workBook.Write(stream);
byte[] buffer = stream.ToArray();
//FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
try
{
fs.Write(buffer, 0, buffer.Length);
fs.Flush();

}
catch
{
//异常不做任何处理，好处是让客户感觉没有问题，缺点是不利于查找程序的问题，需要日志文件跟踪。
}
finally
{
//写到客户端
//DownloadFile(fs);
fs.Dispose();//出现异常时，手动释放fs写对象
stream.Dispose();//出现异常时，手动释放stream流对象，防止卡死的现象
}
}

#endregion
}

 

 

/// <summary>
/// 4-辅助方法拼接列名，当然这个列名称可以直接在sql语句中采用column as "中文列名称"即可
/// </summary>
/// <param name="name"></param>
/// <param name="sheet"></param>
/// <returns></returns>
private static string SetColumnName(string name, ISheet sheet)
{
string columnName = string.Empty;
#region 匹配列名称
switch (name)
{
case "ordergoodsid":
columnName = "订单编号";
sheet.SetColumnWidth(0, 24 * 256);
//sheet.GetColumnStyle(0).IsLocked = true;
break;
case "ordergoodscode":
columnName = "订单号";
sheet.SetColumnWidth(1, 16 * 256);
//sheet.GetColumnStyle(1).IsLocked = false;
break;
case "businessname":
columnName = "商家名称";
sheet.SetColumnWidth(2, 20 * 256);
break;
case "productname":
columnName = "商品名称";
sheet.SetColumnWidth(3, 16 * 256);
break;
case "number":
columnName = "订单数量";
sheet.SetColumnWidth(4, 12 * 256);
break;
case "amount":
columnName = "订单金额";
sheet.SetColumnWidth(5, 12 * 256);
break;
case "orderdate":
columnName = "下单时间";
sheet.SetColumnWidth(6, 20 * 256);
break;
case "receiver":
columnName = "收货人";
sheet.SetColumnWidth(7, 12 * 256);
break;
case "tel":
columnName = "收货人电话";
sheet.SetColumnWidth(8, 12 * 256);
break;
case "deliveryplace":
columnName = "收货人地址";
sheet.SetColumnWidth(9, 36 * 256);
break;
case "paytype":
columnName = "支付方式";
sheet.SetColumnWidth(10, 12 * 256);
break;
case "expresscompany":
columnName = "快递公司";
sheet.SetColumnWidth(11, 20 * 256);
break;
case "expressnum":
columnName = "快递单号";
sheet.SetColumnWidth(12, 14 * 256);
break;
}
#endregion
return columnName;
}

服务器中Excel导出报错（对路径的访问被拒绝）：

解决方法：IIS的应用程序池-找到该项目-右键高级设置-找到标识-将ApplicationPoolIdentity改成LocalSystem-重新启动