/// <summary>
/// Writes the values in an Excel sheet.
/// </summary>
/// <param name="target">Excel address, worksheet, range or null to create a new sheet.</param>
/// <param name="clearFirst">Optional - If true, clears the cells first</param>
public void ToExcel(object target = null, bool clearFirst = false)
{
IRange rg = ExcelExt.GetRange(target);
if (clearFirst)
{
rg[rg.SpecialCells(XlCellType.xlCellTypeLastCell).Row, 1].Clear();
}
if (_count == 0)
{
return;
}
var values = new object[_count, 2];
int i = 0;
for (DictionaryItem node = _head; node != null; node = node.next, i++)
{
values[i, 0] = node.key;
values[i, 1] = node.value;
}
rg[_count, 2].Value2 = values;
}
/// <summary>
/// Insert the image in an Excel sheet.
/// </summary>
/// <param name="target">Excel address, worksheet, range or null to create a new sheet.</param>
/// <param name="autoDispose">Release the image resources once done</param>
/// <returns>Range</returns>
public IRange ToExcel(object target = null, bool autoDispose = true)
{
IRange range = ExcelExt.GetRange(target);
Clipboard.SetDataObject(_bitmap, false);
range.Worksheet.Paste(range, _bitmap);
Clipboard.Clear();
if (autoDispose)
{
_bitmap.Dispose();
}
return(range);
}
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
{
try
{
NPOI.SS.UserModel.IWorkbook book = null;
if (excelExt == ExcelExt.Xls)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ColumnName);
index++;
}
// 添加数据
int num = dt.Rows.Count;
for (int i = 0; i < num; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
book.Write(outStream);
book = null;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
/// <summary>
/// Copies the values to Excel. The target can be an address, a worksheet or a range.
/// </summary>
/// <param name="target">Excel address, worksheet or range.</param>
/// <param name="clear">Optional - If true, the cells will be cleared first</param>
/// <example>
/// Dim lst As New List
/// lst.Add 43
/// lst.ToExcelRange Range("Sheet1!A1")
/// </example>
public void ToExcel(object target, bool clear = false)
{
IRange range = ExcelExt.GetRange(target);
if (clear)
{
range.CurrentRegion.Clear();
}
object[,] values = Data();
int rowlen = values.GetLength(0);
int collen = values.GetLength(1);
range[values, collen].Value2 = values;
}
/// <summary>
/// Copies the values to Excel. The target can be an address, a worksheet or a range.
/// </summary>
/// <param name="target">Optional - Excel address, worksheet or range. New sheet if null</param>
/// <param name="clear">Optional - If true, the cells will be cleared first</param>
/// <param name="firstRowsToSkip">First row(s) to skip. Ex : 2 will skip the first two rows</param>
/// <param name="lastRowsToSkip">Last row(s) to skip. Ex : 2 will skip the last two rows</param>
/// <param name="map">Optional - Javascript code to scrap each cell. Default: (e)=>e.textContent.trim()</param>
/// <example>
/// Dim lst As New List
/// lst.Add 43
/// lst.ToExcel [Sheet1!A1]
/// </example>
public void ToExcel(object target = null, bool clear = false
, int firstRowsToSkip = 0, int lastRowsToSkip = 0, string map = null)
{
IRange range = ExcelExt.GetRange(target);
if (clear)
{
range.CurrentRegion.Clear();
}
object[,] values = Data(firstRowsToSkip, lastRowsToSkip, map);
int rowlen = values.GetLength(0);
int collen = values.GetLength(1);
range[rowlen, collen].Value2 = values;
}
/// <summary>
/// Copies the values to Excel. The target can be an address, a worksheet or a range.
/// </summary>
/// <param name="target">Excel address, worksheet or range or null to create a new sheet</param>
/// <param name="clearFirst">Optional - If true, clears the cells first</param>
public void ToExcel(object target = null, bool clearFirst = false)
{
IRange range = ExcelExt.GetRange(target);
if (clearFirst)
{
var lastCell = range.SpecialCells(XlCellType.xlCellTypeLastCell);
range[lastCell.Row, lastCell.Column].Clear();
}
var values = this.Values();
var rlen = values.GetLength(0);
var clen = values.GetLength(1);
range[rlen, clen].Value2 = values;
}
public static IWorkbook GetWorkbook(ExcelExt ext, FileStream stream = null)
{
IWorkbook workbook = null;
switch (ext)
{
case ExcelExt.Xls:
workbook = stream == null ? new HSSFWorkbook() : new HSSFWorkbook(stream);
break;
case ExcelExt.Xlsx:
workbook = stream == null ? new XSSFWorkbook() : new XSSFWorkbook(stream);
break;
}
return(workbook);
}
public WorkerBase(string filePhysical, Dictionary <string, int> headerRowInfo)
{
if (File.Exists(filePhysical))
{
_filePhysical = filePhysical;
}
if (headerRowInfo != null && headerRowInfo.Count > 0)
{
_headerRowInfo = new Dictionary <string, int>();
foreach (KeyValuePair <string, int> item in headerRowInfo)
{
_headerRowInfo.Add(item.Key.ToLower(), item.Value);
}
}
_fileType = ExcelExt.Unknown;
}
/// <summary>
/// Copies the values to Excel. The target can be an address, a worksheet or a range.
/// </summary>
/// <param name="target">Excel address, worksheet, range or null to create a new sheet.</param>
/// <param name="title">Optional - Adds a title</param>
/// <param name="clearFirst">Optional - If true, clears the cells first</param>
/// <returns>Range</returns>
/// <example>
/// Dim lst As New List
/// lst.Add 43
/// lst.ToExcel [Sheet1!A1]
/// </example>
public IRange ToExcel(object target = null, string title = null, bool clearFirst = false)
{
IRange rg = ExcelExt.GetRange(target);
if (clearFirst)
{
rg[rg.SpecialCells(XlCellType.xlCellTypeLastCell).Row, 1].Clear();
}
if (_count == 0)
{
return(rg);
}
object[,] values;
if (title == null)
{
values = new object[_count, 1];
for (int i = 0; i < _count; i++)
{
values[i, 0] = _items[i];
}
}
else
{
_count++;
values = new object[_count, 1];
values[0, 0] = title;
for (int i = 1; i < _count; i++)
{
values[i, 0] = _items[i - 1];
}
}
rg = rg[_count, 1]; //Resize range
rg.Value2 = values;
return(rg);
}
public async Task <IActionResult> UploadFilePlan([Bind("ClientID,Fileupload")] UploadFilePlanVM ClientfilePlan)
{
string _urlBack = (Request.Headers["Referer"].ToString() == "" ? "Index" : Request.Headers["Referer"].ToString());
try
{
if (ClientfilePlan.Fileupload == null || ClientfilePlan.Fileupload.Length == 0)
{
flashMessage.Danger("File Not Selected");
throw new Exception();
}
var _client = ClientFindByID(Convert.ToInt32(ClientfilePlan.ClientID));
if (_client == null)
{
flashMessage.Danger("Client Not Found");
throw new Exception();
}
FileUploadExt uplExt = new FileUploadExt();
//FileStream Filestrm;
ExcelExt excelRead;
EnrollmentHdrModel enrollH = new EnrollmentHdrModel();
// upload file ke server
// Param 1 untuk file Plan excel
//// Copy File To Server
string FilePath;
EnrollPlanFileExcelDataVM EnrolPlan = new EnrollPlanFileExcelDataVM();
try
{
EnrolPlan.StringPathFileUpload = await uplExt.BackupFile(1, ClientfilePlan.Fileupload);
FilePath = EnrolPlan.StringPathFileUpload;
}
catch (Exception ex) { throw new Exception(ex.Message); }
try { excelRead = new ExcelExt(EnrolPlan.StringPathFileUpload, ClientfilePlan.ClientID); }
catch (Exception ex) { throw new Exception(ex.Message); }
//// Read Cell Value to VM
EnrolPlan.ClientID = ClientfilePlan.ClientID;
EnrolPlan.ClientCode = _client.ClientCode;
try { excelRead.ReadExcelEnrollPlan(ref EnrolPlan); }
catch (Exception ex) { throw new Exception("Error Read Excel : " + ex.Message); }
// Proses pembersihan data dan generate error Upload
ValidasiFileUploadPlan(ref EnrolPlan);
// Save To DB
try
{
enrollH.ClientID = ClientfilePlan.ClientID;
enrollH.FileUploadName = FilePath;
await SaveProductToDB(EnrolPlan, enrollH);
}
catch (Exception ex) { throw new Exception("Error Bulk Insert : " + ex.Message); }
flashMessage.Confirmation("Upload Success");
}
catch (Exception ex) { flashMessage.Danger(ex.Message); }
return(Redirect(_urlBack));
}
/// <summary>
/// Loads the data from the source. Can be an excel address or a range.
/// </summary>
/// <param name="source"></param>
/// <param name="hasHeaders"></param>
/// <returns><see cref="Table"/></returns>
public Table From(object source, bool hasHeaders = true)
{
try {
IRange range = ExcelExt.GetRange(source);
if (range.Count == 1)
{
IListObject listObject = range.ListObject;
if (listObject != null)
{
IExcel excel = range.Application;
range = excel[listObject.HeaderRowRange, listObject.DataBodyRange];
}
else
{
range = range.CurrentRegion;
}
}
var values = (object[, ])range.Value2 ?? new object[1, 1];
int clen = values.GetLength(1);
int rlen = values.GetLength(0);
_table = new System.Data.DataTable();
_columns = _table.Columns;
int irow = hasHeaders ? 2 : 1;
for (var icol = 0; icol++ < clen;)
{
Type coltype = GetColumnType(values, irow, icol);
object colvalue = hasHeaders ?
values[1, icol]
: (icol + 1);
string colname = colvalue == null ?
string.Empty
: colvalue.ToString();
_columns.Add(new System.Data.DataColumn(colname, coltype));
}
_rowsall = _table.Rows;
for (int r = hasHeaders ? 1 : 0; r++ < rlen;)
{
System.Data.DataRow row = _table.NewRow();
for (var c = 0; c < clen; c++)
{
row[c] = values[r, c + 1];
}
_rowsall.Add(row);
}
_hasheaders = hasHeaders;
_cells = (ICells)range;
return(this);
} catch (SeleniumException) {
throw;
} catch (Exception ex) {
throw new SeleniumException(ex);
}
}
private void ImportExcel_Stone(string path)
{
cancelTokenSrc = new CancellationTokenSource();
var token = cancelTokenSrc.Token;
var progressHandler = new Progress<int>(value => {
ProgressStone.Value = value;
});
var progress = progressHandler as IProgress<int>;
StoneDataGrid.Visibility = Visibility.Hidden;
StoneProgressGrid.Visibility = Visibility.Visible;
ImportStone.IsEnabled = false;
ExportStone.IsEnabled = false;
try {
ProgTextStone.Text = "Checking...";
ExcelExt exl = new ExcelExt();
exl.CancelTokenSource = cancelTokenSrc;
Task.Factory.StartNew(() => exl.StartExcelImport(path, token, ProgTextStone))
.ContinueWith((exc) => exl.ExtractStone(exc.Result, progress, token, ProgTextStone))
.ContinueWith((data) => StoneDataGrid.ItemsSource = StoneList = data.Result, TaskScheduler.FromCurrentSynchronizationContext())
.ContinueWith((ui) => ShowUIStone());
} catch (OperationCanceledException) {
ProgTextStone.Text = "Cancelled.";
} catch (Exception ex) {
ProgTextStone.Text = ex.GetType().Name + ": " + ex.Message;
}
}
private void ExportExcel_Stone(string path)
{
cancelTokenSrc = new CancellationTokenSource();
var token = cancelTokenSrc.Token;
var progressHandler = new Progress<int>(value => {
ProgressStone.Value = value;
});
var progress = progressHandler as IProgress<int>;
StoneDataGrid.Visibility = Visibility.Hidden;
StoneProgressGrid.Visibility = Visibility.Visible;
ImportStone.IsEnabled = false;
ExportStone.IsEnabled = false;
try {
ProgTextStone.Text = "Checking...";
ExcelExt exl = new ExcelExt();
exl.CancelTokenSource = cancelTokenSrc;
var _task = Task.Factory.StartNew(() => exl.StartExcelExport(path, token, ProgTextStone))
.ContinueWith((exc) => exl.CreateTemplate(progress, token, ProgTextStone, StoneList))
.ContinueWith((save) => exl.SaveExport(save.Result, "Stone"))
.ContinueWith((ui) => ShowUIStone());
} catch (OperationCanceledException) {
ProgTextStone.Text = "Cancelled.";
} catch (Exception ex) {
ProgTextStone.Text = ex.GetType().Name + ": " + ex.Message;
}
}
public ExcelTable(ExcelExt ext) : this()
{
_iWorkbook = ExcelHelper.GetWorkbook(ext);
}
