如何在C#中设置导出Excel表格里的单元格宽高
废话少说吧,直奔主题。
先来看一下还没有设置样式,直接导出数据时的效果图:
是不是觉得上面的图里的汉字有点靠的太近了?看得不是很顺眼,因为部分的字体被遮住了。
接下来看看设置了宽度和高度的效果图:
来看一下是如何实现这种效果的。
public ActionResult ExportToExcel(){
//首先查询出你要导出的数据
List<BorrowVo> listBorrow = (from tbBorrow in myModels.PW_Borrow
join tbCommodity in myModels.PW_Commodity on tbBorrow.CommodityID equals tbCommodity.CommodityID
join tbWarehouse in myModels.PW_Warehouse on tbBorrow.WarehouseID equals tbWarehouse.WarehouseID
join tbSupplier in myModels.PW_Supplier on tbBorrow.SupplierID equals tbSupplier.SupplierID
join tbUser in myModels.PW_User on tbBorrow.UserID equals tbUser.UserID
orderby tbBorrow.BorrowID descending
select new BorrowVo{
BorrowID = tbBorrow.BorrowID, //借出ID
BorrowDate = tbBorrow.BorrowDate, //业务日期
BorrowNum = tbBorrow.BorrowNum, //单据编号
CommodityName = tbCommodity.CommodityName, //借出商品
WarehouseName = tbWarehouse.WarehouseName, //入库仓库
SupplierName = tbSupplier.SupplierName, //供应商名称
BorrowPlanDate = tbBorrow.BorrowPlanDate, //预计归还日期
UserName = tbUser.UserName //经手人
}).ToList();
HSSFWorkbook workBook = new HSSFWorkbook(); //创建工作簿
//创建工作表
NPOI.SS.UserModel.ISheet BorrowSheet = workBook.CreateSheet("借入单列表");
//创建表头行
NPOI.SS.UserModel.IRow BorrowRow = BorrowSheet.CreateRow(0);
//设置表头
BorrowRow.CreateCell(0).SetCellValue("业务日期");
BorrowRow.CreateCell(1).SetCellValue("单据编号");
BorrowRow.CreateCell(2).SetCellValue("供应商名称");
BorrowRow.CreateCell(3).SetCellValue("借入商品");
BorrowRow.CreateCell(4).SetCellValue("预计归还日期");
BorrowRow.CreateCell(5).SetCellValue("入库仓库");
BorrowRow.CreateCell(6).SetCellValue("经手人");
//为Excel表格添加数据
for (int i = 0; i < listBorrow.Count(); i++){
NPOI.SS.UserModel.IRow rowTemp = BorrowSheet.CreateRow(i + 1); //创建行
//添加数据
rowTemp.CreateCell(0).SetCellValue(listBorrow[i].BorrowDate);
rowTemp.CreateCell(1).SetCellValue(listBorrow[i].BorrowNum);
rowTemp.CreateCell(2).SetCellValue(listBorrow[i].SupplierName);
rowTemp.CreateCell(3).SetCellValue(listBorrow[i].CommodityName);
rowTemp.CreateCell(4).SetCellValue(listBorrow[i].BorrowPlanDate);
rowTemp.CreateCell(5).SetCellValue(listBorrow[i].WarehouseName);
rowTemp.CreateCell(6).SetCellValue(listBorrow[i].UserName);
}
//根据内容动态调整excel单元格宽度
BorrowSheet.SetColumnWidth(0, 20 * 250);
BorrowSheet.SetColumnWidth(1, 20 * 250);
BorrowSheet.SetColumnWidth(2, 20 * 250);
BorrowSheet.SetColumnWidth(3, 20 * 250);
BorrowSheet.SetColumnWidth(4, 20 * 250);
BorrowSheet.SetColumnWidth(5, 20 * 250);
BorrowSheet.SetColumnWidth(6, 20 * 250);
//根据内容动态调整excel单元格高度
IRow currentRow = BorrowSheet.GetRow(BorrowSheet.FirstRowNum);
ICell currentCell = currentRow.GetCell(BorrowSheet.FirstRowNum);
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
currentRow.HeightInPoints = 20 * (length / 60 + 1);
//为Excel文件命名
string fileName = "借入单列表" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
//转化为内存流
MemoryStream ExcelStream = new MemoryStream();
//将Excel文件写入内存流
workBook.Write(ExcelStream);
//Seek(0,Seek.begin) 第一个参数表示相对位置,第二个参数表示参照位置
ExcelStream.Seek(0, SeekOrigin.Begin);
//MIME文件类型(Multipurpose Internet Mail Extensions)多用途互联网邮件扩展类型
return File(ExcelStream, "application/vnd.ms-excel", fileName);
}
注:在做导入功能之前,要把引用一个东西,名为 NPOI.dll,否则无法完成导出的功能