private ExcelSheetContext[] SortByRange(ExcelSheetContext[] array)
{
for (int i = 0; i < array.Length; i++)
{
for (int j = i + 1; j < array.Length; j++)
{
var npoi0 = array[i];
var npoi1 = array[j];
int range0 = i, range1 = j;
if (npoi0 != null && npoi0.Column_Index > 0)
{
range0 = npoi0.Column_Index;
}
if (npoi1 != null && npoi1.Column_Index > 0)
{
range1 = npoi1.Column_Index;
}
ExcelSheetContext temp = array[i];
if (range1 < range0)
{
array[i] = array[j];
array[j] = temp;
}
}
}
return(array);
}
/// <summary>
/// 创建上下文对象
/// </summary>
/// <param name="columnName">数据库表列名</param>
/// <param name="column_title">列名</param>
/// <param name="column_index">表格列顺序</param>
/// <param name="ignore">是否忽略</param>
/// <param name="converter">值转换委托</param>
/// <returns></returns>
public static ExcelSheetContext GetContext(string columnName, string column_title, int column_index, bool ignore = false, Func <object, object> converter = null)
{
var context = new ExcelSheetContext
{
ColumnName = columnName,
Cell_Title = column_title,
Column_Index = column_index,
Ignore = ignore,
Converter = converter
};
return(context);
}
/// <summary>
/// 导出EXCEL到流
/// </summary>
/// <param name="stream"></param>
public override void Export(Stream stream)
{
var columns = SheetColumns();
if (columns != null)
{
columns = SortByRange(columns.ToArray());
}
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
ISheet sheet = hSSFWorkbook.CreateSheet(string.IsNullOrEmpty(_table.TableName) ? "sheet1" : _table.TableName);
IRow head_row = sheet.GetRow(0) ?? sheet.CreateRow(0);
head_row.HeightInPoints = 20;
var index = 0;
List <int> columnWidthlist = new List <int>();
//表头
if (columns == null)
{
columns = new ExcelSheetContext[_table.Columns.Count];
for (int i = 0; i < _table.Columns.Count; i++)
{
columns[i] = new ExcelSheetContext
{
ColumnName = _table.Columns[i].ColumnName,
Column_Index = i,
};
}
}
foreach (var item in columns)
{
string titlename = string.IsNullOrEmpty(item.Cell_Title) ? item.ColumnName : item.Cell_Title;
if (string.IsNullOrEmpty(titlename))
{
continue;
}
ICell cell = head_row.CreateCell(index);
cell.SetCellValue(titlename);
cell.CellStyle = GetTitleStyle(hSSFWorkbook);
index++;
}
//单元格
for (int j = 0; j < _table.Rows.Count; j++)
{
IRow row2 = sheet.GetRow(j + 1) ?? sheet.CreateRow(j + 1);
row2.HeightInPoints = 20;
for (int k = 0; k < columns.Length; k++)
{
var col = columns[k];
ICell cell2 = row2.CreateCell(k);
cell2.CellStyle = GetCellStyle(hSSFWorkbook);
int num = Encoding.Default.GetByteCount(_table.Rows[j][col.ColumnName].ToString()) > Encoding.Default.GetByteCount(col.ColumnName) ?
Encoding.Default.GetByteCount(_table.Rows[j][col.ColumnName].ToString()) :
Encoding.Default.GetByteCount(col.ColumnName);
sheet.SetColumnWidth(k, (num + 1) * 256);
object value = col.Convert(_table.Rows[j][col.ColumnName]);
if (value == null)
{
SetCellValue(cell2, string.Empty, typeof(string));
}
else
{
SetCellValue(cell2, value, value.GetType());
}
}
}
hSSFWorkbook.Write(stream);
//hSSFWorkbook.Dispose();
hSSFWorkbook.Close();
}