/// <summary>
/// Creates the excel workbook.
/// </summary>
/// <param name="subject">The subject.</param>
public void CreateWorkbook(string subject)
{
//Creating the excel workbook
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Creating summary information to the document
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Thoris";
//Applying summary information to the document
wb.DocumentSummaryInformation = dsi;
//Creating summary information for the data
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
//Applying summary information to the data
wb.SummaryInformation = si;
_workbook = wb;
//Creating the current styles
Font font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(font);
_headerStyle = CreateHeaderStyle(_workbook);
_dataStyle = CreateStyle(_workbook, false);
}
/// <summary>
/// 模板导入到NPOI Workbook中
/// </summary>
/// <param name="dataSourceFilePath">数据源路经</param>
/// <returns></returns>
private NPOI.HSSF.UserModel.HSSFWorkbook InitializeWorkbook(string dataSourceFilePath)
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = null;
System.IO.FileStream file = new System.IO.FileStream(dataSourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
if (null == file)
{
return(hssfworkbook);
}
hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
if (null == hssfworkbook)
{
return(hssfworkbook);
}
//create a entry of DocumentSummaryInformation
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "test";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = "test";
hssfworkbook.SummaryInformation = si;
return(hssfworkbook);
}
catch (Exception ex)
{
return(null);
throw new Exception(ex.ToString());
}
}
/// <summary>
/// 添加Excel文件属性信息
/// </summary>
/// <param name="book"></param>
private static void AddBookPropertyInfo(HSSFWorkbook book)
{
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "PM";
book.DocumentSummaryInformation = dsi;
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Author = "品茗造价"; //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "品茗造价"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "导出的Excel"; //填加xls文件标题信息
si.Subject = "数据导出"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
book.SummaryInformation = si;
}
public static void ExportToExcelFile(IList <Article> articles, string pFilePath)
{
IWorkbook workbook = null;
ISheet worksheet = null;
using (FileStream stream = new FileStream(pFilePath, FileMode.Create, FileAccess.ReadWrite))
{
string Ext = Path.GetExtension(pFilePath);
switch (Ext.ToLower())
{
case ".xls":
HSSFWorkbook workbookH = new HSSFWorkbook();
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
workbookH.DocumentSummaryInformation = dsi;
workbook = workbookH;
break;
case ".xlsx": workbook = new XSSFWorkbook(); break;
}
//Create new Excel Sheet
if (workbook != null)
{
var sheet = workbook.CreateSheet();
int rowNumber = 1;
//Populate the sheet with values from the articles
foreach (var objArticles in articles)
{
//Create a new Row
var row = sheet.CreateRow(rowNumber++);
//Set the Values for Cells
row.CreateCell(0).SetCellValue(objArticles.ProductName);
row.CreateCell(1).SetCellValue(objArticles.ProductPrice);
}
workbook.Write(stream);
stream.Close();
}
}
}
/// <summary>
/// Yield
/// </summary>
/// <param name="xlstpmname"></param>
/// <param name="xlsname"></param>
/// <param name="dtSrc"></param>
/// <param name="dtSum"></param>
public static void GridToExcelYield(string xlstpmname, string xlsname, DataTable dtSrc, DataTable dtSum)
{
FileStream file = new FileStream(xlstpmname, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//create a entry of DocumentSummaryInformation
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "SMEE";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = "Wafer Review System";
hssfworkbook.SummaryInformation = si;
ISheet sheet1 = hssfworkbook.GetSheet("yield");
for (int i = 0; i < dtSrc.Columns.Count; i++)
{
//ICell cell = sheet1.GetRow(0).CreateCell(i);
ICell cell = sheet1.GetRow(0).GetCell(i);
cell.SetCellType(CellType.String);
ICellStyle style = hssfworkbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style.FillPattern = FillPattern.NoFill;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = (short)FontColor.Red;
style.SetFont(font);
cell.CellStyle = style;
cell.SetCellValue(dtSrc.Columns[i].ColumnName);
//sheet.AutoSizeColumn(i);
}
for (int i = 0, cnt = dtSrc.Rows.Count; i < cnt; i++)
{
if (dtSrc.Rows[i][0].ToString() == "")
{
continue;
}
for (int j = 0; j < dtSrc.Columns.Count; j++)
{
ICell cell = sheet1.GetRow(i + 1).GetCell(j);
object val = dtSrc.Rows[i][j];
if (val is string)
{
cell.SetCellValue(val.ToString());
}
else
{
cell.SetCellValue(double.Parse(val.ToString()));
}
if ((i + 1) == cnt)
{
ICellStyle style = hssfworkbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style.FillPattern = FillPattern.NoFill;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 11;
font.FontName = cell.CellStyle.GetFont(hssfworkbook).FontName;
style.SetFont(font);
cell.CellStyle = style;
}
}
}
ISheet sheet2 = hssfworkbook.GetSheet("report");
//写入classify名称
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object val = dtSum.Rows[j - 1][0];
if (val.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(1);
cell.SetCellValue(val.ToString());
}
//缺陷个数
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object valv = dtSum.Rows[j - 1][0];
if (valv.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(2);
object val = dtSum.Rows[j - 1][1];
cell.SetCellValue(double.Parse(val.ToString()));
}
//被测的die总数
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object valv = dtSum.Rows[j - 1][0];
if (valv.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(6);
object val = dtSum.Rows[j - 1][2];
cell.SetCellValue(double.Parse(val.ToString()));
}
//所有缺陷的总和
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object valv = dtSum.Rows[j - 1][0];
if (valv.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(7);
object val = dtSum.Rows[j - 1][3];
cell.SetCellValue(double.Parse(val.ToString()));
}
if (dtSum.Rows.Count < 70)
{
for (int i = dtSum.Rows.Count; i < 70 - dtSum.Rows.Count; i++)
{
ICell cell2 = sheet2.GetRow(i).GetCell(3);
if (cell2 != null)
{
sheet2.GetRow(i).RemoveCell(cell2);
}
ICell cell3 = sheet2.GetRow(i).GetCell(4);
if (cell3 != null)
{
sheet2.GetRow(i).RemoveCell(cell3);
}
ICell cell4 = sheet2.GetRow(i).GetCell(5);
if (cell4 != null)
{
sheet2.GetRow(i).RemoveCell(cell4);
}
}
}
sheet2.ForceFormulaRecalculation = true;
//写入数据
FileStream file2 = new FileStream(xlsname, FileMode.Create);
hssfworkbook.Write(file2);
file2.Close();
//删除模板
try
{
file.Dispose();
File.Delete(xlstpmname);
}
catch { }
}
public static void GridToExcelPolat(string xlstpmname, string xlsname, DataTable dtSrc, string img)
{
FileStream file = new FileStream(xlstpmname, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//create a entry of DocumentSummaryInformation
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "SMEE";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = "Wafer Review System";
hssfworkbook.SummaryInformation = si;
ISheet sheet1 = hssfworkbook.GetSheet("Sheet1");
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, 0, 15, 8, 50);
anchor.AnchorType = AnchorType.MoveAndResize;
FileStream fileI = new FileStream(img, FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[fileI.Length];
fileI.Read(buffer, 0, (int)fileI.Length);
int hs = hssfworkbook.AddPicture(buffer, PictureType.JPEG);
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, hs);
picture.Resize();
picture.LineStyle = LineStyle.DotSys;
for (int i = 0; i < dtSrc.Columns.Count; i++)
{
//ICell cell = sheet1.GetRow(0).CreateCell(i);
ICell cell = sheet1.GetRow(0).GetCell(i);
cell.SetCellType(CellType.String);
ICellStyle style = hssfworkbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style.FillPattern = FillPattern.NoFill;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = (short)FontColor.Red;
style.SetFont(font);
cell.CellStyle = style;
cell.SetCellValue(dtSrc.Columns[i].ColumnName);
//sheet.AutoSizeColumn(i);
}
for (int i = 0, cnt = dtSrc.Rows.Count; i < cnt; i++)
{
if (dtSrc.Rows[i][0].ToString() == "")
{
continue;
}
for (int j = 0; j < dtSrc.Columns.Count; j++)
{
ICell cell = sheet1.GetRow(i + 1).GetCell(j);
object val = dtSrc.Rows[i][j];
if (val is string)
{
cell.SetCellValue(val.ToString());
}
else
{
cell.SetCellValue(double.Parse(val.ToString()));
}
}
}
sheet1.ForceFormulaRecalculation = true;
//写入数据
FileStream file2 = new FileStream(xlsname, FileMode.Create);
hssfworkbook.Write(file2);
file2.Close();
//删除模板
try
{
file.Dispose();
File.Delete(xlstpmname);
File.Delete(img);
}
catch { }
}
/// <summary>Convierte un DataTable en un archivo de Excel (xls o Xlsx) y lo guarda en disco.</summary>
/// <param name="pDatos">Datos de la Tabla a guardar. Usa el nombre de la tabla como nombre de la Hoja</param>
/// <param name="pFilePath">Ruta del archivo donde se guarda.</param>
public static void DataTable_To_Excel(DataTable pDatos, string pFilePath)
{
try
{
if (pDatos != null && pDatos.Rows.Count > 0)
{
IWorkbook workbook = null;
ISheet worksheet = null;
using (FileStream stream = new FileStream(pFilePath, FileMode.Create, FileAccess.ReadWrite))
{
string Ext = System.IO.Path.GetExtension(pFilePath); //<-Extension del archivo
switch (Ext.ToLower())
{
case ".xls":
HSSFWorkbook workbookH = new HSSFWorkbook();
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Cutcsa"; dsi.Manager = "Departamento Informatico";
workbookH.DocumentSummaryInformation = dsi;
workbook = workbookH;
break;
case ".xlsx": workbook = new XSSFWorkbook(); break;
}
worksheet = workbook.CreateSheet(pDatos.TableName); //<-Usa el nombre de la tabla como nombre de la Hoja
//CREAR EN LA PRIMERA FILA LOS TITULOS DE LAS COLUMNAS
int iRow = 0;
if (pDatos.Columns.Count > 0)
{
int iCol = 0;
IRow fila = worksheet.CreateRow(iRow);
foreach (DataColumn columna in pDatos.Columns)
{
ICell cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue(columna.ColumnName);
iCol++;
}
iRow++;
}
//FORMATOS PARA CIERTOS TIPOS DE DATOS
ICellStyle _doubleCellStyle = workbook.CreateCellStyle();
_doubleCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.###");
ICellStyle _intCellStyle = workbook.CreateCellStyle();
_intCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
ICellStyle _boolCellStyle = workbook.CreateCellStyle();
_boolCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("BOOLEAN");
ICellStyle _dateCellStyle = workbook.CreateCellStyle();
_dateCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");
ICellStyle _dateTimeCellStyle = workbook.CreateCellStyle();
_dateTimeCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy HH:mm:ss");
//AHORA CREAR UNA FILA POR CADA REGISTRO DE LA TABLA
foreach (DataRow row in pDatos.Rows)
{
IRow fila = worksheet.CreateRow(iRow);
int iCol = 0;
foreach (DataColumn column in pDatos.Columns)
{
ICell cell = null; //<-Representa la celda actual
object cellValue = row[iCol]; //<- El valor actual de la celda
switch (column.DataType.ToString())
{
case "System.Boolean":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Boolean);
if (Convert.ToBoolean(cellValue))
{
cell.SetCellFormula("TRUE()");
}
else
{
cell.SetCellFormula("FALSE()");
}
cell.CellStyle = _boolCellStyle;
}
break;
case "System.String":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue(Convert.ToString(cellValue));
}
break;
case "System.Int32":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt32(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Int64":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Decimal":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.Double":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.DateTime":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDateTime(cellValue));
//Si No tiene valor de Hora, usar formato dd-MM-yyyy
DateTime cDate = Convert.ToDateTime(cellValue);
if (cDate != null && cDate.Hour > 0)
{
cell.CellStyle = _dateTimeCellStyle;
}
else
{
cell.CellStyle = _dateCellStyle;
}
}
break;
default:
break;
}
iCol++;
}
iRow++;
}
workbook.Write(stream);
stream.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public new void FromDataTable(DataTable data)
{
try
{
if (data != null && data.Rows.Count > 0)
{
IWorkbook workbook = null;
ISheet worksheet = null;
using (FileStream stream = new FileStream(filepath, FileMode.Create, FileAccess.ReadWrite))
{
string Ext = Path.GetExtension(filepath);
switch (Ext.ToLower())
{
case ".xls":
HSSFWorkbook workbookH = new HSSFWorkbook();
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Pierotucci"; dsi.Manager = "Divisione Informatica";
workbookH.DocumentSummaryInformation = dsi;
workbook = workbookH;
break;
case ".xlsx": workbook = new XSSFWorkbook(); break;
}
worksheet = workbook.CreateSheet(data.TableName);
// columns titles
int iRow = 0;
if (data.Columns.Count > 0)
{
int iCol = 0;
IRow row = worksheet.CreateRow(iRow);
foreach (DataColumn columna in data.Columns)
{
ICell cell = row.CreateCell(iCol, CellType.String);
cell.SetCellValue(columna.ColumnName);
iCol++;
}
iRow++;
}
ICellStyle _doubleCellStyle = workbook.CreateCellStyle();
_doubleCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#0.###");
ICellStyle _intCellStyle = workbook.CreateCellStyle();
_intCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#0");
ICellStyle _boolCellStyle = workbook.CreateCellStyle();
_boolCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("BOOLEAN");
ICellStyle _dateCellStyle = workbook.CreateCellStyle();
_dateCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");
ICellStyle _dateTimeCellStyle = workbook.CreateCellStyle();
_dateTimeCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy HH:mm:ss");
foreach (DataRow row in data.Rows)
{
IRow line = worksheet.CreateRow(iRow);
int iCol = 0;
foreach (DataColumn column in data.Columns)
{
ICell cell = null;
object cellValue = row[iCol];
switch (column.DataType.ToString())
{
case "System.Boolean":
if (cellValue != DBNull.Value)
{
cell = line.CreateCell(iCol, CellType.Boolean);
if (Convert.ToBoolean(cellValue))
{
cell.SetCellFormula("TRUE()");
}
else
{
cell.SetCellFormula("FALSE()");
}
cell.CellStyle = _boolCellStyle;
}
break;
case "System.Int32":
if (cellValue != DBNull.Value)
{
cell = line.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt32(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Int64":
if (cellValue != DBNull.Value)
{
cell = line.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(cellValue));
cell.CellStyle = _intCellStyle;
}
break;
case "System.Decimal":
if (cellValue != DBNull.Value)
{
cell = line.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.Double":
if (cellValue != DBNull.Value)
{
cell = line.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
cell.CellStyle = _doubleCellStyle;
}
break;
case "System.DateTime":
if (cellValue != DBNull.Value)
{
cell = line.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDateTime(cellValue));
DateTime cDate = Convert.ToDateTime(cellValue);
if (cDate != null && cDate.Hour > 0)
{
cell.CellStyle = _dateTimeCellStyle;
}
else
{
cell.CellStyle = _dateCellStyle;
}
}
break;
default:
if (cellValue != DBNull.Value)
{
cell = line.CreateCell(iCol, CellType.String);
cell.SetCellValue(Convert.ToString(cellValue));
}
break;
}
iCol++;
}
iRow++;
}
workbook.Write(stream);
stream.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Load excel file and transfer data to data table
/// </summary>
/// <param name="strFilePath"></param>
/// <param name="strExcelSheetName"></param>
/// <param name="dtTable"></param>
/// <param name="intMaxRow"></param>
/// <returns></returns>
private string DataTable_To_Excel(string strFilePath, string strExcelSheetName, ref DataTable dtTable, int intMaxRow = 0)
{
try
{
dtTable = new DataTable();
IWorkbook workbook = null;
ISheet worksheet = null;
int i, j = 0;
using (FileStream stream = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
{
string strExtension = System.IO.Path.GetExtension(strFilePath); //Get extension
switch (strExtension.ToLower())
{
case ".xls":
HSSFWorkbook workbookH = new HSSFWorkbook(stream);
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Canon"; dsi.Manager = "PED";
workbookH.DocumentSummaryInformation = dsi;
workbook = workbookH;
break;
case ".xlsx":
workbook = new XSSFWorkbook(stream);
break;
}
worksheet = workbook.GetSheet(strExcelSheetName);
//Find all column has in worksheet include missing cell or blank cell
//Note that NPOI auto-remove missing cell! => we have to do manual
int NumCol = 0;
for (i = 0; i <= worksheet.LastRowNum; i++) //Looking through all row
{
int maxCol = 0;
int searchIndex = 0;
var row = worksheet.GetRow(i);
if (row != null)
{
searchIndex = 2 * row.Cells.Count; //Searching area is 2 times bigger than number of cell count in row
for (j = 0; j < searchIndex; j++)
{
try
{
ICell cell = row.GetCell(j, MissingCellPolicy.RETURN_NULL_AND_BLANK);
if (cell != null)
{
maxCol = j + 1;
}
}
catch (Exception ex)
{
var strTest = ex.Message;
}
}
//
if (NumCol < maxCol)
{
NumCol = maxCol;
}
}
}
//add neccessary columns
//if (dtTable.Columns.Count < worksheet.GetRow(0).Cells.Count)
if (dtTable.Columns.Count < NumCol)
{
//for (j = 0; j < worksheet.GetRow(0).Cells.Count; j++)
for (j = 0; j < NumCol; j++)
{
//dtTable.Columns.Add("", typeof(string));
dtTable.Columns.Add("", typeof(object));
}
}
//Loading all row to data table
for (i = 0; i <= worksheet.LastRowNum; i++)
{
//Check option to limit row loading
if ((intMaxRow > 0) && (i > intMaxRow - 1)) //No need load anymore
{
break;
}
//
if (worksheet.GetRow(i) != null)
{
object objTest = worksheet.GetRow(i);
// add row
dtTable.Rows.Add();
// write row value
//for (int j = 0; j < worksheet.GetRow(i).Cells.Count; j++)
for (j = 0; j < dtTable.Columns.Count; j++)
{
var cell = worksheet.GetRow(i).GetCell(j);
if (cell != null)
{
//Treat all data input as string type
if (cell.CellType == CellType.Formula) //Formular type
{
var test = cell.CachedFormulaResultType;
var test2 = "";
switch (test)
{
case CellType.String:
test2 = cell.StringCellValue;
break;
case CellType.Boolean:
test2 = cell.BooleanCellValue.ToString();
break;
case CellType.Numeric:
test2 = cell.NumericCellValue.ToString();
break;
default:
test2 = cell.ToString();
break;
}
dtTable.Rows[i][j] = test2;
}
else //Not formular
{
dtTable.Rows[i][j] = cell.ToString(); //Treat all data input as string type
}
}
else
{
dtTable.Rows[i][j] = "";
}
}
}
else
{
// add row
dtTable.Rows.Add();
// write row value
for (j = 0; j < dtTable.Columns.Count; j++)
{
dtTable.Rows[i][j] = "";
}
}
}
stream.Close();
}
//Return 0 if everything is OK
return("0");
}
catch (Exception ex)
{
return("Unexpected error happened. Error Message: " + ex.Message);
}
}
public ActionResult Generar(DateTime Desde, DateTime Hasta, Guid?Id_Parq)
{
try
{
Utilidades.Export export = new Utilidades.Export();
FileStream fs = new FileStream(Server.MapPath(@"~\Archivos\NPOI.xls"), FileMode.Open, FileAccess.Read);
Parqueadero parqueadero = db.Parqueaderoes.Find(Id_Parq);
// Getting the complete workbook...
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = parqueadero.NombreEmpresa_Parq; dsi.Manager = parqueadero.NombreEmpresa_Parq;
templateWorkbook.DocumentSummaryInformation = dsi;
// Getting the worksheet by its name...
HSSFSheet sheet = templateWorkbook.GetSheet("Hoja1") as HSSFSheet;
DataSet dts = new DataSet();
DataTable dtb = new DataTable();
dts = export.ConvertDataSet(reportes(Desde, Hasta, Id_Parq.Value).ToList(), dts);
if (dts.Tables.Count > 0)
{
dtb = dts.Tables[0];
}
int fila = 11, columna = 0, i = 0;
foreach (DataRow item in dtb.Rows)
{
HSSFRow dataRow = null;
IRow row = null;
dataRow = sheet.GetRow(fila) as HSSFRow;
if (dataRow == null)
{
row = sheet.CreateRow(fila);
dataRow = sheet.GetRow(fila) as HSSFRow;
if (dataRow == null)
{
throw new Exception("ha ocurrido un error al crear el archivo .xls");
}
}
//ArregloExcel(item);
foreach (var item1 in item.ItemArray)
{
if (i == 0)
{
if (row != null && row.Count() <= 27)
{
row.CreateCell(columna);
}
if (!string.IsNullOrEmpty(item1.ToString()))
{
dataRow.GetCell(columna).SetCellValue(string.Format(item1.ToString()));
}
else
{
dataRow.GetCell(columna).SetCellValue(string.Format("N/A"));
}
}
else
{
if (row != null && row.Count() <= 27)
{
row.CreateCell(columna);
}
if (!string.IsNullOrEmpty(item1.ToString()))
{
dataRow.GetCell(columna).SetCellValue(item1.ToString());
}
else
{
dataRow.GetCell(columna).SetCellValue(string.Format("N/A"));
}
}
// Setting the value 77 at row 5 column 1
columna++; i++;
}
fila++; columna = 0; i = 0;
}
//GraficaExcel(templateWorkbook, dtb);
MemoryStream ms = new MemoryStream();
string fechas = ("(" + Desde.ToString("yyyy-MM-dd") + "-" + Hasta.ToString("yyyy-MM-dd") + ")");
templateWorkbook.SetSheetName(templateWorkbook.GetSheetIndex(sheet), fechas);
// Writing the workbook content to the FileStream...
templateWorkbook.Write(ms);
// Sending the server processed data back to the user computer...
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Reportes" + fechas + ".xls")));
}
catch (Exception)
{
throw;
}
}
