using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
namespace BackupAttach
{
public class Excel
{
private HSSFWorkbook _workBook;
private ISheet _wbSheet = null;
private DataColumnCollection _columns = null;
private int _col = 0; //total columns
private int _row = 0; //total rows
private int _sheet = 0; //total sheets
private int _sheetRowNum = 65536; //each sheet allow rows
public Excel()
{
InstanceWorkBook();
}
/// <summary>
/// Instance method
/// </summary>
/// <param name="sheetRowNum">Maximum number of rows allowed for a single form</param>
public Excel(int sheetRowNum)
{
_sheetRowNum = sheetRowNum;
InstanceWorkBook();
}
/// <summary>
/// Instance method
/// </summary>
/// <param name="columns">Table header</param>
public Excel(DataColumnCollection columns)
{
_columns = columns;
InstanceWorkBook();
}
private void InstanceWorkBook()
{
/////cretate WorkBook
_workBook = new HSSFWorkbook();
var dsi = ();
= "BaiyiTimes";
_workBook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
var si = ();
= "Etimes Secure Document System Log Backup";
_workBook.SummaryInformation = si;
}
private DataColumnCollection GetColumns(DataColumnCollection columns)
{
return columns == null || == 0 ? _columns : columns;
}
private ISheet GetSheet(ISheet sheet)
{
return sheet == null ? _wbSheet : sheet;
}
private void CreateHeader(ISheet sheet, DataColumnCollection columns)
{
_columns = GetColumns(columns);
/////create row of column
var oRow = (0);
foreach (DataColumn column in _columns)
{
var oCell = (_col);
var style1 = _workBook.CreateCellStyle();
= .index2;
= FillPatternType.SOLID_FOREGROUND;
= ;
= ;
var font = _workBook.CreateFont();
= ;
(font);
= style1;
var name = ;
(());
_col++;
}
///// header belong to rows
_row++;
}
private void CreateHeader(ISheet sheet)
{
CreateHeader(sheet, null);
}
public ISheet CreateSheet()
{
return CreateSheet(null);
}
public ISheet CreateSheet(DataColumnCollection columns)
{
_wbSheet = _workBook.CreateSheet((_sheet + 1).ToString());
CreateHeader(_wbSheet, columns);
_sheet++;
return _wbSheet;
}
public void SetRowValue(DataRowCollection rows, ISheet sheet)
{
_wbSheet = GetSheet(sheet);
foreach (DataRow row in rows)
{
SetRowValue(row);
}
}
public void SetRowValue(DataRowCollection rows)
{
SetRowValue(rows, null);
}
public void SetRowValue(DataRow row)
{
// create a new sheet
if (_row % _sheetRowNum == 0)
{
CreateSheet();
}
var oRow = _wbSheet.CreateRow(_row % _sheetRowNum);
var obj = ;
var cell = 0;
foreach (DataColumn column in _columns)
{
obj = row[].ToString();
(cell).SetCellValue(obj);
cell++;
}
_row++;
}
public void SetProtectPassword(string password, string username)
{
_workBook.WriteProtectWorkbook(password, username);
}
public void SaveAs(string filePath)
{
if ((filePath)) (filePath);
var file = new FileStream(filePath, );
_workBook.Write(file);
();
}
}
}