/// <summary>
/// 表头解析
/// </summary>
/// <remarks>
/// author:zhujt
/// create date:2015-9-10 19:24:51
/// </remarks>
/// <param name="header">表头</param>
/// <param name="rows">总行数</param>
/// <param name="addRows">外加行</param>
/// <param name="addCols">外加列</param>
/// <returns></returns>
private static IList <NPOIHeader> GetHeaders(string header, int rows, int addRows)
{
// 临时表头数组
string[] tempHeader;
string[] tempHeader2;
// 所跨列数
int colSpan = 0;
// 所跨行数
int rowSpan = 0;
// 单元格对象
NPOIHeader model = null;
// 行数计数器
int rowIndex = 0;
// 列数计数器
int colIndex = 0;
//
IList <NPOIHeader> list = new List <NPOIHeader>();
// 初步解析
string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);
// 表头遍历
for (int i = 0; i < headers.Length; i++)
{
// 行数计数器清零
rowIndex = 0;
// 列数计数器清零
colIndex = 0;
// 获取所跨行数
rowSpan = GetRowSpan(headers[i], rows);
// 获取所跨列数
colSpan = GetColSpan(headers[i]);
// 如果所跨行数与总行数相等，则不考虑是否合并单元格问题
if (rows == rowSpan)
{
colIndex = GetMaxCol(list);
model = new NPOIHeader(headers[i],
addRows,
(rowSpan - 1 + addRows),
colIndex,
(colSpan - 1 + colIndex),
addRows);
list.Add(model);
rowIndex += (rowSpan - 1) + addRows;
}
else
{
// 列索引
colIndex = GetMaxCol(list);
// 如果所跨行数不相等，则考虑是否包含多行
tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
for (int j = 0; j < tempHeader.Length; j++)
{
// 如果总行数=数组长度
if (1 == GetColSpan(tempHeader[j]))
{
if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
{
model = new NPOIHeader(tempHeader[j],
(j + addRows),
(j + addRows) + (rows - tempHeader.Length),
colIndex,
(colIndex + colSpan - 1),
addRows);
list.Add(model);
}
else
{
model = new NPOIHeader(tempHeader[j],
(j + addRows),
(j + addRows),
colIndex,
(colIndex + colSpan - 1),
addRows);
list.Add(model);
}
}
else
{
// 如果所跨列数不相等，则考虑是否包含多列
tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
for (int m = 0; m < tempHeader2.Length; m++)
{
// 列索引
colIndex = GetMaxCol(list) - colSpan + m;
if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
{
model = new NPOIHeader(tempHeader2[m],
(j + addRows),
(j + addRows) + (rows - tempHeader.Length),
colIndex,
colIndex,
addRows);
list.Add(model);
}
else
{
model = new NPOIHeader(tempHeader2[m],
(j + addRows),
(j + addRows),
colIndex,
colIndex,
addRows);
list.Add(model);
}
}
}
rowIndex += j + addRows;
}
}
}
return(list);
}
/// <summary>
/// 导出方法实现
/// </summary>
/// <param name="list"></param>
private static void Export(IList <NPOIModel> list)
{
#region 变量声明
// 初始化
_workbook = new HSSFWorkbook();
// 声明 Row 对象
IRow _row;
// 声明 Cell 对象
ICell _cell;
// 总列数
int cols = 0;
// 总行数
int rows = 0;
// 行数计数器
int rowIndex = 0;
// 单元格值
string drValue = null;
#endregion
foreach (NPOIModel model in list)
{
// 工作薄命名
if (model.sheetName != null)
{
_sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
}
else
{
_sheet = (HSSFSheet)_workbook.CreateSheet();
}
// 获取数据源
DataTable dt = model.dataSource;
// 初始化
rowIndex = 0;
// 获取总行数
rows = GetRowCount(model.headerName);
// 获取总列数
cols = GetColCount(model.headerName);
// 循环行数
foreach (DataRow row in dt.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
_sheet = (HSSFSheet)_workbook.CreateSheet();
}
// 构建行
for (int i = 0; i < rows + model.isTitle; i++)
{
_row = _sheet.GetRow(i);
// 创建行
if (_row == null)
{
_row = _sheet.CreateRow(i);
}
for (int j = 0; j < cols; j++)
{
_row.CreateCell(j).CellStyle = bodyStyle;
}
}
// 如果存在表标题
if (model.isTitle > 0)
{
// 获取行
_row = _sheet.GetRow(0);
// 合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
_sheet.AddMergedRegion(region);
// 填充值
_row.CreateCell(0).SetCellValue(model.tableTitle);
// 设置样式
_row.GetCell(0).CellStyle = titleStyle;
// 设置行高
_row.HeightInPoints = 20;
}
// 取得上一个实体
NPOIHeader lastRow = null;
IList <NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
// 创建表头
foreach (NPOIHeader m in hList)
{
var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
if (data.Count() > 0)
{
lastRow = data.First();
if (m.headerName == lastRow.headerName)
{
m.firstCol = lastRow.firstCol;
}
}
// 获取行
_row = _sheet.GetRow(m.firstRow);
// 合并单元格
CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);
_sheet.AddMergedRegion(region);
// 填充值
_row.CreateCell(m.firstCol).SetCellValue(m.headerName);
}
// 填充表头样式
for (int i = 0; i < rows + model.isTitle; i++)
{
_row = _sheet.GetRow(i);
for (int j = 0; j < cols; j++)
{
_row.GetCell(j).CellStyle = bodyStyle;
//设置列宽
_sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 256);
}
}
rowIndex = (rows + model.isTitle);
}
#endregion
#region 填充内容
// 构建列
_row = _sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
// 添加序号列
if (1 == model.isOrderby && column.Ordinal == 0)
{
_cell = _row.CreateCell(0);
_cell.SetCellValue(rowIndex - rows);
_cell.CellStyle = bodyStyle;
}
// 创建列
_cell = _row.CreateCell(column.Ordinal + model.isOrderby);
// 获取值
drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
_cell.SetCellValue(drValue);
_cell.CellStyle = bodyStyle;
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
_cell.SetCellValue(dateV);
_cell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
_cell.SetCellValue(boolV);
_cell.CellStyle = bodyStyle;
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
_cell.SetCellValue(intV);
_cell.CellStyle = bodyRightStyle;
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
_cell.SetCellValue(doubV);
_cell.CellStyle = bodyRightStyle;
break;
case "System.DBNull": //空值处理
_cell.SetCellValue("");
break;
default:
_cell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
}
}