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 } }