public void TestAutoSizeColumn()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("43902.xls");
String sheetName = "my sheet";
HSSFSheet sheet = (HSSFSheet)wb.GetSheet(sheetName);
// Can't use literal numbers for column sizes, as
// will come out with different values on different
// machines based on the fonts available.
// So, we use ranges, which are pretty large, but
// thankfully don't overlap!
int minWithRow1And2 = 6400;
int maxWithRow1And2 = 7800;
int minWithRow1Only = 2750;
int maxWithRow1Only = 3300;
// autoSize the first column and check its size before the merged region (1,0,1,1) is set:
// it has to be based on the 2nd row width
sheet.AutoSizeColumn(0);
Assert.IsTrue(sheet.GetColumnWidth(0) >= minWithRow1And2, "Column autosized with only one row: wrong width");
Assert.IsTrue(sheet.GetColumnWidth(0) <= maxWithRow1And2, "Column autosized with only one row: wrong width");
//Create a region over the 2nd row and auto size the first column
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
sheet.AutoSizeColumn(0);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Check that the autoSized column width has ignored the 2nd row
// because it is included in a merged region (Excel like behavior)
NPOI.SS.UserModel.ISheet sheet2 = wb2.GetSheet(sheetName);
Assert.IsTrue(sheet2.GetColumnWidth(0) >= minWithRow1Only);
Assert.IsTrue(sheet2.GetColumnWidth(0) <= maxWithRow1Only);
// Remove the 2nd row merged region and Check that the 2nd row value is used to the AutoSizeColumn width
sheet2.RemoveMergedRegion(1);
sheet2.AutoSizeColumn(0);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
NPOI.SS.UserModel.ISheet sheet3 = wb3.GetSheet(sheetName);
Assert.IsTrue(sheet3.GetColumnWidth(0) >= minWithRow1And2);
Assert.IsTrue(sheet3.GetColumnWidth(0) <= maxWithRow1And2);
}
public string ExportAccountTemplate(SaveFileDialog sflg, DataTable dt)
{
string message = string.Empty;
try
{
string filename = sflg.FileName;
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
//整体样式
ICellStyle style = book.CreateCellStyle();
style.FillPattern = FillPattern.NoFill;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "宋体";
style.SetFont(font);
//style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//一般样式
ICellStyle style_center = book.CreateCellStyle();
style_center.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style_center.VerticalAlignment = VerticalAlignment.Center;
style_center.WrapText = true;
//style_center.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//一般样式
ICellStyle style_left = book.CreateCellStyle();
style_left.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
style_left.WrapText = true;
//style_left.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("清算信息汇总");
sheet.DefaultRowHeight = 20;
for (int columnNum = 0; columnNum < 11; columnNum++)
{
sheet.SetDefaultColumnStyle(columnNum, style);
}
int rowIndex = 0;
#region 添加表头
NPOI.SS.UserModel.IRow rowName = sheet.CreateRow(rowIndex);
rowName.CreateCell(0, CellType.String).SetCellValue("地区");
rowName.CreateCell(1, CellType.String).SetCellValue("序号");
rowName.CreateCell(2, CellType.String).SetCellValue("车辆生产企业");
rowName.CreateCell(3, CellType.String).SetCellValue("车辆型号");
rowName.CreateCell(4, CellType.Numeric).SetCellValue("企业申报推广数");
rowName.CreateCell(5, CellType.Numeric).SetCellValue("企业申请补助标准");
rowName.CreateCell(6, CellType.Numeric).SetCellValue("企业申请清算资金");
rowName.CreateCell(7, CellType.Numeric).SetCellValue("专家组核定的推广数");
rowName.CreateCell(8, CellType.Numeric).SetCellValue("专家组核定的补助标准");
rowName.CreateCell(9, CellType.Numeric).SetCellValue("应清算补助资金");
rowName.CreateCell(10, CellType.String).SetCellValue("核减原因");
rowIndex++;
#endregion
#region 添加数据
int dealerNum = 0;
//第一行总计
var countENTNum = (from DataRow row in dt.Rows select new { ent_num = row["ENT_NUM"] }).Sum(a => Convert.ToDecimal(a.ent_num));
var countENTMoney = (from DataRow row in dt.Rows select new { ent_money = row["SQBZBZ"] }).Sum(a => Convert.ToDecimal(a.ent_money));
var countENTCount = (from DataRow row in dt.Rows select new { ent_count = row["ENT_COUNT"] }).Sum(a => Convert.ToDecimal(a.ent_count));
var countAPPNum = (from DataRow row in dt.Rows select new { app_num = row["APP_NUM"] }).Sum(a => Convert.ToDecimal(a.app_num));
var countAPPMoney = (from DataRow row in dt.Rows select new { app_money = row["APP_MONEY"] }).Sum(a => Convert.ToDecimal(a.app_money));
var countAPPCount = (from DataRow row in dt.Rows select new { app_count = row["APP_COUNT"] }).Sum(a => Convert.ToDecimal(a.app_count));
IRow rowTotal = sheet.CreateRow(rowIndex);
ICell cellTotal = rowTotal.CreateCell(0);
cellTotal.SetCellValue("总计");
cellTotal.CellStyle = style_center;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3));
rowTotal.CreateCell(4, CellType.String).SetCellValue(countENTNum.ToString());
rowTotal.CreateCell(5, CellType.String).SetCellValue(countENTMoney.ToString());
rowTotal.CreateCell(6, CellType.String).SetCellValue(countENTCount.ToString());
rowTotal.CreateCell(7, CellType.String).SetCellValue(countAPPNum.ToString());
rowTotal.CreateCell(8, CellType.String).SetCellValue(countAPPMoney.ToString());
rowTotal.CreateCell(9, CellType.String).SetCellValue(countAPPCount.ToString());
rowIndex++;
#region 循环省份写入
var ProvinceList = dt.AsEnumerable().Select(d => d.Field <string>("DQ")).Distinct().ToList();
for (int i = 0; i < ProvinceList.Count(); i++)
{
dealerNum = 1;
//地区 省份
int DealerCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().Count();
int ClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLXH")).Count();
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + ClxhCount + DealerCount, 0, 0));
IRow rowSFTotal = sheet.CreateRow(rowIndex);
ICell cellSF = rowSFTotal.CreateCell(0);
cellSF.SetCellValue(ProvinceList[i]);
#region 省份合计
// IRow rowSFTotal = sheet.CreateRow(rowIndex);
var dvShengfen = dt.DefaultView;
dvShengfen.RowFilter = String.Format("DQ='{0}'", ProvinceList[i]);
var dtShengfen = dvShengfen.ToTable();
var sum_ENT_NUM = Convert.ToDecimal(dtShengfen.Compute("sum(ENT_NUM)", "TRUE"));
var sum_SQBZBZ = dtShengfen.Compute("sum(SQBZBZ)", "TRUE");
var sum_ENT_COUNT = dtShengfen.Compute("sum(ENT_COUNT)", "TRUE");
var sum_APP_NUM = dtShengfen.Compute("sum(APP_NUM)", "TRUE");
var sum_APP_MONEY = dtShengfen.Compute("sum(APP_MONEY)", "TRUE");
var sum_APP_COUNT = dtShengfen.Compute("sum(APP_COUNT)", "TRUE");
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
ICell cellSFTotal = rowSFTotal.CreateCell(1);
cellSFTotal.SetCellValue("合计");
cellSFTotal.CellStyle = style_center;
rowSFTotal.CreateCell(4, CellType.String).SetCellValue(sum_ENT_NUM.ToString());
rowSFTotal.CreateCell(5, CellType.String).SetCellValue(sum_SQBZBZ.ToString());
rowSFTotal.CreateCell(6, CellType.String).SetCellValue(sum_ENT_COUNT.ToString());
rowSFTotal.CreateCell(7, CellType.String).SetCellValue(sum_APP_NUM.ToString());
rowSFTotal.CreateCell(8, CellType.String).SetCellValue(sum_APP_MONEY.ToString());
rowSFTotal.CreateCell(9, CellType.String).SetCellValue(sum_APP_COUNT.ToString());
rowIndex++;
#endregion
#region 照汽车生产企业写入
var DealerList = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().ToList();
for (int j = 0; j < DealerList.Count(); j++)
{
//企业序号
int DealerClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).Select(d => d.Field <string>("CLXH")).Count();
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 1, 1));
IRow rowDealerTotal = sheet.CreateRow(rowIndex);
ICell cellDealerNum = rowDealerTotal.CreateCell(1);
cellDealerNum.SetCellValue(dealerNum);
//企业名称
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 2, 2));
ICell cellDealer = rowDealerTotal.CreateCell(2);
cellDealer.SetCellValue(DealerList[j]);
//企业小计
var dvDealer = dt.DefaultView;
dvDealer.RowFilter = String.Format("CLSCQY='{0}' AND DQ = '{1}'", DealerList[j], ProvinceList[i]);
var dtDealer = dvDealer.ToTable();
var dealer_ENT_NUM = dtDealer.Compute("sum(ENT_NUM)", "TRUE");
var dealer_SQBZBZ = dtDealer.Compute("sum(SQBZBZ)", "TRUE");
var dealer_ENT_COUNT = dtDealer.Compute("sum(ENT_COUNT)", "TRUE");
var dealer_APP_NUM = dtDealer.Compute("sum(APP_NUM)", "TRUE");
var dealer_APP_MONEY = dtDealer.Compute("sum(APP_MONEY)", "TRUE");
var dealer_APP_COUNT = dtDealer.Compute("sum(APP_COUNT)", "TRUE");
rowDealerTotal.CreateCell(3, CellType.String).SetCellValue("小计");
rowDealerTotal.CreateCell(4, CellType.String).SetCellValue(dealer_ENT_NUM.ToString());
rowDealerTotal.CreateCell(5, CellType.String).SetCellValue(dealer_SQBZBZ.ToString());
rowDealerTotal.CreateCell(6, CellType.String).SetCellValue(dealer_ENT_COUNT.ToString());
rowDealerTotal.CreateCell(7, CellType.String).SetCellValue(dealer_APP_NUM.ToString());
rowDealerTotal.CreateCell(8, CellType.String).SetCellValue(dealer_APP_MONEY.ToString());
rowDealerTotal.CreateCell(9, CellType.String).SetCellValue(dealer_APP_COUNT.ToString());
rowIndex++;
//按照车辆型号写入
var detailData = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).CopyToDataTable();
for (int k = 0; k < detailData.Rows.Count; k++)
{
IRow rowDealer = sheet.CreateRow(rowIndex);
rowDealer.CreateCell(3, CellType.String).SetCellValue(detailData.Rows[k]["CLXH"].ToString());
rowDealer.CreateCell(4, CellType.String).SetCellValue(detailData.Rows[k]["ENT_NUM"].ToString());
rowDealer.CreateCell(5, CellType.String).SetCellValue(detailData.Rows[k]["SQBZBZ"].ToString());
rowDealer.CreateCell(6, CellType.String).SetCellValue(detailData.Rows[k]["ENT_COUNT"].ToString());
rowDealer.CreateCell(7, CellType.String).SetCellValue(detailData.Rows[k]["APP_NUM"].ToString());
rowDealer.CreateCell(8, CellType.String).SetCellValue(detailData.Rows[k]["APP_MONEY"].ToString());
rowDealer.CreateCell(9, CellType.String).SetCellValue(detailData.Rows[k]["APP_COUNT"].ToString());
rowDealer.CreateCell(10, CellType.String).SetCellValue(detailData.Rows[k]["APP_RESULT"].ToString());
rowDealer.Cells[7].CellStyle = style_left;
rowIndex++;
}
dealerNum++;
}
#endregion
}
for (int colNum = 0; colNum <= 10; colNum++)
{
int columnWidth = sheet.GetColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++)
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(colNum);
if (currentCell != null)
{
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length + 1)
{
columnWidth = length + 1 > 254 ? 255 : length + 1;
}
currentRow.HeightInPoints = 20;
}
}
sheet.SetColumnWidth(colNum, columnWidth * 256);
}
sheet.ProtectSheet("1");
#endregion
#endregion
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
catch (Exception ex)
{
message = ex.Message;
}
return(message);
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
int dtcolunmnum = dt.Columns.Count;
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 13;
//hssfSheet.SetColumnWidth(0, 20 * 256);
//hssfSheet.SetColumnWidth(3, 20 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtcolunmnum - 1));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 30 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue(fileName);
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dtcolunmnum - 1));
// 标题样式
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#endregion
// 表数据
for (int k = 0; k < dt.Rows.Count; k++)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[i].ToString());
rowlast.GetCell(i).CellStyle = cellStyle;
}
}
// 表数据
//for (int k = 0; k < dt.Rows.Count; k++)
//{
// hssfSheet.AutoSizeColumn(k);
//}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
{
int columnWidth = hssfSheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= hssfSheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (hssfSheet.GetRow(rowNum) == null)
{
currentRow = hssfSheet.CreateRow(rowNum);
}
else
{
currentRow = hssfSheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
hssfSheet.SetColumnWidth(columnNum, columnWidth * 256);
}
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
//保存excel文件
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Report/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Report/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}
