public void Test49381()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
int colSplit = 1;
int rowSplit = 2;
int leftmostColumn = 3;
int topRow = 4;
ISheet s = wb.CreateSheet();
// Populate
for (int rn = 0; rn <= topRow; rn++)
{
IRow r = s.CreateRow(rn);
for (int cn = 0; cn < leftmostColumn; cn++)
{
ICell c = r.CreateCell(cn, CellType.Numeric);
c.SetCellValue(100 * rn + cn);
}
}
// Create the Freeze Pane
s.CreateFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
PaneInformation paneInfo = s.PaneInformation;
// Check it
Assert.AreEqual(colSplit, paneInfo.VerticalSplitPosition);
Assert.AreEqual(rowSplit, paneInfo.HorizontalSplitPosition);
Assert.AreEqual(leftmostColumn, paneInfo.VerticalSplitLeftColumn);
Assert.AreEqual(topRow, paneInfo.HorizontalSplitTopRow);
// Now a row only freezepane
s.CreateFreezePane(0, 3);
paneInfo = s.PaneInformation;
Assert.AreEqual(0, paneInfo.VerticalSplitPosition);
Assert.AreEqual(3, paneInfo.HorizontalSplitPosition);
Assert.AreEqual(0, paneInfo.VerticalSplitLeftColumn);
Assert.AreEqual(3, paneInfo.HorizontalSplitTopRow);
// Now a column only freezepane
s.CreateFreezePane(4, 0);
paneInfo = s.PaneInformation;
Assert.AreEqual(4, paneInfo.VerticalSplitPosition);
Assert.AreEqual(0, paneInfo.HorizontalSplitPosition);
Assert.AreEqual(4, paneInfo.VerticalSplitLeftColumn);
Assert.AreEqual(0, paneInfo.HorizontalSplitTopRow);
}
private void addTablaArchivosFirmados(ref int rowNumber, List <FileProcessing> data, ISheet sheet)
{
var row = sheet.CreateRow(rowNumber);
sheet.CreateFreezePane(0, rowNumber + 1);
var cell = row.CreateCell(0);
AddHeaderCell(row, 0, "Lote", widthInChars(25));
AddHeaderCell(row, 1, "Archivo", widthInChars(55));
AddHeaderCell(row, 2, "Estado", widthInChars(12));
AddHeaderCell(row, 3, "Descripcion", widthInChars(32));
AddHeaderCell(row, 4, "Path completo", widthInChars(92));
rowNumber++;
var firstRow = rowNumber;
bool alternate = false;
foreach (var pp in data)
{
row = sheet.CreateRow(rowNumber);
addRow(row, alternate, pp);
rowNumber++;
alternate = !alternate;
}
}
void ExportExecute()
{
rows = DataGrid.Rows.Where(x => x.Type == DataGridRowType.Group || x.Type == DataGridRowType.Item).ToList();
if (rows.Count == 0)
{
return;
}
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
sheet.CreateFreezePane(0, 2);
if (DataGrid.FlowDirection == FlowDirection.RightToLeft)
{
sheet.IsRightToLeft = true;
}
for (int i = 0; i <= DataGrid.GroupedColumns.Count(); i++)
{
sheet.SetColumnWidth(i, 1024);
}
PrepareStyle(workbook);
CreateExcelHeader(sheet);
worker = new BackgroundWorker();
worker.DoWork += CreateExcelItem;
worker.WorkerReportsProgress = true;
worker.WorkerSupportsCancellation = true;
worker.ProgressChanged += worker_ProgressChanged;
worker.RunWorkerAsync(sheet);
}
//Action action;
public void DatatableToExcel(DataTable table, string path)
{
if (table == null || string.IsNullOrEmpty(path))
{
MessageBox.Show(ReadConfigXml("alarm08"));
return;
}
IWorkbook workbook = null;
if (Path.GetExtension(path).ToLower() == ".xls")
{
workbook = new HSSFWorkbook();
}
else if (Path.GetExtension(path) == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else
{
MessageBox.Show(path);
}
ISheet sheet = workbook.CreateSheet("sheet1");
sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
sheet.CreateFreezePane(table.Columns.Count, 1);
IRow cells = sheet.CreateRow(0);
for (int i = 0; i < table.Columns.Count; i++)
{
sheet.SetColumnWidth(i, 20 * 256);
cells.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
}
for (int i = 0; i < table.Rows.Count; i++)
{
cells = sheet.CreateRow(i + 1);
for (int j = 0; j < table.Columns.Count; j++)
{
cells.CreateCell(j).SetCellValue(table.Rows[i][j].ToString().Trim());
}
}
FileStream fs;
try
{
// File.Delete(path);
fs = File.Create(path);
fs.Close();
fs = new FileStream(path, FileMode.Open, FileAccess.Write);
workbook.Write(fs);
//fs.Flush();
fs.Close();
workbook.Close();
//action = new Action(SimpleIoc.Default.GetInstance<EncodingViewModel>().SaveSuccess);
//action.Invoke();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public void DownLoadExcelTemplate()
{
string filename = "test.xlsx";
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//冻结第一行
sheet1.CreateFreezePane(0, 1, 0, 1);
//创建表头
//sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for (int i = 0; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
using (var f = System.IO.File.Create(@"d:\test.xlsx"))
{
workbook.Write(f);
}
Response.WriteFile(@"d:\test.xlsx");
Response.Flush();
Response.End();
}
private Collection <TotalsColumn> CreateHeader(ISheet worksheet, Type type, IList <PropertyInfo> properties, StylingOptions stylingOptions,
Dictionary <int, short> columnFormatters, Dictionary <string, ICellStyle> styleCache)
{
var headerRowColorId = GetXlColor(stylingOptions.HeaderRowColor);
var headerFontColorId = GetXlColor(stylingOptions.HeaderFontColor);
var headerCellStyle = GetCellStyle(0, headerFontColorId, stylingOptions.HeaderFontSize, styleCache, true, headerRowColorId, HorizontalAlignment.Center);
var headerRow = worksheet.CreateRow(0);
var totalsColumns = new Collection <TotalsColumn>();
for (var columnIndex = 0; columnIndex < properties.Count; columnIndex++)
{
var displayNameAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(DisplayNameAttribute)) as DisplayNameAttribute;
headerRow.CreateCell(columnIndex).SetCellValue(displayNameAttribute == null ? properties[columnIndex].Name : displayNameAttribute.DisplayName);
headerRow.Cells[columnIndex].CellStyle = headerCellStyle;
var formatAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(FormatAttribute)) as FormatAttribute;
var format = GetFormatId(formatAttribute);
columnFormatters.Add(columnIndex, format);
var totalsAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(FormulaAttribute)) as FormulaAttribute;
if (totalsAttribute != null)
{
totalsColumns.Add(new TotalsColumn
{
ColumnIndex = columnIndex,
Format = format,
TotalType = totalsAttribute.FormulaType
});
}
}
worksheet.CreateFreezePane(0, 1);
return(totalsColumns);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("new sheet");
ISheet sheet2 = workbook.CreateSheet("second sheet");
ISheet sheet3 = workbook.CreateSheet("third sheet");
ISheet sheet4 = workbook.CreateSheet("fourth sheet");
// Freeze just one row
sheet1.CreateFreezePane(0, 1, 0, 1);
// Freeze just one column
sheet2.CreateFreezePane(1, 0, 1, 0);
// Freeze the columns and rows (forget about scrolling position of the lower right quadrant).
sheet3.CreateFreezePane(2, 2);
// Create a split with the lower left side being the active quadrant
sheet4.CreateSplitPane(2000, 2000, 0, 0, PanePosition.LowerLeft);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// Export2s the excel.
/// </summary>
/// <param name="contentDics">The content dics.</param>
/// <param name="sheetColumnDics">The sheet column dics.</param>
/// <param name="filename">The filename.</param>
/// <returns></returns>
public static bool ExportToExcel(Dictionary <string, Dictionary <string, List <string> > > contentDics, Dictionary <string, List <string> > sheetColumnDics, string filename)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
foreach (KeyValuePair <string, Dictionary <string, List <string> > > keyValuePair in contentDics)
{
ISheet sheet = workbook.CreateSheet(keyValuePair.Key);
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
sheet.CreateFreezePane(0, 1, 0, 1);
IRow rowHead = sheet.CreateRow(0);
ICell cell = rowHead.CreateCell(0, CellType.String);
cell.SetCellValue("时间");
cell.CellStyle = style;
int y = 1;
for (int x = 0; x < sheetColumnDics[keyValuePair.Key].Count; x++)
{
cell = rowHead.CreateCell(x + 1, CellType.String);
cell.SetCellValue(sheetColumnDics[keyValuePair.Key][x]);
cell.CellStyle = style;
}
Dictionary <string, List <string> > data = keyValuePair.Value;
foreach (KeyValuePair <string, List <string> > subKeyValuePair in data)
{
IRow row = sheet.CreateRow(y++);
cell = row.CreateCell(0, CellType.String);
cell.SetCellValue(subKeyValuePair.Key);
cell.CellStyle = style;
for (int index = 0; index < subKeyValuePair.Value.Count; index++)
{
string p = subKeyValuePair.Value[index];
cell = row.CreateCell(index + 1, CellType.String);
cell.SetCellValue(p);
cell.CellStyle = style;
}
}
}
using (FileStream stream = File.Create(filename))
{
workbook.Write(stream);
stream.Close();
}
return(true);
}
catch
{
throw;
}
}
public byte[] WriteExcel(IEnumerable data, string[] columns)
{
MemoryStream output = new MemoryStream();
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.SetFont(headerFont);
headerStyle.Alignment = HorizontalAlignment.Center;
//(Optional) freeze the header row so it is not scrolled
sheet.CreateFreezePane(0, 1, 0, 1);
IEnumerator foo = data.GetEnumerator();
foo.MoveNext();
Type t = foo.Current.GetType();
IRow header = sheet.CreateRow(0);
PropertyInfo[] properties = t.GetProperties();
int colIndex = 0;
for (int i = 0; i < properties.Length; i++)
{
if (columns.Contains(properties[i].Name))
{
ICell cell = header.CreateCell(colIndex);
cell.CellStyle = headerStyle;
cell.SetCellValue(properties[i].Name);
colIndex++;
}
}
int rowIndex = 0;
foreach (object o in data)
{
colIndex = 0;
IRow row = sheet.CreateRow(rowIndex + 1);
for (int i = 0; i < properties.Length; i++)
{
if (columns.Contains(properties[i].Name))
{
row.CreateCell(colIndex).SetCellValue(properties[i].GetValue(o, null).ToString());
colIndex++;
}
}
rowIndex++;
}
workbook.Write(output);
return(output.ToArray());
}
private void SetUpHeader(ISheet sheet, ClassMapping classMapping)
{
var workbook = sheet.Workbook;
var row = sheet.GetRow(0) ?? sheet.CreateRow(0);
InitMappingColumns(classMapping);
CacheColumnIndices(classMapping);
//freeze header row
sheet.CreateFreezePane(0, 1);
//create header and column style for every mapped column
foreach (var mapping in classMapping.PropertyMappings)
{
var cellIndex = CellReference.ConvertColStringToIndex(mapping.Column);
var cell = row.GetCell(cellIndex) ?? row.CreateCell(cellIndex);
cell.SetCellType(CellType.String);
cell.SetCellValue(mapping.Header);
cell.CellStyle = GetStyle(DataStatus.Header, workbook);
//set default column style if not defined but available
var style = GetStyle(mapping.Status, workbook);
if (mapping.Status == DataStatus.None)
{
continue;
}
var existStyle = sheet.GetColumnStyle(cellIndex);
if (
existStyle != null &&
existStyle.FillForegroundColor == style.FillForegroundColor &&
existStyle.BorderTop == style.BorderTop &&
existStyle.TopBorderColor == style.TopBorderColor
)
{
continue;
}
//create new style
sheet.SetDefaultColumnStyle(cellIndex, style);
sheet.SetColumnHidden(cellIndex, false);
//set default width
sheet.SetColumnWidth(cellIndex, 256 * 15);
//hide if defined
if (mapping.Hidden)
{
sheet.SetColumnHidden(cellIndex, true);
}
}
//set up filter
var lastPropMap = classMapping.PropertyMappings.OrderBy(p => p.ColumnIndex).LastOrDefault();
if (lastPropMap != null)
{
sheet.SetAutoFilter(new CellRangeAddress(0, 1, 0, lastPropMap.ColumnIndex));
}
}
/// <summary>
/// 将List转换成excel
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="list">数据集合</param>
/// <param name="tableHeaders">表头 k--属性 v--属性显示名称</param>
/// <param name="sheet">sheet名称</param>
/// <returns>文件流</returns>
public static byte[] ExportDataListToExcel <T>(List <T> list, Dictionary <string, string> tableHeaders, string sheetPrefix) where T : class, new()
{
HSSFWorkbook hssWorkBook = new HSSFWorkbook(); // 创建工作薄
int sheetNums = (int)Math.Ceiling(list.Count / 50000.00);
for (int i = 0; i < sheetNums; i++)
{
ISheet sheet = hssWorkBook.CreateSheet(sheetPrefix + (i + 1)); // 创建sheet表
// 创建表头
IRow rowHead = sheet.CreateRow(0);
int rowId = 0;
foreach (KeyValuePair <string, string> kv in tableHeaders)
{
ICell cell = rowHead.CreateCell(rowId);
cell.SetCellValue(kv.Value);
// 设置样式 加粗居中
HSSFCellStyle cellStyle = (HSSFCellStyle)hssWorkBook.CreateCellStyle();
HSSFFont font = (HSSFFont)hssWorkBook.CreateFont();
font.Boldweight = 600;
cellStyle.SetFont(font);
cellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle = cellStyle;
rowId++;
}
rowId = 1;
// 创建数据行
PropertyInfo[] properties = typeof(T).GetProperties();
// 计算每个sheet中记录数
var pagedList = list.Skip(i * 50000).Take(50000).ToList();
foreach (T item in pagedList)
{
IRow rowBody = sheet.CreateRow(rowId);
int cellIndex = 0;
foreach (KeyValuePair <string, string> kv in tableHeaders)
{
foreach (PropertyInfo property in properties)
{
if (string.Equals(property.Name, kv.Key, StringComparison.CurrentCultureIgnoreCase))
{
ICell cell = rowBody.CreateCell(cellIndex);
FillCellValue(cell, property.GetValue(item, null));
break;
}
}
cellIndex++;
}
rowId++;
}
// 冻结首行
sheet.CreateFreezePane(0, 1);
}
MemoryStream file = new MemoryStream();
hssWorkBook.Write(file);
return(file.GetBuffer());
}
/// <summary>
/// 冻结窗口
/// </summary>
/// <param name="sheet">Sheet对象</param>
/// <param name="rowIndex">行,默认值0表示不冻结行</param>
/// <param name="colIndex">列,默认值0表示不冻结列</param>
public static void Freeze(ISheet sheet, int rowIndex = 0, int colIndex = 0)
{
rowIndex = rowIndex < 0 ? 0 : rowIndex;
colIndex = colIndex < 0 ? 0 : colIndex;
if (rowIndex > 0 || colIndex > 0)
{
sheet.CreateFreezePane(colIndex, rowIndex);
}
}
/// <summary>
/// 冻结行，FreezeRow为要冻结的行
/// </summary>
/// <param name="Sheetindex"></param>
public void SplitAndFreezePanes(int Sheetindex, int FreezeRow)
{
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex);
// Freeze just one row
sheet1.CreateFreezePane(0, FreezeRow);
WriteToFile();
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public bool ToExcel(DataTable table)
{
FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
IWorkbook workBook = new HSSFWorkbook();
this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
ISheet sheet = workBook.CreateSheet(this._sheetName);
//处理表格标题
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(this._title);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
row.Height = 500;
sheet.CreateFreezePane(2, 0, 5, 0);
ICellStyle cellStyle = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 17;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
row.Cells[0].CellStyle = cellStyle;
//处理表格列头
row = sheet.CreateRow(1);
for (int i = 0; i < table.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
//处理数据内容
for (int i = 0; i < table.Rows.Count; i++)
{
row = sheet.CreateRow(2 + i);
row.Height = 250 * 5;
for (int j = 0; j < table.Columns.Count; j++)
{
row.CreateCell(j).CellStyle.WrapText = true;
row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
sheet.SetColumnWidth(j, 256 * 8);
}
}
//写入数据流
workBook.Write(fs);
fs.Flush();
fs.Close();
return(true);
}
public void MakeSheet(IEnumerable <DataRow> rows)
{
ISheet sh1 = WBC.WB.CreateSheet(SheetName);
var row0 = sh1.CreateRow(0);
for (int i = 0; i < FieldsConfig.Count; i++)
{
var fc = FieldsConfig[i];
var cell = row0.CreateCell(i);
cell.CellStyle = WBC.CellStyleCations;
cell.SetCellValue(FieldsConfig[i].Caption);
sh1.SetColumnWidth(i, fc.WidthInChars * 256);
if (fc.CellStyle != null)
{
sh1.SetDefaultColumnStyle(i, fc.CellStyle);
}
}
int ct = 1;
foreach (var dr in rows)
{
var row = sh1.CreateRow(ct);
ct++;
for (int i = 0; i < FieldsConfig.Count; i++)
{
var fc = FieldsConfig[i];
object value = null;
if (fc.FieldHelper == null)
{
value = dr[fc.FieldName];
}
else
{
value = fc.FieldHelper.GetFieldValue(fc.FieldName, dr);
}
var cell = row.CreateCell(i);
SetCellValue(cell, fc, value);
}
WBC.RowsDone++;
WBC.OnProgress();
if (WBC.Cancel)
{
return;
}
}
sh1.CreateFreezePane(0, 1);
}
/// <summary>
/// 导出一个面签的数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="data"></param>
public void ExportSheet(ISheet sheet, Sheet data)
{
// 输出标题列
var head = sheet.CreateRow(0);
var headFont = CreateFont();
headFont.FontName = "宋体";
headFont.FontHeightInPoints = 11;
headFont.Boldweight = 600;
var headCellStyle = CreateCellStyle();
headCellStyle.CloneStyleFrom(defaultCellStyle);
headCellStyle.Alignment = HorizontalAlignment.Center;
headCellStyle.VerticalAlignment = VerticalAlignment.Center;
headCellStyle.SetFont(headFont);
var columns = data.Columns;
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
SetColumnDefaultStyle(sheet, i, column);
var cell = head.CreateCell(i);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = headCellStyle;
}
// 输出行
var rowNumber = 1;
var rowWriter = new ExcelRowWriter(columns);
foreach (var rowValues in data.Rows)
{
var row = sheet.CreateRow(rowNumber++);
rowWriter.Write(row, rowValues);
if (rowNumber == 100) // 采样到100时自适应大小
{
for (int i = 0; i < columns.Length; i++)
{
sheet.AutoSizeColumn(i);
sheet.SetColumnWidth(i, Math.Min(sheet.GetColumnWidth(i) + 1000, 65280));
}
}
}
// 自适应列宽，弥补小于100行的情况
for (int i = 0; rowNumber < 100 && i < columns.Length; i++)
{
sheet.AutoSizeColumn(i);
sheet.SetColumnWidth(i, Math.Min(sheet.GetColumnWidth(i) + 1000, 65280));
}
sheet.CreateFreezePane(0, 1);
}
private static IWorkbook CrearLibro <T>(IEnumerable <T> datos, TipoExportacion tipoExportacion, string grupo)
{
//Inicializamos el archivo excel
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("DatosExportados");
//Inmoviliza la cabecera del reporte
sheet.CreateFreezePane(0, 1, 0, 1);
workbook = AgregarDatos <T>(workbook, datos, tipoExportacion, grupo);
return(workbook);
}
protected override void Resize(int headerRows, int headerColumns, int rows, int columns)
{
_headerRows = headerRows;
_headerColumns = headerColumns;
_sheet.CreateFreezePane(headerColumns, headerRows);
for (int row = 0; row < headerRows; row++)
{
for (int column = 0; column < headerColumns; column++)
{
SetHeader(row, column, "", 1, 1);
}
}
}
public ISheet DataTableToSheet([NotNull] ISheet sheet, DataTable dataTable, int sheetIndex)
{
if (null == dataTable || dataTable.Rows.Count == 0 || dataTable.Columns.Count == 0 || _propertyColumnDictionary.Keys.Count == 0)
{
return(sheet);
}
var sheetSetting = sheetIndex >= 0 && sheetIndex < _sheetSettings.Count ? _sheetSettings[sheetIndex] : _sheetSettings[0];
if (sheetSetting.HeaderRowIndex >= 0)
{
var headerRow = sheet.CreateRow(sheetSetting.HeaderRowIndex);
for (var i = 0; i < dataTable.Columns.Count; i++)
{
var col = _propertyColumnDictionary.GetPropertySettingByPropertyName(dataTable.Columns[i].ColumnName);
if (null != col)
{
headerRow.CreateCell(col.ColumnIndex).SetCellValue(col.ColumnTitle);
}
}
}
for (var i = 0; i < dataTable.Rows.Count; i++)
{
var row = sheet.CreateRow(sheetSetting.StartRowIndex + i);
for (var j = 0; j < dataTable.Columns.Count; j++)
{
var col = _propertyColumnDictionary.GetPropertySettingByPropertyName(dataTable.Columns[j].ColumnName);
row.CreateCell(col.ColumnIndex).SetCellValue(dataTable.Rows[i][j], col.ColumnFormatter);
}
}
// autosizecolumn
foreach (var setting in _propertyColumnDictionary.Values)
{
sheet.AutoSizeColumn(setting.ColumnIndex);
}
foreach (var freezeSetting in _excelConfiguration.FreezeSettings)
{
sheet.CreateFreezePane(freezeSetting.ColSplit, freezeSetting.RowSplit, freezeSetting.LeftMostColumn, freezeSetting.TopRow);
}
if (_excelConfiguration.FilterSetting != null)
{
var headerIndex = sheetSetting.HeaderRowIndex >= 0 ? sheetSetting.HeaderRowIndex : 0;
sheet.SetAutoFilter(new CellRangeAddress(headerIndex, dataTable.Rows.Count + headerIndex, _excelConfiguration.FilterSetting.FirstColumn, _excelConfiguration.FilterSetting.LastColumn ?? _propertyColumnDictionary.Values.Max(_ => _.ColumnIndex)));
}
return(sheet);
}
/// <summary>
/// DataTable转换成Excel文档流
/// </summary>
/// <param name="table"></param>
/// <param name="clomunDic">列头字典，Key是列的索引，value是列名称</param>
/// <returns></returns>
public static MemoryStream RenderToExcel(DataTable table, Dictionary <string, string> headerDic)
{
MemoryStream ms = new MemoryStream();
using (table)
{
using (IWorkbook workbook = new HSSFWorkbook())
{
using (ISheet sheet = workbook.CreateSheet())
{
IRow headerRow = sheet.CreateRow(0);
int index = 0;
foreach (var item in headerDic)
{
headerRow.CreateCell(index).SetCellValue(item.Value);//If Caption not set, returns the ColumnName value
index++;
}
//第一个参数表示要冻结的列数；
//第二个参数表示要冻结的行数，这里只冻结列所以为0；
//第三个参数表示右边区域可见的首列序号，从1开始计算；
//第四个参数表示下边区域可见的首行序号，也是从1开始计算，这里是冻结列，所以为0；
//冻结首行
sheet.CreateFreezePane(0, 1, 0, 1);
// handling value.
int rowIndex = 1;
List <KeyValuePair <string, string> > list = headerDic.ToList();
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
if (headerDic.Keys.Contains(column.ColumnName))
{
int cloumnIndex = list.FindIndex(t => t.Key == column.ColumnName);
dataRow.CreateCell(cloumnIndex).SetCellValue(row[column.ColumnName].ToString());
}
}
rowIndex++;
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
}
}
return(ms);
}
/// <summary>
/// Export2s the excel.
/// </summary>
/// <param name="contentDic">The content dictionary.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="filename">The filename.</param>
/// <returns></returns>
public static bool ExportToExcel(Dictionary <string, string> contentDic, string columnName, string sheetName, string filename)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
sheet.CreateFreezePane(0, 1, 0, 1);
//填写表头
IRow rowHead = sheet.CreateRow(0);
ICell cell = rowHead.CreateCell(0, CellType.String);
cell.SetCellValue("时间");
cell.CellStyle = style;
cell = rowHead.CreateCell(1, CellType.String);
cell.SetCellValue(columnName);
cell.CellStyle = style;
//填写内容
int y = 1;
foreach (KeyValuePair <string, string> keyValuePair in contentDic)
{
IRow row = sheet.CreateRow(y++);
cell = row.CreateCell(0, CellType.String);
cell.SetCellValue(keyValuePair.Key);
cell.CellStyle = style;
cell = row.CreateCell(1, CellType.String);
cell.SetCellValue(keyValuePair.Value);
cell.CellStyle = style;
}
using (FileStream stream = File.Create(filename))
{
workbook.Write(stream);
stream.Close();
}
return(true);
}
catch
{
throw;
}
}
/// <summary>
/// datatable导出到excel，指定表头信息
/// </summary>
/// <param name="HeaderText">excel表头信息，如:{"编号","名称"}</param>
/// <param name="HeaderField">excel对应的DataTable中字段名称，如{"ID","Name"}</param>
/// <param name="SourceTable">DataTable数据源</param>
/// <returns></returns>
private static MemoryStream Export(string[] HeaderText, string[] HeaderField, DataTable SourceTable)
{
if (SourceTable == null)
{
return(null);
}
MemoryStream stream = new MemoryStream();
HSSFWorkbook workbook = new HSSFWorkbook();
string sheetName = String.IsNullOrEmpty(SourceTable.TableName) ? "Sheet1" : SourceTable.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
//固定表头
sheet.CreateFreezePane(0, 1);
//导出excel表头;
IRow hRow = sheet.CreateRow(0);
hRow.HeightInPoints = 25;
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//headStyle.FillBackgroundColor =See getFillForegroundColor();
for (int m = 0; m < HeaderText.Length; m++)
{
hRow.CreateCell(m).SetCellValue(HeaderText[m]);
hRow.GetCell(m).CellStyle = headStyle;
}
for (int i = 0; i < SourceTable.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < HeaderField.Length; j++)
{
row.CreateCell(j).SetCellValue(SourceTable.Rows[i][HeaderField[j]].ToString());
}
}
workbook.Write(stream);
sheet = null;
workbook = null;
stream.Flush();
stream.Position = 0L;
return(stream);
}
public static void DataSetToExcel(DataSet ds, string filename)
{
#region 将DATASET中文件，生成Excel代码
//导出Excel代码
//创建Workbook
IWorkbook wk = new HSSFWorkbook();
//创建Sheet
foreach (DataTable dtl in ds.Tables)
{
//设置Excel第一行名称
ISheet sheet = wk.CreateSheet(dtl.TableName);
IRow row0 = sheet.CreateRow(0);
for (int i = 0; i < dtl.Columns.Count; i++)
{
row0.CreateCell(i).SetCellValue(dtl.Columns[i].ColumnName);
}
int rowIndex = 1;
foreach (DataRow dtdr in dtl.Rows)
{
IRow row = sheet.CreateRow(rowIndex);
foreach (DataColumn dtdc in dtl.Columns)
{
for (int i = 0; i < dtl.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dtdr[i].ToString());
}
}
rowIndex++;
}
////第一行自动筛选
//CellRangeAddress c = new CellRangeAddress(1, 65535, 0,9);
//sheet.SetAutoFilter(c);
//冻结窗口
sheet.CreateFreezePane(0, 1);
// 把数据写入到磁盘上
using (FileStream fs = File.OpenWrite(filename + ".xls"))
{
wk.Write(fs);
}
}
//MessageBox.Show("PTN配置数据已成功导出至程序目录下：" + filename + "中，请查看。");
#endregion
}
private int MSWriteToSheet2(Dictionary <int, SelfNPOICell[]> data, ISheet sheet)
{
if (data == null || data.Count == 0)
{
return(-1);
}
ICellStyle backcolor = this.setCellBackColor();
ICellStyle fontBold = this.setFontBold();
ICellStyle wrapText = this.setWrapText();
foreach (var r in data)
{
IRow row = sheet.CreateRow(r.Key);
bool isSetBackColor = false;
if (r.Key > 0 && r.Value[0].HasBackColor)
{
isSetBackColor = true;
}
for (int col = 0; col < r.Value.Count(); col++)
{
ICell cell = row.CreateCell(col);
cell.SetCellValue(r.Value[col].CellValue);
if (r.Value[col].IsFontBold)
{
cell.CellStyle = fontBold;
}
if (isSetBackColor)
{
cell.CellStyle = backcolor;
}
else if (col == 4)
{
cell.CellStyle = setWrapText();
}
}
}
//设置合并单元格的长度，需全部显示文字
sheet.SetColumnWidth(0, 24 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(4, 60 * 256);
sheet.SetColumnWidth(8, 15 * 256);
sheet.CreateFreezePane(0, 1, 0, 1);
return(0);
}
private static void CreateHeader(HSSFWorkbook workbook, ISheet sheet, int rowId)
{
IRow rowHeader = sheet.CreateRow(rowId);
rowHeader.Height = 40 * 20;
ICellStyle style = CreateTableStyle(workbook, true, false);
CreateApplyStyleToCell(rowHeader, 0, style, "Название");
CreateApplyStyleToCell(rowHeader, 1, style, "Артикул");
CreateApplyStyleToCell(rowHeader, 2, style, "Кол-во");
CreateApplyStyleToCell(rowHeader, 3, style, "Зак. цена");
CreateApplyStyleToCell(rowHeader, 4, style, "Цена");
CreateApplyStyleToCell(rowHeader, 5, style, "Зак. сумма");
CreateApplyStyleToCell(rowHeader, 6, style, "Сумма");
sheet.CreateFreezePane(0, rowId + 1);
}
public void Export(Stream outStram, int year)
{
wb = new XSSFWorkbook();
exportStyleManager = new ExportStyleManager(wb);
this.employes = employes.OrderBy(e => e.Surname).ThenBy(e => e.Name).ToList();
ISheet sheet = CreateSheet(year);
int i = 1;
int mergeSize = 1;
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 30;
IRow dayNameRow = sheet.CreateRow(1);
IRow dayNumberRow = sheet.CreateRow(2);
List <IRow> employesLeavesRows = CreateEmployeLeavesRows(sheet);
for (int month = 1; month <= 12; month++)
{
DateTime calendar = new DateTime(year, month, 1);
int daysInMonth = DateTime.DaysInMonth(year, month);
CreateMonthHeader(year, sheet, headerRow, mergeSize, month, daysInMonth);
mergeSize += daysInMonth;
for (; i < mergeSize; i++)
{
int weekDay = (int)calendar.DayOfWeek;
sheet.SetColumnWidth(i, 4 * 256);
CreateDayColumn(calendar, dayNameRow, dayNumberRow, employesLeavesRows, weekDay, i, mergeSize);
calendar = calendar.AddDays(1);
}
i = mergeSize;
}
CreateTotalColumn(sheet, employesLeavesRows, dayNameRow, dayNumberRow, i);
sheet.CreateFreezePane(1, 3);
sheet.SetActiveCell(3, 0);
wb.Write(outStram);
wb.Close();
}
protected void FillComponentSheet(ISheet sheet, CODModel model, Database db)
{
BuildRow(sheet, HeadStyle, GetHeadValues());
sheet.CreateFreezePane(0, 1, 0, 1);
long line = 0;
var totalLines = model.Renderings.Count;
var d = new Dictionary <Guid, Item>();
foreach (var a in model.Renderings)
{
d.Add(a.Key, a.Value);
}
foreach (var b in model.Sublayouts)
{
d.Add(b.Key, b.Value);
}
foreach (KeyValuePair <Guid, Item> itm in d)
{
line++;
BuildRow(sheet, GreenStyle, GetTemplateValues($"{itm.Value.Name} ({itm.Value.TemplateName})", "Component"));
var rpItem = GetItemByFieldId(db, itm.Value, RenderingParameterField);
var dsItem = GetItemByFieldPath(db, itm.Value, DatasourceTemplateField);
if (dsItem == null && rpItem == null)
{
BuildRow(sheet, DefaultStyle, GetEmptyValues());
continue;
}
if (dsItem != null)
{
EnumerateTemplateFields(sheet, db, dsItem, "Datasource");
}
if (rpItem != null)
{
EnumerateTemplateFields(sheet, db, rpItem, "Rendering Parameters");
}
JobService.SetJobMessage($"Processed component item {line} of {totalLines}");
}
}
public void WriteExcel2003(string path, List <Dictionary <string, string> > data)
{
using (FileStream fs = new FileStream(path, FileMode.Create))
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
sheet.CreateFreezePane(0, 1);
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 15f;
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
IFont headerFont = workbook.CreateFont();
headerFont.IsBold = true;
headerFont.FontHeightInPoints = 12;
headerFont.FontName = "宋体";
headerStyle.SetFont(headerFont);
var dic = data.ElementAt(0);
for (int i = 0; i < dic.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.CellStyle = headerStyle;
cell.SetCellValue(dic.ElementAt(i).Key);
}
for (int i = 0; i < data.Count; i++)
{
var item = data.ElementAt(i);
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < item.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(item.ElementAt(j).Value);
}
}
workbook.Write(fs);
}
}
/// <summary>
/// 创建Sheet及列头
/// </summary>
private ISheet CreateSheetWithHeader(IWorkbook workbook, ColumnInfo[] cols, int sheetIndex)
{
ISheet sheet = workbook.CreateSheet("第 " + sheetIndex + " 页");
//冻结首行首列
sheet.CreateFreezePane(0, 1);
IRow header = sheet.CreateRow(0);
for (int i = 0; i < cols.Length; i++)
{
ICell cell = header.CreateCell(i);
cell.SetCellValue(cols[i].DisplayName);
//自适应宽度
sheet.AutoSizeColumn(i);
}
return(sheet);
}
private void BuildTitle()
{
var row = _sheet.CreateRow(0);
ICell cell;
row.HeightInPoints = TitleRowHight;
for (int i = 0; i < Columns.Count; i++)
{
cell = row.CreateCell(i, CellType.String);
cell.SetCellValue(Columns[i].Name);
cell.SetCellType(CellType.String);
}
if (FreezeTitle)
{
_sheet.CreateFreezePane(0, 1);
}
}
private static void CreateHeader(HSSFWorkbook workbook, ISheet sheet, int rowId)
{
IRow rowHeader = sheet.CreateRow(rowId);
rowHeader.Height = 40 * 20;
ICellStyle style = CreateTableStyle(workbook, true, false);
CreateApplyStyleToCell(rowHeader, 0, style, "Название");
CreateApplyStyleToCell(rowHeader, 1, style, "Артикул");
CreateApplyStyleToCell(rowHeader, 2, style, "Кол-во");
CreateApplyStyleToCell(rowHeader, 3, style, "Зак. цена");
CreateApplyStyleToCell(rowHeader, 4, style, "Цена");
CreateApplyStyleToCell(rowHeader, 5, style, "Зак. сумма");
CreateApplyStyleToCell(rowHeader, 6, style, "Сумма");
sheet.CreateFreezePane(0, rowId + 1);
}
private int MSWriteToSheet2(Dictionary<int, SelfNPOICell[]> data, ISheet sheet)
{
if (data == null || data.Count == 0)
return -1;
ICellStyle backcolor = this.setCellBackColor();
ICellStyle fontBold = this.setFontBold();
ICellStyle wrapText = this.setWrapText();
foreach (var r in data)
{
IRow row = sheet.CreateRow(r.Key);
bool isSetBackColor = false;
if (r.Key > 0 && r.Value[0].HasBackColor)
{
isSetBackColor = true;
}
for (int col = 0; col < r.Value.Count(); col++)
{
ICell cell = row.CreateCell(col);
cell.SetCellValue(r.Value[col].CellValue);
if (r.Value[col].IsFontBold)
{
cell.CellStyle = fontBold;
}
if (isSetBackColor)
{
cell.CellStyle = backcolor;
}
else if (col == 4)
{
cell.CellStyle = setWrapText();
}
}
}
//设置合并单元格的长度，需全部显示文字
sheet.SetColumnWidth(0, 24 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(4, 60 * 256);
sheet.SetColumnWidth(8, 15 * 256);
sheet.CreateFreezePane(0, 1, 0, 1);
return 0;
}
