public string GenerateCode(string masterName, IRow columns, IRow types)
{
var fields = new StringBuilder();
for (var i = 0; i < columns.Count(); i++)
{
var column = columns.GetCell(i).StringCellValue.ToTopUpper();
var type = types.GetCell(i).StringCellValue.ToLower();
fields.Append($" {MakeField(column, type)}{Environment.NewLine}");
}
var key = ConvertibleTypeUtility.GetString(types.GetCell(0).StringCellValue);
return(scriptableObjectCodeTemplate
.Replace("$Namespace$", @namespace)
.Replace("$Master$", masterName)
.Replace("$Key$", key)
.Replace("$Columns$", fields.ToString().TrimEnd(Environment.NewLine))
.Replace("$ResourcePath$", GetPathUnderResources()));
}
private void F_資料確認(ref DataTable D_table, ref DataTable D_errortable, string str頁簽名稱, IRow row, int i, IRow DateRow)
{
string StrError = "";
#region regex用法
//bool b工號Error = false;
//if (!string.IsNullOrEmpty(row.GetCell(z).ToString()))
//{
// str工號 = row.GetCell(z).ToString().Trim().ToUpper();
// Regex reg = new Regex(strRegex工號);
// b工號Error = (!reg.IsMatch(str工號) && str工號.Length != 5) ? true : false;
//}
//else
// b工號Error = true;
//Regex資料驗證規則
//string strRegex工號 = "V[0-9]{4}", strRegex工段 = "[0-9]{3}", strRegex數量 = "[0-9]{4}";
//string strRegex日期 = "\\b(?<year>\\d{4})(?<month>\\d{2})(?<day>\\d{2})\\b";
#endregion
DataRow D_dataRow = D_table.NewRow();
DataRow D_erroraRow = D_errortable.NewRow();
#region 基礎資料
//D_dataRow[0] = str頁簽名稱;
#endregion
try
{
//款號
#region 款號
string Str款號 = "";
bool bCheck = true;
if (row.GetCell(0).CellType == CellType.String || row.GetCell(0).CellType == CellType.Numeric)
{
Str款號 = row.GetCell(0).ToString();
}
else
{
StrError += "沒有款號";
}
#region 顏色
string StrColor = "";
if (row.GetCell(1).CellType == CellType.String)
{
StrColor = row.GetCell(1).ToString();
}
else
{
StrError += $"{(StrError.Length > 0 ? "," : "")}沒有顏色";
}
#endregion
#region Size
string StrSize = "";
if (row.GetCell(2).CellType == CellType.String)
{
StrSize = row.GetCell(2).ToString();
}
else
{
if ("#N/A" == row.GetCell(2).ToString())
{
bCheck = false;
}
StrError += $"{(StrError.Length > 0 ? "," : "")}沒有Size";
}
#endregion
if (bCheck)
{
for (int x = 3; x < row.Count(); x++)
{
int i數量 = 0;
if (row.GetCell(x).CellType == CellType.Numeric)
{
i數量 = (int)row.GetCell(x).NumericCellValue;
}
string[] StrArr顏色 = Regex.Split(StrColor, "[/]");
if (StrArr顏色.Length > 0)
{
foreach (var item in StrArr顏色)
{
if (i數量 > 0 && string.IsNullOrEmpty(StrError))
{
D_dataRow = D_table.NewRow();
D_dataRow[0] = Str款號;
D_dataRow[1] = DateRow.GetCell(x);
D_dataRow[2] = item;
D_dataRow[3] = StrSize;
D_dataRow[4] = i數量;
D_table.Rows.Add(D_dataRow);
}
}
}
}
}
//int I數量US = 0, I數量EU=0, I數量JP=0;
//if (row.GetCell(3).CellType == CellType.Numeric)
//{
// I數量US = (int)row.GetCell(3).NumericCellValue;
//}
//if(row.Cells.Count > 4)
// if (row.GetCell(4).CellType == CellType.Numeric)
// {
// I數量EU = (int)row.GetCell(4).NumericCellValue;
// }
//if(row.Cells.Count>5)
// if (row.GetCell(5).CellType == CellType.Numeric)
// {
// I數量JP = (int)row.GetCell(5).NumericCellValue;
// }
//if(I數量US==0&& I數量EU==0&& I數量JP==0)
//{
// StrError += $"{(StrError.Length > 0 ? "," : "")}沒有數量";
//}
#endregion
//NA資料不檢查
if (StrError.Length > 0 && bCheck)
{
D_erroraRow[0] = "Row " + i.ToString() + " " + StrError;
D_errortable.Rows.Add(D_erroraRow);
}
}
catch (Exception ex)
{
F_ErrorShow(ex.Message.ToString());
}
}
private void F_資料確認(ref DataTable D_table, ref DataTable D_errortable, ref DataTable DtCula, IRow row, int i, ref List <ColorClass> colorClass)
{
int iDDL顏色數量 = 0;
int.TryParse(匯入筆數DDL.SelectedValue, out iDDL顏色數量);
int IRowCount = row.Count();
string StrError = "";
//顏色數量
if (iDDL顏色數量 > 0)
{
if (i > 0)
{
for (int i顏色數量 = 0; i顏色數量 < iDDL顏色數量; i顏色數量++)
{
ColorClass CC = new ColorClass
{
ColorX = i,
ColorY = i顏色數量,
ColorName = row.GetCell(i顏色數量).ToString()
};
colorClass.Add(CC);
}
}
//增加欄位
if (i == 0)
{
for (int i欄位數量 = 0; i欄位數量 < IRowCount - 1; i欄位數量++)
{
if (i欄位數量 < iDDL顏色數量)
{
D_table.Columns.Add(row.GetCell(i欄位數量).ToString());
DtCula.Columns.Add("計算" + row.GetCell(i欄位數量).ToString());
}
else
{
DataColumn column;
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = row.GetCell(i欄位數量).ToString();
D_table.Columns.Add(column);
DataColumn column2;
column2 = new DataColumn();
column2.DataType = System.Type.GetType("System.Int32");
column2.ColumnName = "計算" + row.GetCell(i欄位數量).ToString();
DtCula.Columns.Add(column2);
}
}
}
else
{
DataRow D_dataRow = D_table.NewRow();
DataRow D_erroraRow = D_errortable.NewRow();
DataRow D_DtCulDataRow = DtCula.NewRow();
Boolean BError = false;
#region 基礎資料
try
{
int I公式件數總計 = (int)row.GetCell(IRowCount - 2).NumericCellValue;
int I數量增減 = (int)row.GetCell(IRowCount - 1).NumericCellValue;
int I件數總計確認 = 0;
int I件異動後總件數 = 0;
for (int j = 0; j < IRowCount; j++)
{
if (j < iDDL顏色數量)
{
D_dataRow[j] = row.GetCell(j).ToString().Trim();
D_DtCulDataRow[j] = row.GetCell(j).ToString().Trim();
ListColor.Add(row.GetCell(j).ToString().Trim());
}
else
{
int icount = 0;
try
{
switch (row.GetCell(j).CellType)
{
case CellType.Numeric:
case CellType.Formula:
if (j < IRowCount - 2)
{
I件數總計確認 += (int)row.GetCell(j).NumericCellValue;
if (j == IRowCount - 2 && I件數總計確認 != I公式件數總計)
{
BError = true;
StrError = "件數不同：件數加總" + I件數總計確認.ToString() + "，公式加總" + I公式件數總計.ToString();
}
icount = (I數量增減 != 0) ? (int)((float)row.GetCell(j).NumericCellValue + ((float)row.GetCell(j).NumericCellValue / I公式件數總計) * I數量增減) : (int)row.GetCell(j).NumericCellValue;
//D_dataRow[j + IRowCount - 2] = icount;
D_DtCulDataRow[j] = icount;
I件異動後總件數 += icount;
}
if (j == IRowCount - 2)
{
D_DtCulDataRow[IRowCount - 2] = I件異動後總件數;
}
D_dataRow[j] = (int)row.GetCell(j).NumericCellValue;
break;
case CellType.Error:
default:
D_dataRow[j] = 0;
break;
}
}
catch (Exception ex2)
{
F_ErrorShow(ex2.ToString());
}
}
}
D_table.Rows.Add(D_dataRow);
DtCula.Rows.Add(D_DtCulDataRow);
if (BError)
{
D_erroraRow[0] = "Row " + i.ToString() + " " + StrError;
D_errortable.Rows.Add(D_erroraRow);
}
}
catch (Exception ex)
{
F_ErrorShow(ex.ToString());
}
#endregion
}
}
#region old code
//string StrError = "";
//int IRowCount = row.Count();
//if (i==0)
//{
// for (int x = 0; x < IRowCount*2; x++)
// {
// if(x< IRowCount)
// {
// if(x<2)
// D_table.Columns.Add(row.GetCell(x).ToString());
// else
// {
// DataColumn column;
// column = new DataColumn();
// column.DataType = System.Type.GetType("System.Int32");
// column.ColumnName = row.GetCell(x).ToString();
// D_table.Columns.Add(column);
// }
// }
// else
// {
// if ( x<IRowCount * 2-1)
// if(x<IRowCount+2)
// {
// DtCula.Columns.Add("計算" + row.GetCell(x - IRowCount).ToString());
// }
// else
// {
// DataColumn column;
// column = new DataColumn();
// column.DataType = System.Type.GetType("System.Int32");
// column.ColumnName = "計算" + row.GetCell(x - IRowCount).ToString();
// DtCula.Columns.Add(column);
// }
// }
// }
//}
//else
//{
// DataRow D_dataRow = D_table.NewRow();
// DataRow D_erroraRow = D_errortable.NewRow();
// DataRow D_DtCulDataRow = DtCula.NewRow();
// Boolean BError = false;
// #region 基礎資料
// try
// {
// int I公式件數總計 = (int)row.GetCell(IRowCount-2).NumericCellValue;
// int I數量增減 = (int)row.GetCell(IRowCount - 1).NumericCellValue;
// int I件數總計確認 = 0;
// int I件異動後總件數 = 0;
// for (int j = 0; j < IRowCount; j++)
// {
// if(j<2)
// {
// D_dataRow[j] = row.GetCell(j).ToString().Trim();
// D_DtCulDataRow[j] = row.GetCell(j).ToString().Trim();
// ListColor.Add(row.GetCell(j).ToString().Trim());
// }
// else
// {
// int icount = 0;
// try
// {
// switch (row.GetCell(j).CellType)
// {
// case CellType.Numeric:
// case CellType.Formula:
// if (j < IRowCount - 2)
// {
// I件數總計確認 += (int)row.GetCell(j).NumericCellValue;
// if (j == IRowCount - 2 && I件數總計確認 != I公式件數總計)
// {
// BError = true;
// StrError = "件數不同：件數加總" + I件數總計確認.ToString() + "，公式加總" + I公式件數總計.ToString();
// }
// icount = (I數量增減 != 0) ? (int)((float)row.GetCell(j).NumericCellValue + ((float)row.GetCell(j).NumericCellValue / I公式件數總計) * I數量增減) : (int)row.GetCell(j).NumericCellValue;
// //D_dataRow[j + IRowCount - 2] = icount;
// D_DtCulDataRow[j] = icount;
// I件異動後總件數 += icount;
// }
// if (j == IRowCount - 2)
// D_DtCulDataRow[IRowCount - 2] = I件異動後總件數;
// D_dataRow[j] = (int)row.GetCell(j).NumericCellValue;
// break;
// case CellType.Error:
// default:
// D_dataRow[j] = 0;
// break;
// }
// }
// catch (Exception ex2)
// {
// F_ErrorShow(ex2.ToString());
// }
// }
// }
// D_table.Rows.Add(D_dataRow);
// DtCula.Rows.Add(D_DtCulDataRow);
// if (BError)
// {
// D_erroraRow[0] = "Row " + i.ToString() + " " + StrError;
// D_errortable.Rows.Add(D_erroraRow);
// }
// }
// catch (Exception ex)
// {
// F_ErrorShow(ex.ToString());
// }
// #endregion
//}
#endregion
}
public static void Draw(this IRow self, GUILayoutOption[] guiOpts)
{
self.Draw(0, self.Count(), guiOpts);
}
/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="firstRowColumn">第几行是DataTable的列名：从0开始</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, int firstRowColumn = 0)
{
//定义返回值
DataTable dt = new DataTable();
//定义WookBook
IWorkbook workbook = null;
//Sheet页
ISheet sheet = null;
//定义数据起始行
int startDataRow = 0;
try
{
//指定文件是否存在
if (!File.Exists(fileName))
{
return(null);
}
//根据文件流创建excel数据结构
using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(fileStream);
}
//获取指定名称的Sheet页，若果未指定名称，则获取第一个sheet页
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
//如果没有Sheet页，则返回
if (sheet == null)
{
return(null);
}
IRow firstRow = sheet.GetRow(firstRowColumn);
//添加标题
for (int i = firstRow.FirstCellNum; i < firstRow.LastCellNum; i++)
{
ICell cell = firstRow.GetCell(i);
if (cell == null)
{
continue;
}
var cellValue = cell.StringCellValue;
if (cellValue == null)
{
continue;
}
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(column);
}
startDataRow = firstRowColumn + 1;
//添加数据
for (int i = startDataRow; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null || row.Count() <= 0)
{
continue;
}
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < firstRow.LastCellNum; ++j)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
continue;
}
dataRow[j] = cell.ToString();
//if (DateTime.TryParse(cell.StringCellValue, out DateTime date))
//{
// dataRow[j] = date;
//}
//else
//{
// dataRow[j] = cell.ToString();
//}
//dataRow[j] = GetValueType(cell);
}
dt.Rows.Add(dataRow);
}
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public MemoryStream CreateTableXlsx(List <StaffViewModel> staffs, int countRecord = 0)
{
MemoryStream result = new MemoryStream();
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("MySheet");
//ICellStyle rowstyle = workbook.CreateCellStyle();
//rowstyle.FillForegroundColor = IndexedColors.Red.Index;
//rowstyle.FillPattern = FillPattern.SolidForeground;
IFont font1 = workbook.CreateFont();
// font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
// font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
ICellStyle style = workbook.CreateCellStyle();
style.BorderTop = BorderStyle.Thick;
style.TopBorderColor = 256;
style.BorderLeft = BorderStyle.Thick;
style.LeftBorderColor = 256;
style.BorderRight = BorderStyle.Thick;
style.RightBorderColor = 256;
style.BorderBottom = BorderStyle.Thick;
style.BottomBorderColor = 256;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.SetFont(font1);
IRow rowTop = sheet.CreateRow(1);
ICell с1 = rowTop.CreateCell(1);
с1.SetCellValue("Фамилия");
с1.CellStyle = style;
// с1.CellStyle = style1;
ICell с2 = rowTop.CreateCell(2);
с2.SetCellValue("Имя");
с2.CellStyle = style;
ICell с3 = rowTop.CreateCell(3);
с3.SetCellValue("Отчество");
с3.CellStyle = style;
ICell с4 = rowTop.CreateCell(4);
с4.SetCellValue("Должность");
с4.CellStyle = style;
ICell с5 = rowTop.CreateCell(5);
с5.SetCellValue("Звание");
с5.CellStyle = style;
ICell с6 = rowTop.CreateCell(6);
с6.SetCellValue("Подразделение");
с6.CellStyle = style;
ICell с7 = rowTop.CreateCell(7);
с7.SetCellValue("Уволен?");
с7.CellStyle = style;
var count = rowTop.Count() + 1;
for (int item = 1; item < count; item++)
{
sheet.SetColumnWidth(item, 23 * 256);
}
int i = 2;
if (staffs != null)
{
foreach (var item in staffs)
{
IRow row = sheet.CreateRow(i);
ICell surnameCellRow = row.CreateCell(1);
surnameCellRow.SetCellValue(item.Second);
ICell nameCellRow = row.CreateCell(2);
nameCellRow.SetCellValue(item.First);
ICell middleNameCellRow = row.CreateCell(3);
middleNameCellRow.SetCellValue(item.MiddleName);
ICell positionCellRow = row.CreateCell(4);
positionCellRow.SetCellValue(item.Position.Name);
ICell RankCellRow = row.CreateCell(5);
RankCellRow.SetCellValue(item.Rank.Name);
ICell SubDepartmentCellRow = row.CreateCell(6);
SubDepartmentCellRow.SetCellValue(item.SubDepartmen.Name);
ICell FiredCellRow = row.CreateCell(7);
FiredCellRow.SetCellValue(item.Fired == true ? "Уволен" : "Работает");
++i;
}
}
workbook.Write(result);
return(result);
}
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;
}
}
private void F_資料確認(ref DataTable D_table, ref DataTable D_errortable, ref DataTable DtCula, IRow row, int i)
{
string StrError = "";
int IRowCount = row.Count();
if (i == 0)
{
for (int x = 0; x < IRowCount * 2; x++)
{
if (x < IRowCount)
{
if (x < 2)
{
D_table.Columns.Add(row.GetCell(x).ToString());
}
else
{
DataColumn column;
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = row.GetCell(x).ToString();
D_table.Columns.Add(column);
}
}
else
{
if (x < IRowCount * 2 - 1)
{
if (x < IRowCount + 2)
{
DtCula.Columns.Add("計算" + row.GetCell(x - IRowCount).ToString());
}
else
{
DataColumn column;
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "計算" + row.GetCell(x - IRowCount).ToString();
DtCula.Columns.Add(column);
}
}
}
}
}
else
{
DataRow D_dataRow = D_table.NewRow();
DataRow D_erroraRow = D_errortable.NewRow();
DataRow D_DtCulDataRow = DtCula.NewRow();
Boolean BError = false;
#region 基礎資料
try
{
int I公式件數總計 = (int)row.GetCell(IRowCount - 2).NumericCellValue;
int I數量增減 = (int)row.GetCell(IRowCount - 1).NumericCellValue;
int I件數總計確認 = 0;
int I件異動後總件數 = 0;
for (int j = 0; j < IRowCount; j++)
{
if (j < 2)
{
D_dataRow[j] = row.GetCell(j).ToString().Trim();
D_DtCulDataRow[j] = row.GetCell(j).ToString().Trim();
ListColor.Add(row.GetCell(j).ToString().Trim());
}
else
{
int icount = 0;
try
{
switch (row.GetCell(j).CellType)
{
case CellType.Numeric:
case CellType.Formula:
if (j < IRowCount - 2)
{
I件數總計確認 += (int)row.GetCell(j).NumericCellValue;
if (j == IRowCount - 2 && I件數總計確認 != I公式件數總計)
{
BError = true;
StrError = "件數不同：件數加總" + I件數總計確認.ToString() + "，公式加總" + I公式件數總計.ToString();
}
icount = (I數量增減 != 0) ? (int)((float)row.GetCell(j).NumericCellValue + ((float)row.GetCell(j).NumericCellValue / I公式件數總計) * I數量增減) : (int)row.GetCell(j).NumericCellValue;
//D_dataRow[j + IRowCount - 2] = icount;
D_DtCulDataRow[j] = icount;
I件異動後總件數 += icount;
}
if (j == IRowCount - 2)
{
D_DtCulDataRow[IRowCount - 2] = I件異動後總件數;
}
D_dataRow[j] = (int)row.GetCell(j).NumericCellValue;
break;
case CellType.Error:
default:
D_dataRow[j] = 0;
break;
}
}
catch (Exception ex2)
{
F_ErrorShow(ex2.ToString());
}
}
}
D_table.Rows.Add(D_dataRow);
DtCula.Rows.Add(D_DtCulDataRow);
if (BError)
{
D_erroraRow[0] = "Row " + i.ToString() + " " + StrError;
D_errortable.Rows.Add(D_erroraRow);
}
}
catch (Exception ex)
{
F_ErrorShow(ex.ToString());
}
#endregion
}
}
