public List<StockDetail> ImportToXls(string fileName, ref string ErrMsg) { List<StockDetail> list = new List<StockDetail>(); try { using (var file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite)) { string strExt = System.IO.Path.GetExtension(fileName); IWorkbook wb;// = new XSSFWorkbook(file); if (strExt.Equals(".xls")) { wb = new HSSFWorkbook(file); } else wb = new XSSFWorkbook(file); ISheet sheet = wb.GetSheetAt(0); for (int i = 0; i <= sheet.LastRowNum; i++) { try { IRow row = sheet.GetRow(i); for (int j = 0; j < 1; j++) { ICell readCell = row.GetCell(j); StockDetail glass = new StockDetail(); glass.GlassID = readCell.ToString().ToUpper(); readCell.ToString();//这就是当前格子的值了 glass.Qty = 1; glass.AccountID = CurrentAccount.ID; glass.AccountName = CurrentAccount.Name; glass.Status = 0; glass.CreateDt = DateTime.Now; if (list.Any(p => p.GlassID.Equals(glass.GlassID))) { ErrMsg = "Excel中存在重复的GlassID"; return null; } list.Add(glass); } } catch { } } } } catch (Exception ex) { ErrMsg = ex.Message; } if (list.Count() <= 0) { ErrMsg = "当前不存在需要导入的GlassID,请检查Excel格式"; } return list; }
public IWorkbook GetIWorkbook(string filePath) { IWorkbook iworkbook = null; try { if (filePath.EndsWith(".xls")) { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { iworkbook = new HSSFWorkbook(file); } } else if (filePath.EndsWith(".xlsx")) { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { iworkbook = new XSSFWorkbook(file); } } } catch (Exception et) { throw et; } return iworkbook; }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); // Create a Worksheet ISheet ws = wb.CreateSheet("Sheet1"); // Aqua background ICellStyle style = wb.CreateCellStyle(); style.FillBackgroundColor = IndexedColors.Aqua.Index; style.FillPattern = FillPattern.BigSpots; IRow row = ws.CreateRow(0); ICell cell = row.CreateCell(1); cell.SetCellValue("X"); cell.CellStyle = style; // Orange "foreground", foreground being the fill foreground not the font color. style = wb.CreateCellStyle(); style.FillBackgroundColor = IndexedColors.Orange.Index; style.FillPattern = FillPattern.SolidForeground; cell = row.CreateCell(2); cell.SetCellValue("X"); cell.CellStyle = style; FileStream sw = File.Create("test.xlsx"); wb.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet s1 = workbook.CreateSheet("Sheet1"); ICellStyle rowstyle = workbook.CreateCellStyle(); rowstyle.FillForegroundColor = IndexedColors.Red.Index; rowstyle.FillPattern = FillPattern.SolidForeground; ICellStyle c1Style = workbook.CreateCellStyle(); c1Style.FillForegroundColor = IndexedColors.Yellow.Index; c1Style.FillPattern = FillPattern.SolidForeground; IRow r1 = s1.CreateRow(1); IRow r2= s1.CreateRow(2); r1.RowStyle = rowstyle; r2.RowStyle = rowstyle; ICell c1 = r2.CreateCell(2); c1.CellStyle = c1Style; c1.SetCellValue("Test"); ICell c4 = r2.CreateCell(4); c4.CellStyle = c1Style; using(var fs=File.Create("test.xlsx")) { workbook.Write(fs); } }
public void Create(Bilan bilan, string path) { var splitPath = path.Split('\\'); var pathFile = splitPath.Take(splitPath.Count() - 1).Aggregate((e, p) => e + "\\" + p) + "\\"; var fileNameOld = splitPath.Last(); var fileName = "synoptique_" + fileNameOld; using (var stream = new FileStream(pathFile + fileName, FileMode.Create, FileAccess.Write)) { var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet("synoptique"); var cadreArmoire = new CadreArmoire(sheet, workbook); cadreArmoire.Create(bilan.PageDeGarde.ChambrePMZ); var cadrePA = new CadrePA(sheet, workbook); cadrePA.Create(bilan.PageDeGarde.ChambrePointAboutement); var positionnementEtudeCreator = new PositionnementEtudeCreator(sheet, workbook); var hauteur = 9; var numeroCassette = 0; foreach (var positionnementEtude in bilan.PositionnementEtudes) { positionnementEtudeCreator.Create(positionnementEtude, ref hauteur, ref numeroCassette); } SetWidth(sheet); workbook.Write(stream); } }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("PictureSheet"); IDrawing patriarch = sheet1.CreateDrawingPatriarch(); //create the anchor XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7); anchor.AnchorType = 2; //load the picture and get the picture index in the workbook //first picture int imageId= LoadImage("../../image/HumpbackWhale.jpg", workbook); XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId); //Reset the image to the original size. //picture.Resize(); //Note: Resize will reset client anchor you set. picture.LineStyle = LineStyle.DashDotGel; //second picture int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook); XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15); XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2); picture.LineStyle = LineStyle.DashDotGel; FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("linechart"); // Create a row and put some cells in it. Rows are 0 based. IRow row; ICell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.CreateRow((short)rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.CreateCell((short)colIndex); cell.SetCellValue(colIndex * (rowIndex + 1)); } } IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15); CreateChart(drawing, sheet, anchor1, "title1","title2"); IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35); CreateChart(drawing, sheet, anchor2, "s1", "s2"); using (FileStream fs =File.Create("test.xlsx")) { wb.Write(fs); } }
public static IWorkbook WriteOutAndReadBack(IWorkbook wb) { IWorkbook result; try { using (MemoryStream baos = new MemoryStream(8192)) { wb.Write(baos); using (Stream is1 = new MemoryStream(baos.ToArray())) { if (wb is HSSFWorkbook) { result = new HSSFWorkbook(is1); } else if (wb is XSSFWorkbook) { result = new XSSFWorkbook(is1); } else { throw new RuntimeException("Unexpected workbook type (" + wb.GetType().Name + ")"); } } } } catch (IOException e) { throw new RuntimeException(e); } return result; }
public void TestXSSFSetArrayFormula_multiCell() { ICellRange<ICell> cells; String formula2 = "456"; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(); CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6"); cells = sheet.SetArrayFormula(formula2, range); Assert.AreEqual(3, cells.Size); // sheet.SetArrayFormula Creates rows and cells for the designated range /* * From the spec: * For a multi-cell formula, the c elements for all cells except the top-left * cell in that range shall not have an f element; */ // Check that each cell exists and that the formula text is Set correctly on the first cell XSSFCell firstCell = (XSSFCell)cells.TopLeftCell; ConfirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6"); ConfirmArrayFormulaCell(cells.GetCell(1, 0), "C5"); ConfirmArrayFormulaCell(cells.GetCell(2, 0), "C6"); Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell)); }
public void TestCustomProperties() { POIXMLDocument wb = new XSSFWorkbook(); CustomProperties customProps = wb.GetProperties().CustomProperties; customProps.AddProperty("test-1", "string val"); customProps.AddProperty("test-2", 1974); customProps.AddProperty("test-3", 36.6); //Adding a duplicate try { customProps.AddProperty("test-3", 36.6); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.AreEqual("A property with this name already exists in the custom properties", e.Message); } customProps.AddProperty("test-4", true); wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack((XSSFWorkbook)wb); CT_CustomProperties ctProps = wb.GetProperties().CustomProperties.GetUnderlyingProperties(); Assert.AreEqual(6, ctProps.sizeOfPropertyArray()); CT_Property p; p = ctProps.GetPropertyArray(0); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-1", p.name); Assert.AreEqual("string val", p.Item.ToString()); Assert.AreEqual(2, p.pid); p = ctProps.GetPropertyArray(1); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-2", p.name); Assert.AreEqual(1974, p.Item); Assert.AreEqual(3, p.pid); p = ctProps.GetPropertyArray(2); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-3", p.name); Assert.AreEqual(36.6, p.Item); Assert.AreEqual(4, p.pid); p = ctProps.GetPropertyArray(3); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-4", p.name); Assert.AreEqual(true, p.Item); Assert.AreEqual(5, p.pid); p = ctProps.GetPropertyArray(4); Assert.AreEqual("Generator", p.name); Assert.AreEqual("NPOI", p.Item); Assert.AreEqual(6, p.pid); //p = ctProps.GetPropertyArray(5); //Assert.AreEqual("Generator Version", p.name); //Assert.AreEqual("2.0.9", p.Item); //Assert.AreEqual(7, p.pid); }
static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); int x = 1; for (int i = 0; i < 15; i++) { IRow row = sheet1.CreateRow(i); for (int j = 0; j < 15; j++) { ICell cell = row.CreateCell(j); if (x % 2 == 0) { //fill background with blue ICellStyle style1 = workbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; cell.CellStyle = style1; } else { //fill background with yellow ICellStyle style1 = workbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; cell.CellStyle = style1; } x++; } } FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
/// <summary> /// use me for 2003 and 2007 /// </summary> /// <param name="fileName"></param> /// <returns></returns> public DataTable ReadExcelAsTable(string fileName) { try { using (FileStream fs = new FileStream(fileName, FileMode.Open)) { //chk xls SS.UserModel.ISheet sheet; if (fileName.EndsWith("xlsx")) { XSSF.UserModel.XSSFWorkbook wb = new XSSF.UserModel.XSSFWorkbook(fs); //for 2007 sheet = wb.GetSheetAt(0); //read first sheet } else { SS.UserModel.IWorkbook wb = new HSSF.UserModel.HSSFWorkbook(fs); //for 2003 sheet = wb.GetSheetAt(0); //read first sheet } return(ReadSheet(sheet)); } } catch (Exception ex) { throw ex; } }
public static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet s1=wb.CreateSheet("Monthly Salary Report"); IRow headerRow = s1.CreateRow(0); headerRow.CreateCell(0).SetCellValue("First Name"); s1.SetColumnWidth(0, 20 * 256); headerRow.CreateCell(1).SetCellValue("Last Name"); s1.SetColumnWidth(1, 20 * 256); headerRow.CreateCell(2).SetCellValue("Salary"); headerRow.CreateCell(3).SetCellValue("Tax Rate"); headerRow.CreateCell(4).SetCellValue("Tax"); headerRow.CreateCell(5).SetCellValue("Delivery"); int row = 1; GenerateRow(s1, row++, "Bill", "Zhang", 5000, 9.0/100); GenerateRow(s1, row++, "Amy", "Huang", 8000, 11.0/100); GenerateRow(s1, row++, "Tomos", "Johnson", 6000, 9.0/100); GenerateRow(s1, row++, "Macro", "Jeep", 12000, 15.0/100); s1.ForceFormulaRecalculation = false; FileStream fs = File.Create("test.xlsx"); wb.Write(fs); fs.Close(); }
public CadrePA(ISheet sheet, XSSFWorkbook workbook) { _sheet = sheet; _workbook = workbook; _lineCreator = new DataLineStyle(workbook, sheet); _cadreCreator = new Cadre(workbook, sheet); }
public void TestValidXSSF() { XSSFWorkbook wb = new XSSFWorkbook(); Evaluator = new XSSFFormulaEvaluator(wb); Confirm(wb); }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet1 = wb.CreateSheet("First Sheet"); ISheet sheet2 = wb.CreateSheet("Second Sheet"); // Note that sheet name is Excel must not exceed 31 characters // and must not contain any of the any of the following characters: // 0x0000 // 0x0003 // colon (:) // backslash (\) // asterisk (*) // question mark (?) // forward slash (/) // opening square bracket ([) // closing square bracket (]) // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)} // for a safe way to create valid names, this utility replaces invalid characters with a space (' ') String safeName = WorkbookUtil.CreateSafeSheetName("[O'Brien's sales*?]"); ISheet sheet3 = wb.CreateSheet(safeName); FileStream sw = File.Create("newWorksheet.xls"); wb.Write(sw); sw.Close(); }
public ExcelParser(string fileName) { FileStream fs = new FileStream(fileName, FileMode.Open); template = new XSSFWorkbook(fs); TestCases = new List<TestCase>(); }
public void TestExcel() { POIXMLDocument doc = new XSSFWorkbook( POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("ExcelWithAttachments.xlsm") ); Test(doc, 4); }
public static int[][][] ReadExcelPattern(string filepath) { using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read)) { IWorkbook workbook = new XSSFWorkbook(fs); int sheetnumber = workbook.NumberOfSheets; int [][][] sheetelements = new int[sheetnumber][][]; for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){ ISheet _isheet = workbook.GetSheetAt(sheetindex); int lastrownum = _isheet.LastRowNum; int [][] rowelements = new int[lastrownum+1][]; for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){ IRow row = _isheet.GetRow(rowindex); if(row == null)continue; int lastcellnum = row.LastCellNum; int[] cellelements = new int[lastcellnum+1]; for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){ ICell cell = row.GetCell(cellindex); if(cell != null){ cellelements[cellindex] = Convert.ToInt32(cell.ToString()); Debug.Log(cellelements[cellindex]); } } rowelements[rowindex] = cellelements; } sheetelements[sheetindex] = rowelements; } return sheetelements; } }
static void Main(string[] args) { IWorkbook wb = new XSSFWorkbook(); // Create a Worksheet ISheet ws = wb.CreateSheet("Sheet1"); ICellStyle style = wb.CreateCellStyle(); //Setting the line of the top border 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; IRow row = ws.CreateRow(0); ICell cell = row.CreateCell(1); cell.CellStyle = style; FileStream sw = File.Create("test.xlsx"); wb.Write(sw); sw.Close(); }
public Dictionary<DateTime, List<SaleParsed>> ImportFromSheet(string path, DateTime dateTime, IEnumerable<int> sheetNumbers, IEnumerable<GoodType> goodTypes, string producer) { this.goodTypes = goodTypes; this.producer = producer; var result = new Dictionary<DateTime, List<SaleParsed>>(); using (var fs = File.OpenRead(path)) { var workbook = new XSSFWorkbook(fs); foreach (var sNum in sheetNumbers) { ISheet sheet = workbook.GetSheetAt(sNum); var sales = ParseSales(sheet, dateTime); foreach (var s in sales) { if (result.ContainsKey(s.Key)) { result[s.Key].AddRange(s.Value); } else { result.Add(s.Key, s.Value); } } } } return result; }
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test"); sheet.SetColumnWidth(4, 5000); sheet.SetColumnWidth(5, 5000); sheet.GroupColumn((short)4, (short)5); sheet.SetColumnGroupCollapsed(4, true); CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0); //logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing"); sheet = (XSSFSheet)wb.GetSheet("test"); for (int i = 4; i <= 5; i++) { Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i); } cols = sheet.GetCTWorksheet().GetColsArray(0); foreach (CT_Col col in cols.GetColArray()) { Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString()); } }
public void TestBug48936() { IWorkbook w = new XSSFWorkbook(); ISheet s = w.CreateSheet(); int i = 0; List<String> lst = ReadStrings("48936-strings.txt"); foreach (String str in lst) { s.CreateRow(i++).CreateCell(0).SetCellValue(str); } try { w = XSSFTestDataSamples.WriteOutAndReadBack(w); } catch (POIXMLException) { Assert.Fail("Detected Bug #48936"); } s = w.GetSheetAt(0); i = 0; foreach (String str in lst) { String val = s.GetRow(i++).GetCell(0).StringCellValue; Assert.AreEqual(str, val); } }
public string Read(string inFile) { var stream = File.OpenRead(inFile); var book = new XSSFWorkbook(stream); stream.Close(); var sb = new StringBuilder(); var sheet = book.GetSheetAt(0); int lastRowNum = sheet.LastRowNum; for (int r = 0; r <= lastRowNum; r++) { var datarow = sheet.GetRow(r); { foreach (var cell in datarow.Cells) { switch (cell.CellType) { case CellType.Numeric: sb.Append(cell.NumericCellValue.ToString() + "\t"); break; case CellType.String: sb.Append(cell.StringCellValue.Replace("\n", "") + "\t"); break; default: throw new Exception("?"); } } sb.Append("\r\n"); } } return sb.ToString(); }
public static bool UpdateExcelData(string excelFilePath, string excelName) { XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open)); var sheet = excelBook.GetSheetAt(0); var headerRow = sheet.GetRow(0); //total columns int cellCount = headerRow.LastCellNum; //total rows int rowCount = sheet.LastRowNum; switch (excelName) { case Constant.Water: UpdateWaterData(sheet, cellCount, rowCount); break; case Constant.Air: UpdateAirData(sheet, cellCount, rowCount); break; } try { FileStream writefile = new FileStream("d:\\" + excelName + ".xlsx", FileMode.Create, FileAccess.Write); excelBook.Write(writefile); writefile.Close(); return true; } catch { return false; } }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> /// <param name="tb"></param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable tb) { tb = tb ?? throw new Exception($"无法把空{nameof(DataTable)}转成Excel"); using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet"); var style = GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index); for (int i = 0; i < tb.Rows.Count; ++i) { var row = sheet.CreateRow(i); for (int j = 0; j < tb.Columns.Count; ++j) { var cell = row.CreateCell(j); var data = tb.Rows[i][j]; cell.SetCellValue(ConvertHelper.GetString(data)); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
public void TestCreate() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(); XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data"); IList pictures = wb.GetAllPictures(); Assert.AreEqual(0, pictures.Count); int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG); Assert.AreEqual(1, pictures.Count); Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension()); Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data)); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30); Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType); anchor.AnchorType = (int)AnchorType.DontMoveAndResize; Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType); XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx); Assert.IsTrue(anchor.Equals(shape.GetAnchor())); Assert.IsNotNull(shape.PictureData); Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data)); CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0]; // STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs); }
public static DataSet ExcelImport(string strFileName, Tk5ListMetaData metaInfos, ResultHolder resultHolder) { DataSet dataSet = new DataSet(); DataTable dataTable = DataSetUtil.CreateDataTable(metaInfos.Table.TableName, metaInfos.Table.TableList); string sheetName = metaInfos.Table.TableDesc; HSSFWorkbook hssfworkbook = null; XSSFWorkbook xssfworkbook = null; ISheet sheet = null; string fileExt = Path.GetExtension(strFileName); using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (fileExt == ".xls") hssfworkbook = new HSSFWorkbook(file); else if (fileExt == ".xlsx") xssfworkbook = new XSSFWorkbook(file); } if (hssfworkbook != null) { sheet = hssfworkbook.GetSheet(sheetName); } else if (xssfworkbook != null) { sheet = xssfworkbook.GetSheet(sheetName); } SheetImport(metaInfos, dataTable, sheet, resultHolder); dataSet.Tables.Add(dataTable); return dataSet; }
/// <summary> /// Gets the field names from a workbook and stores them in a list. /// </summary> /// <param name="fieldNames">list where field names will be stored</param> /// <param name="workbook">workbook from which field names will be extracted</param> public static void GetFieldNames(ImportData data, XSSFWorkbook workbook) { ISheet sheet = workbook.GetSheetAt(0); bool reading = true; int rindex = sheet.FirstRowNum; while (reading) { IRow row = sheet.GetRow(rindex); if (row != null) { ICell cell = row.GetCell(0); if (cell != null) { string s = CellValueAsString(cell); if (s != "" && s[0] == '[') { for (int i = 0; i < row.LastCellNum; i++) { s = CellValueAsString(row.GetCell(i)).TrimEnd(']').TrimStart('['); ; data.fieldNames.Add(s); } // don't read more than one row of field names reading = false; } } } rindex++; if (rindex > sheet.LastRowNum) { reading = false; } } }
/// <summary> /// Read an excel file and extract the meta, variables, and parsable rows. /// </summary> /// <param name="assetPath"></param> public void ReadAsset(string assetPath, ref ImportData data) { Debug.Log("START IMPORT PROCESS FOR XLSX"); // get an absolute path to the asset string absolutePath = System.IO.Directory.GetCurrentDirectory() + "/" + assetPath; // open a file stream to the asset using (FileStream fs = new FileStream(absolutePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { // get workbook XSSFWorkbook wb = new XSSFWorkbook(fs); // get field names GetFieldNames(data, wb); // get key/value meta data GetKeyValData(ref data.meta, wb, "#"); // get key/value GetKeyValData(ref data.vars, wb, "$"); // get the parsable rows GetRows(data, wb); } }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> public byte[] DataTableToExcel(DataTable tb, bool show_header = true) { tb = tb ?? throw new ArgumentNullException($"无法把空{nameof(DataTable)}转成Excel"); using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsNotEmpty(tb.TableName) ? tb.TableName : "sheet"); var columns = tb.Columns.AsEnumerable_ <DataColumn>(); var row_index = 0; IRow NewRow() => sheet.CreateRow(row_index++); if (show_header) { //头部 var header_style = this.GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.Black.Index, NPOI.HSSF.Util.HSSFColor.White.Index); var header = NewRow(); var cell_index = 0; foreach (var col in columns) { var cell = header.CreateCell(cell_index++, CellType.String); var data = col.ColumnName; cell.SetCellValue(data); cell.CellStyle = header_style; } } var style = this.GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index); foreach (var tb_row in tb.Rows.AsEnumerable_ <DataRow>()) { var row = NewRow(); var cell_index = 0; foreach (var tb_col in columns) { var cell = row.CreateCell(cell_index++, CellType.String); var data = tb_row[tb_col.ColumnName]; cell.SetCellValue(ConvertHelper.GetString(data)); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
/// <summary> /// 写入DataTable到Excel /// </summary> /// <param name="dt"></param> /// <param name="excelFile"></param> public virtual void writeDataTableToExcel(DataTable dt, string excelFile) { //Excel数据 MemoryStream memoryStream = new MemoryStream(); //创建Workbook NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region 设置Excel样式 //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle(); cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleA.WrapText = true; //创建单元格设置对象(普通内容) NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle(); cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyleB.WrapText = true; //创建设置字体对象(内容字体) NPOI.SS.UserModel.IFont fontA = workbook.CreateFont(); fontA.FontHeightInPoints = 16;//设置字体大小 fontA.FontName = "宋体"; cellStyleA.SetFont(fontA); //创建设置字体对象(标题字体) NPOI.SS.UserModel.IFont fontB = workbook.CreateFont(); fontB.FontHeightInPoints = 16;//设置字体大小 fontB.FontName = "宋体"; fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellStyleB.SetFont(fontB); #endregion //写入基本数据 writeSheet(workbook, cellStyleA, cellStyleB, dt); #region 输出文件 //输出到流 workbook.Write(memoryStream); //写Excel文件 File.WriteAllBytes(excelFile, memoryStream.ToArray()); #endregion }
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); } }
private static ICellStyle SetCellStyle(NPOI.XSSF.UserModel.XSSFWorkbook workbook, string color) { ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.WrapText = true; cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //边框 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.FillPattern = FillPattern.SolidForeground; switch (color) { case "rose": cellStyle.FillForegroundColor = IndexedColors.Violet.Index; break; case "white": cellStyle.FillForegroundColor = IndexedColors.White.Index; break; case "red": cellStyle.FillForegroundColor = IndexedColors.Red.Index; break; case "yellow": cellStyle.FillForegroundColor = IndexedColors.Yellow.Index; break; case "lime": cellStyle.FillForegroundColor = IndexedColors.BrightGreen.Index; break; case "gray": cellStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index; break; default: cellStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index; break; } //CellsStyle.FillBackgroundColor = IndexedColors.BrightGreen.Index; return(cellStyle); }
public static byte[] Output(DataTable dataTable, string[] tableTitle) { NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet"); IRow Title = null; IRow rows = null; for (int i = 1; i <= dataTable.Rows.Count; i++) { //创建表头 if (i - 1 == 0) { Title = sheet.CreateRow(0); for (int k = 1; k < tableTitle.Length + 1; k++) { Title.CreateCell(0).SetCellValue("序号"); Title.CreateCell(k).SetCellValue(tableTitle[k - 1]); } continue; } else { rows = sheet.CreateRow(i - 1); for (int j = 1; j <= dataTable.Columns.Count; j++) { rows.CreateCell(0).SetCellValue(i - 1); rows.CreateCell(j).SetCellValue(dataTable.Rows[i - 1][j - 1].ToString()); } } } byte[] buffer = new byte[1024 * 5]; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); buffer = ms.GetBuffer(); ms.Close(); } return(buffer); }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> /// <param name="tb"></param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable tb) { if (tb == null) { return(null); } using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet"); var style = GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.Red.Index, NPOI.HSSF.Util.HSSFColor.White.Index); NPOI.SS.UserModel.IRow row = null; NPOI.SS.UserModel.ICell cell = null; for (int i = 0; i < tb.Rows.Count; ++i) { row = sheet.CreateRow(i); for (int j = 0; j < tb.Columns.Count; ++j) { cell = row.CreateCell(j); cell.SetCellValue(ConvertHelper.GetString(tb.Rows[i][j])); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
public IHttpActionResult ExportAllToExcel(FilterSales filter) { var response = new DataResponse <string>(); try { if (filter.GroupBy <= 0) { #region Sales Default Export NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport"); NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper(); string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray(); string[] headers, columns = null; headers = columns = columnname; columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray(); columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray(); //byte[] rgb = new byte[3] { 22, 183, 223 }; //XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb)); var headerRow = sheet.CreateRow(0); //headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua); //create header for (int i = 0; i < headers.Length; i++) { sheet.DefaultColumnWidth = 20; XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFColor colorToFill = new XSSFColor(Color.Aqua); style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua); headerRow.RowStyle = style; var cell = headerRow.CreateCell(i); cell.SetCellValue(headers[i]); } string[] allowedRoles = { "RDSLS" }; string[] superRoles = { "RDSLSALL" }; bool hasSuperRight = HasRight(superRoles); bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" }); if (HasRight(allowedRoles) || hasSuperRight) { var repository = new RepositorySales(); var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath); List <EntitySales> salesList = dataResponse.Model.List.ToList(); int recordCount = dataResponse.Model.Pager.TotalCount; //fill content var rowIndex = 0; for (int i = 0; i < recordCount; i++) { rowIndex++; var row = sheet.CreateRow(rowIndex); for (int j = 0; j < columns.Length; j++) { var font = workbook.CreateFont(); font.FontHeightInPoints = 11; font.FontName = "Calibri"; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; var cell = row.CreateCell(j); cell.CellStyle = workbook.CreateCellStyle(); cell.CellStyle.SetFont(font); string value = null; var objSalesItem = salesList[i]; var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]); if (objStaticItem != null) { var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase)); if (property != null) { value = Convert.ToString(property.GetValue(salesList[i], null)); } } else { var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]); if (objDynamicItem != null) { value = objDynamicItem.Value; } } cell.SetCellValue(value); } bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1; if (isFinanceDataCount) { for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++) { rowIndex++; var financeRow = sheet.CreateRow(rowIndex); for (int j = 0; j < columns.Length; j++) { var cell = financeRow.CreateCell(j); string value = null; var objSalesItem = salesList[i].FinanceDataList.ToList()[k]; var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]); if (objStaticItem != null) { value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null)); } else { var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]); if (objDynamicItem != null) { value = objDynamicItem.Value; } } cell.SetCellValue(value); } } } } string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports"); string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory)); if (!Directory.Exists(fileUri)) { Directory.CreateDirectory(fileUri); } string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now), extension = "xlsx"; string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension)); int count = 1; isExist: if (File.Exists(filePath)) { fileName = string.Format("{0}{1}{2}", fileName, count, extension); filePath = Path.Combine(fileUri, fileName); count++; goto isExist; } using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(stream); } response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension)); } return(Ok <DataResponse>(response)); #endregion } #region Sales Grouped Report return(ExportGroupedSalesToExcel(filter)); #endregion } catch (Exception ex) { ex.Log(); } response.Message = "Internal Server Error"; return(Ok <DataResponse>(response)); }
public void TestXSSFTextParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.CreateFont() as XSSFFont; Color color = Color.FromArgb(0, 255, 255); font.SetColor(new XSSFColor(color)); font.FontName = (/*setter*/ "Arial"); rt.ApplyFont(font); shape.SetText(/*setter*/ rt); Assert.IsNotNull(shape.GetCTShape()); Assert.IsNotNull(shape.GetEnumerator()); Assert.IsNotNull(XSSFSimpleShape.GetPrototype()); foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber))) { shape.TextParagraphs[(0)].SetBullet(nr); Assert.IsNotNull(shape.Text); } shape.TextParagraphs[(0)].SetBullet(false); Assert.IsNotNull(shape.Text); shape.SetText("testtext"); Assert.AreEqual("testtext", shape.Text); shape.SetText(new XSSFRichTextString()); Assert.AreEqual("null", shape.Text); shape.AddNewTextParagraph(); shape.AddNewTextParagraph("test-other-text"); shape.AddNewTextParagraph(new XSSFRichTextString("rtstring")); shape.AddNewTextParagraph(new XSSFRichTextString()); Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP); Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP); Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); Assert.AreEqual((short)VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.Bottom; Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.Top; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.STACKED); Assert.AreEqual(TextDirection.STACKED, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ 12.32); Assert.AreEqual(12.32, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ 12.31); Assert.AreEqual(12.31, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ 13.31); Assert.AreEqual(13.31, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.RightInset, 0.01); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ 23.31); Assert.AreEqual(23.31, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.TopInset, 0.01); Assert.IsTrue(shape.WordWrap); shape.WordWrap = (/*setter*/ false); Assert.IsFalse(shape.WordWrap); shape.WordWrap = (/*setter*/ true); Assert.IsTrue(shape.WordWrap); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE); Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.NONE); Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit); Assert.AreEqual(5, shape.ShapeType); shape.ShapeType = (/*setter*/ 23); Assert.AreEqual(23, shape.ShapeType); // TODO: should this be supported? // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); Assert.IsNotNull(shape.GetShapeProperties()); } finally { wb.Close(); } }
/** * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code> * for the (conservative) assumption that any cell may have its defInition Changed After * Evaluation begins. * @deprecated (Sep 2009) (reduce overloading) use {@link #Create(XSSFWorkbook, NPOI.ss.formula.IStabilityClassifier, NPOI.ss.formula.udf.UDFFinder)} */ public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) { _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, null); _book = workbook; }
public static System.IO.MemoryStream createExcelReport(DataTable dt) { using (System.IO.MemoryStream mem = new System.IO.MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.Center; headerLabelCellStyle.BorderBottom = BorderStyle.Thin; headerLabelCellStyle.BorderRight = BorderStyle.Thin; headerLabelCellStyle.BorderTop = BorderStyle.Thin; headerLabelCellStyle.BorderLeft = BorderStyle.Thin; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.Bold; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.Right; rightAlignedCellStyle.BorderBottom = BorderStyle.Thin; rightAlignedCellStyle.BorderRight = BorderStyle.Thin; rightAlignedCellStyle.BorderTop = BorderStyle.Thin; rightAlignedCellStyle.BorderLeft = BorderStyle.Thin; #endregion #region Currency Cell Style var CellStyleRight = workbook.CreateCellStyle(); CellStyleRight.Alignment = HorizontalAlignment.Right; CellStyleRight.BorderBottom = BorderStyle.Thin; CellStyleRight.BorderRight = BorderStyle.Thin; CellStyleRight.BorderTop = BorderStyle.Thin; CellStyleRight.BorderLeft = BorderStyle.Thin; #endregion #region Detail Currency Subtotal Style var CellStyleNormal = workbook.CreateCellStyle(); CellStyleNormal.BorderBottom = BorderStyle.Thin; CellStyleNormal.BorderRight = BorderStyle.Thin; CellStyleNormal.BorderTop = BorderStyle.Thin; CellStyleNormal.BorderLeft = BorderStyle.Thin; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.Normal; CellStyleNormal.SetFont(detailCurrencySubtotalFont); #endregion #endregion var sheet = workbook.CreateSheet(dt.TableName); var rowIndex = 0; var row = sheet.CreateRow(rowIndex); List <String> columns = new List <string>(); for (int i = 0; i < dt.Columns.Count; i++) { columns.Add(dt.Columns[i].ColumnName); var cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); cell.CellStyle = headerLabelCellStyle; } rowIndex++; //// Add data rows foreach (System.Data.DataRow item in dt.Rows) { row = sheet.CreateRow(rowIndex); for (int ii = 0; ii < dt.Columns.Count; ii++) { var cell = row.CreateCell(ii); cell.SetCellValue(dt.Columns[ii].ColumnName); if (dt.Columns[ii].DataType == typeof(System.Decimal)) { cell.CellStyle = CellStyleRight; } else { cell.CellStyle = CellStyleNormal; } cell.SetCellValue(item[dt.Columns[ii].ColumnName].ToString()); } rowIndex++; } for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); } workbook.Write(mem); return(mem); } }
/// <summary> /// Загрузка из XSLX. /// </summary> /// <param name="FileName">Имя файла с полным путем к XLSX файлу</param> private void LoadXLSX(string FileName) { BeginOfLoad(); DateTime dateTime1 = DateTime.Now; NPOI.XSSF.UserModel.XSSFWorkbook book = null; //Книга. NPOI.SS.UserModel.ISheet sheet = null; //Лист. try { FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); if (fs != null) { fs.Close(); } } catch (Exception ex) { sys.SM("Ошибка открытия файла шаблона: " + ex.Message); return; } //Устанавливаем текущий лист. string errorMes = ""; if (!sys.SetCurrentSheet(book, 1, true, out sheet, out errorMes)) { return; } string[] arrLine = null; //= new string[sheet.LastCellNum + 1]; int columnsCount = 0; //Код выполняется в отдельном потоке var task1 = Task.Factory.StartNew(() => { //запускаем цикл по строкам for (int row = sheet.FirstRowNum; row <= sheet.LastRowNum; row++) { //получаем строку var currentRow = sheet.GetRow(row); if (currentRow == null) { continue; //null когда строка содержит только пустые ячейки } //запускаем цикл по столбцам if (arrLine == null) { columnsCount = currentRow.LastCellNum; arrLine = new string[currentRow.LastCellNum]; } for (int col = 0; col < columnsCount; col++) { if (loadStop) { return; } //получаем значение ячейки string value = ""; var currentCell = currentRow.GetCell(col); if (currentCell == null) { arrLine[col] = value; continue; } NPOI.SS.UserModel.CellType ctype = currentCell.CellType; if (ctype == NPOI.SS.UserModel.CellType.String) { value = currentCell.StringCellValue; } else if (ctype == NPOI.SS.UserModel.CellType.Boolean) { value = currentCell.BooleanCellValue.ToString(); } else if (ctype == NPOI.SS.UserModel.CellType.Formula) { value = currentCell.CellFormula; } else if (ctype == NPOI.SS.UserModel.CellType.Numeric) { value = currentCell.NumericCellValue.ToString(); } else if (ctype == NPOI.SS.UserModel.CellType.Error) { value = currentCell.ErrorCellValue.ToString(); } arrLine[col] = value; } if (row == sheet.FirstRowNum) { for (int i = 0; i < arrLine.Length; i++) { if (arrLine[i] == "") { arrLine[i] = "Column"; } } Arr.SetUniqValue(arrLine); //Меняем название колонок, если они дублируются. //Потому чтонельзя добавлять в dt колонки с одинаковым именем. for (int i = 0; i < arrLine.Length; i++) { dt.Columns.Add(arrLine[i], typeof(string)); } } else { dt.Rows.Add(arrLine); } } }); //После завершения обновляем GUI компаненты. var task2 = task1.ContinueWith((previous) => { EndOfLoad(dateTime1); }, TaskScheduler.FromCurrentSynchronizationContext()); return; }
/** * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code> * for the (conservative) assumption that any cell may have its defInition Changed After * Evaluation begins. * @param udfFinder pass <code>null</code> for default (AnalysisToolPak only) */ public static XSSFFormulaEvaluator Create(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { return(new XSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder)); }
public XSSFFormulaEvaluator(XSSFWorkbook workbook) : this(workbook, null, null) { }
public void CreateExcelFile() { string FileName = @"d:\5.5.5.101_5555_Log_18.txt"; string[] filelist = File.ReadAllLines(FileName, Encoding.Default); List <string> StringLists = new List <string>(); int gg = 0; for (int linenum = filelist.Length - 1; linenum >= 0; linenum--) { if (filelist[linenum].IndexOf("ANR") > -1) { int first = filelist[linenum].IndexOf("ANR in ") + "ANR in ".Length; int last = filelist[linenum].LastIndexOf(" ("); string str2 = filelist[linenum].Substring(first, last - first); StringLists.Add(str2); } } ////建立Excel 2007檔案 IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); //合併區 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 1, 2)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6)); //合併Summary行 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 5)); //合併Error List行 //背景色(藍色) ICellStyle cellStyle0 = workbook.CreateCellStyle(); cellStyle0.FillPattern = FillPattern.SolidForeground; cellStyle0.FillForegroundColor = IndexedColors.PaleBlue.Index; cellStyle0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //背景色(綠色) ICellStyle cellStyle1 = workbook.CreateCellStyle(); cellStyle1.FillPattern = FillPattern.SolidForeground; cellStyle1.FillForegroundColor = IndexedColors.Lime.Index; cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //背景色(粉色) ICellStyle cellStyle2 = workbook.CreateCellStyle(); cellStyle2.FillPattern = FillPattern.SolidForeground; cellStyle2.FillForegroundColor = IndexedColors.Tan.Index; cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //背景色(灰色) ICellStyle cellStyle3 = workbook.CreateCellStyle(); cellStyle3.FillPattern = FillPattern.SolidForeground; cellStyle3.FillForegroundColor = IndexedColors.Grey25Percent.Index; //背景色(白色) ICellStyle cellStyle4 = workbook.CreateCellStyle(); cellStyle4.FillPattern = FillPattern.SolidForeground; cellStyle4.FillForegroundColor = IndexedColors.White.Index; //Summary儲存格格式 ICellStyle summaryStyle = workbook.CreateCellStyle(); IFont summaryFont = workbook.CreateFont(); summaryFont.FontHeightInPoints = 18; summaryStyle.SetFont(summaryFont); summaryStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; summaryStyle.FillPattern = FillPattern.SolidForeground; summaryStyle.FillForegroundColor = IndexedColors.PaleBlue.Index; //A列 sheet.CreateRow(0).CreateCell(0).SetCellValue("Project Name"); sheet.CreateRow(1).CreateCell(0).SetCellValue("Model Name"); sheet.CreateRow(2).CreateCell(0).SetCellValue("Start Time"); sheet.CreateRow(3).CreateCell(0).SetCellValue("Renew Time"); sheet.CreateRow(4).CreateCell(0).SetCellValue("SW Build Time"); sheet.CreateRow(5).CreateCell(0).SetCellValue("Project No."); sheet.CreateRow(6).CreateCell(0).SetCellValue("Test Device"); sheet.CreateRow(7).CreateCell(0).SetCellValue("Tester"); for (int A = 0; A < 8; A++) { sheet.GetRow(A).GetCell(0).CellStyle = cellStyle0; } //E列 sheet.GetRow(0).CreateCell(4).SetCellValue("Date"); sheet.GetRow(1).CreateCell(4).SetCellValue("Period (H)"); sheet.GetRow(2).CreateCell(4).SetCellValue("SW ISSUES"); sheet.GetRow(3).CreateCell(4).SetCellValue("System Crash"); sheet.GetRow(4).CreateCell(4).SetCellValue("Result"); sheet.GetRow(5).CreateCell(4).SetCellValue("MTBF_SW"); sheet.GetRow(6).CreateCell(4).SetCellValue("MTBF_Crash"); for (int E = 0; E < 7; E++) { sheet.GetRow(E).GetCell(4).CellStyle = cellStyle0; } sheet.GetRow(4).GetCell(4).CellStyle = cellStyle4; //F列 sheet.GetRow(0).CreateCell(5).SetCellValue("-----"); sheet.GetRow(1).CreateCell(5).SetCellValue("-----"); sheet.GetRow(2).CreateCell(5).SetCellValue("-----"); sheet.GetRow(3).CreateCell(5).SetCellValue("-----"); sheet.GetRow(4).CreateCell(5).SetCellValue(""); sheet.GetRow(5).CreateCell(5).SetCellValue("-----"); sheet.GetRow(6).CreateCell(5).SetCellValue("-----"); for (int F = 0; F < 7; F++) { sheet.GetRow(F).GetCell(5).CellStyle = cellStyle2; } sheet.GetRow(4).GetCell(5).CellStyle = cellStyle4; //Summary sheet.CreateRow(9).CreateCell(0).SetCellValue("Summary"); sheet.GetRow(9).GetCell(0).CellStyle = summaryStyle; //Error List sheet.CreateRow(10).CreateCell(0).SetCellValue("Error List"); sheet.GetRow(10).GetCell(0).CellStyle = cellStyle3; //Total sheet.GetRow(10).CreateCell(6).SetCellValue("Total"); sheet.GetRow(10).GetCell(6).CellStyle = cellStyle3; //搜尋相同字串並記次 Dictionary <string, int> dict = new Dictionary <string, int>(); foreach (string myStringList in StringLists) { if (dict.ContainsKey(myStringList)) { //如果Dictionary中存在这个关键词元素,则把这个Dictionary的key+1 dict[myStringList]++; } else { //如果Dictionary中不存在这个关键词元素,则把它添加进Dictionary dict.Add(myStringList, 1); } } int rowcnt = dict.Count; while (rowcnt != 0) { foreach (KeyValuePair <string, int> item in dict) { Console.WriteLine(item.Key); Console.WriteLine(item.Value); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10 + rowcnt, 10 + rowcnt, 0, 5)); //合併Error List行 sheet.CreateRow(10 + rowcnt).CreateCell(0).SetCellValue(item.Key); sheet.GetRow(10 + rowcnt).CreateCell(6).SetCellValue(item.Value); rowcnt--; } } for (int c = 0; c <= 25; c++) { sheet.AutoSizeColumn(c); } FileStream file = new FileStream(@"d:\npoi.xlsx", FileMode.Create);//產生檔案 workbook.Write(file); file.Close(); }
/// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="exceltype">是否模板</param> /// <param name="FileName">文件名称</param> public void DataTableToExcelTemplet(DataTable dtSource, string ExportType, string FileName = "") { string TempletFileName = "";//模板文件名称 FileStream file = null; NPOI.SS.UserModel.IWorkbook workbook = null; SaveFileDialog savefd = new SaveFileDialog(); //模板文件 TempletFileName = Application.StartupPath + "\\JNMLTemp.xls"; file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read); if (FileName != "") { savefd.FileName = FileName + ".xls"; } savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } if (savefd.FilterIndex == 1) { workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file); } else { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(file); } HSSFSheet sheet = (HSSFSheet)workbook.GetSheet("Sheet1"); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 1; foreach (DataRow row in dtSource.Rows) { #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.GetRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.GetCell(column.Ordinal); string drValue = row[column].ToString(); if (column.ColumnName == "numxh") { newCell.SetCellValue(rowIndex.ToString()); continue; } switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } if (file != null) { file.Dispose(); file.Close(); } }
/// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="FileName">文件名称</param> /// <param name="exceltype">是否模板</param> public void DataTableToExcel(DataTable dtSource, string ExportType, string strHeaderText, string FileName = "") { NPOI.SS.UserModel.IWorkbook workbook = null; SaveFileDialog savefd = new SaveFileDialog(); if (FileName != "") { savefd.FileName = FileName + ".xls"; } savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx"; if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } if (savefd.FilterIndex == 1) { workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); } HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ExportType); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = SetExcelColWidth(dtSource, ExportType); int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(ExportType); } #region 表头及样式 { if (strHeaderText != "") { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 18; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); rowIndex = 1; } } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 11; font.Boldweight = 700; font.IsBold = true; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex++; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } }
/// <summary> /// 将excel导入到datatable /// </summary> /// <param name="filePath">excel路径</param> /// <param name="isColumnName">第一行是否是列名</param> /// <returns>返回datatable</returns> public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumnHeader, string fileName) { ISheet sheet = null; IWorkbook workbook = null; System.Data.DataTable data = new System.Data.DataTable(); FileStream fs = null; int startRow = 0; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); } catch (Exception ex) { CloseStream(fs); MessageBox.Show("文件打开失败!请检查文件是否被占用,关闭已打开文件后重试!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(data); } try { if (Path.GetExtension(fileName) == ".xlsx") { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);//2007 } else if (Path.GetExtension(fileName) == ".xls") { fs.Position = 0; workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);//2003 } if (workbook == null) { CloseStream(fs); MessageBox.Show($"打开文件失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(data); } } catch (Exception ex) { CloseStream(fs); MessageBox.Show($"{ex.Message}", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); //Aspose2Data(fileName); return(data); } if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet == null) { MessageBox.Show($"未查询到Sheet表格!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); CloseStream(fs); return(data); } IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 try { if (isFirstRowColumnHeader) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { DataColumn column = new DataColumn("column" + (i + 1)); data.Columns.Add(column); } startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } DataRow dataRow = data.NewRow(); bool IsStrNull = false; for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { if (row.GetCell(j).ToString().Trim() != "") { dataRow[j] = row.GetCell(j).ToString(); IsStrNull = true; } } } if (IsStrNull) { data.Rows.Add(dataRow); } } } catch (Exception ex) { CloseStream(fs); MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return(data); } CloseStream(fs); return(data); }
public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter) { var response = new DataResponse <string>(); try { #region Sales Default Export NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport"); NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper(); string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" }; var headerRow = sheet.CreateRow(0); //create header for (int i = 0; i < headers.Length; i++) { sheet.DefaultColumnWidth = 20; XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFColor colorToFill = new XSSFColor(Color.Aqua); style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua); headerRow.RowStyle = style; var cell = headerRow.CreateCell(i); cell.SetCellValue(headers[i]); } string[] allowedRoles = { "RDSLS" }; string[] superRoles = { "RDSLSALL" }; bool hasSuperRight = HasRight(superRoles); bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" }); if (HasRight(allowedRoles) || hasSuperRight) { var repository = new RepositorySales(); var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath); List <GroupedSales> salesList = dataResponse.Model.List.ToList(); int recordCount = dataResponse.Model.Pager.TotalCount; //fill content var rowIndex = 0; for (int i = 0; i < recordCount; i++) { rowIndex++; var row = sheet.CreateRow(rowIndex); var objSalesItem = salesList[i]; if (objSalesItem == null) { continue; } var font = workbook.CreateFont(); font.FontHeightInPoints = 11; font.FontName = "Calibri"; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; var cell1 = row.CreateCell(0); cell1.CellStyle = workbook.CreateCellStyle(); cell1.CellStyle.SetFont(font); cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName); var cell2 = row.CreateCell(1); cell2.CellStyle = workbook.CreateCellStyle(); cell2.CellStyle.SetFont(font); cell2.SetCellValue(objSalesItem.Count); var cell3 = row.CreateCell(2); cell3.CellStyle = workbook.CreateCellStyle(); cell3.CellStyle.SetFont(font); cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn)); } string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports"); string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory)); if (!Directory.Exists(fileUri)) { Directory.CreateDirectory(fileUri); } string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now), extension = "xlsx"; string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension)); int count = 1; isExist: if (File.Exists(filePath)) { fileName = string.Format("{0}{1}{2}", fileName, count, extension); filePath = Path.Combine(fileUri, fileName); count++; goto isExist; } using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(stream); } response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension)); } return(Ok <DataResponse>(response)); #endregion } catch (Exception ex) { ex.Log(); } response.Message = "Internal Server Error"; return(Ok <DataResponse>(response)); }
/// <summary> /// 匯入副檔名為.xls的Excel檔th /// </summary> private DataTable ImportXLS(string file) { DataTable dt = new DataTable(); IWorkbook workbook; //只能讀取 System.IO.Stream //FileContent 屬性會取得指向要上載之檔案的 Stream 物件。這個屬性可以用於存取檔案的內容 (做為位元組)。 //例如,您可以使用 FileContent 屬性傳回的 Stream 物件,將檔案的內容做為位元組進行讀取並將其以位元組陣列儲存。 //FileContent 屬性,型別:System.IO.Stream //檢查上傳檔案的附檔名 string fileExt = Path.GetExtension(file).ToLower(); using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { //XSSFWorkbook 適用XLSX格式,HSSFWorkbook 適用XLS格式 if (fileExt == ".xlsx") { workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); } else { workbook = null; } if (workbook == null) { return(null); } //取得目標sheet,指定為第1個分頁 ISheet sheet = workbook.GetSheetAt(0); //取得表頭 IRow header = sheet.GetRow(sheet.FirstRowNum); List <int> columns = new List <int>(); for (int i = 0; i < header.LastCellNum; i++) { object obj = GetValueType(header.GetCell(i)); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); } else { dt.Columns.Add(new DataColumn(obj.ToString())); } columns.Add(i); } //取得資料內容 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] = GetValueType(sheet.GetRow(i).GetCell(j)); if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } } return(dt); }
// Disabled because shift rows is not yet implemented for SXSSFWorkbook public void disabled_testBug53798XLSXStream() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53798_ShiftNegative_TMPL.xlsx"); FileInfo xlsOutput = TempFile.CreateTempFile("testBug53798", ".xlsx"); //bug53798Work(new SXSSFWorkbook(wb), xlsOutput); }
private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder); _book = workbook; }
public void TestCreate() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper; String[] urls = { "http://apache.org/", "www.apache.org", "/temp", "file:///c:/temp", "http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*" }; for (int i = 0; i < urls.Length; i++) { String s = urls[i]; XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink; link.Address = (s); XSSFCell cell = row.CreateCell(i) as XSSFCell; cell.Hyperlink = (link); } workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile) { Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath); } else { // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } } // Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook; sheet = workbook.GetSheetAt(0) as XSSFSheet; rels = sheet.GetPackagePart().Relationships; Assert.AreEqual(urls.Length, rels.Size); for (int i = 0; i < rels.Size; i++) { PackageRelationship rel = rels.GetRelationship(i); if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile) { Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath); } else { // there should be a relationship for each URL Assert.AreEqual(urls[i], rel.TargetUri.ToString()); } } }
public void TestLoadSave() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx"); ICreationHelper CreateHelper = workbook.GetCreationHelper(); Assert.AreEqual(3, workbook.NumberOfSheets); XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); // Check hyperlinks Assert.AreEqual(4, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); // Write out, and check // Load up again, check all links still there XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); Assert.AreEqual(3, wb2.NumberOfSheets); Assert.IsNotNull(wb2.GetSheetAt(0)); Assert.IsNotNull(wb2.GetSheetAt(1)); Assert.IsNotNull(wb2.GetSheetAt(2)); sheet = (XSSFSheet)wb2.GetSheetAt(0); // Check hyperlinks again Assert.AreEqual(4, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); // Add one more, and re-check IRow r17 = sheet.CreateRow(17); ICell r17c = r17.CreateCell(2); IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url); hyperlink.Address = ("http://poi.apache.org/spreadsheet/"); hyperlink.Label = "POI SS Link"; r17c.Hyperlink = (hyperlink); Assert.AreEqual(5, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); Assert.AreEqual(HyperlinkType.Url, sheet.GetRow(17).GetCell(2).Hyperlink.Type); Assert.AreEqual("POI SS Link", sheet.GetRow(17).GetCell(2).Hyperlink.Label); Assert.AreEqual("http://poi.apache.org/spreadsheet/", sheet.GetRow(17).GetCell(2).Hyperlink.Address); // Save and re-load once more XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2); Assert.AreEqual(3, wb3.NumberOfSheets); Assert.IsNotNull(wb3.GetSheetAt(0)); Assert.IsNotNull(wb3.GetSheetAt(1)); Assert.IsNotNull(wb3.GetSheetAt(2)); sheet = (XSSFSheet)wb3.GetSheetAt(0); Assert.AreEqual(5, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); Assert.AreEqual(HyperlinkType.Url, sheet.GetRow(17).GetCell(2).Hyperlink.Type); Assert.AreEqual("POI SS Link", sheet.GetRow(17).GetCell(2).Hyperlink.Label); Assert.AreEqual("http://poi.apache.org/spreadsheet/", sheet.GetRow(17).GetCell(2).Hyperlink.Address); }
public void Bug49702() { // First try with a new file XSSFWorkbook wb1 = new XSSFWorkbook(); // Should have one style Assert.AreEqual(1, wb1.NumCellStyles); wb1.GetCellStyleAt((short)0); try { wb1.GetCellStyleAt((short)1); Assert.Fail("Shouldn't be able to get style at 1 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } // Add another one ICellStyle cs = wb1.CreateCellStyle(); cs.DataFormat = ((short)11); // Re-check Assert.AreEqual(2, wb1.NumCellStyles); wb1.GetCellStyleAt((short)0); wb1.GetCellStyleAt((short)1); try { wb1.GetCellStyleAt((short)2); Assert.Fail("Shouldn't be able to get style at 2 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } // Save and reload XSSFWorkbook nwb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb1); Assert.AreEqual(2, nwb.NumCellStyles); nwb.GetCellStyleAt((short)0); nwb.GetCellStyleAt((short)1); try { nwb.GetCellStyleAt((short)2); Assert.Fail("Shouldn't be able to Get style at 2 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } // Now with an existing file XSSFWorkbook wb2 = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx"); Assert.AreEqual(3, wb2.NumCellStyles); wb2.GetCellStyleAt((short)0); wb2.GetCellStyleAt((short)1); wb2.GetCellStyleAt((short)2); try { wb2.GetCellStyleAt((short)3); Assert.Fail("Shouldn't be able to Get style at 3 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } wb2.Close(); wb1.Close(); nwb.Close(); }
public void Bug58245_XSSFSheetIterator() { XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet(); // ===================================================================== // Case 1: Existing code uses XSSFSheet for-each loop // ===================================================================== // Original code (no longer valid) /* * for (XSSFSheet sh : wb) { * sh.createRow(0); * } */ // Option A: foreach (XSSFSheet sh in wb) { sh.CreateRow(0); } // Option B (preferred for new code): foreach (ISheet sh in wb) { sh.CreateRow(0); } // ===================================================================== // Case 2: Existing code creates an iterator variable // ===================================================================== // Original code (no longer valid) /* * Iterator<XSSFSheet> it = wb.iterator(); * XSSFSheet sh = it.next(); * sh.createRow(0); */ // Option A: { IEnumerator <XSSFSheet> it = wb.GetEnumerator() as IEnumerator <XSSFSheet>; XSSFSheet sh = it.Current; sh.CreateRow(0); } // Option B: { //IEnumerator<XSSFSheet> it = wb.XssfSheetIterator(); //XSSFSheet sh = it.Current; //sh.CreateRow(0); } // Option C (preferred for new code): { IEnumerator <ISheet> it = wb.GetEnumerator() as IEnumerator <ISheet>; ISheet sh = it.Current; sh.CreateRow(0); } wb.Close(); }
public void SaveLoadNew() { XSSFWorkbook wb1 = new XSSFWorkbook(); //check that the default date system is Set to 1900 CT_WorkbookPr pr = wb1.GetCTWorkbook().workbookPr; Assert.IsNotNull(pr); Assert.IsTrue(pr.IsSetDate1904()); Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system"); ISheet sheet1 = wb1.CreateSheet("sheet1"); ISheet sheet2 = wb1.CreateSheet("sheet2"); wb1.CreateSheet("sheet3"); IRichTextString rts = wb1.GetCreationHelper().CreateRichTextString("hello world"); sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2); sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts); sheet2.CreateRow(0); Assert.AreEqual(0, wb1.GetSheetAt(0).FirstRowNum); Assert.AreEqual(1, wb1.GetSheetAt(0).LastRowNum); Assert.AreEqual(0, wb1.GetSheetAt(1).FirstRowNum); Assert.AreEqual(0, wb1.GetSheetAt(1).LastRowNum); Assert.AreEqual(0, wb1.GetSheetAt(2).FirstRowNum); Assert.AreEqual(0, wb1.GetSheetAt(2).LastRowNum); FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx"); Stream out1 = File.OpenWrite(file.FullName); wb1.Write(out1); out1.Close(); // Check the namespace Contains what we'd expect it to OPCPackage pkg = OPCPackage.Open(file.ToString()); PackagePart wbRelPart = pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels")); Assert.IsNotNull(wbRelPart); Assert.IsTrue(wbRelPart.IsRelationshipPart); Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType); PackagePart wbPart = pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml")); // Links to the three sheets, shared strings and styles Assert.IsTrue(wbPart.HasRelationships); Assert.AreEqual(5, wbPart.Relationships.Size); wb1.Close(); // Load back the XSSFWorkbook XSSFWorkbook wb2 = new XSSFWorkbook(pkg); Assert.AreEqual(3, wb2.NumberOfSheets); Assert.IsNotNull(wb2.GetSheetAt(0)); Assert.IsNotNull(wb2.GetSheetAt(1)); Assert.IsNotNull(wb2.GetSheetAt(2)); Assert.IsNotNull(wb2.GetSharedStringSource()); Assert.IsNotNull(wb2.GetStylesSource()); Assert.AreEqual(0, wb2.GetSheetAt(0).FirstRowNum); Assert.AreEqual(1, wb2.GetSheetAt(0).LastRowNum); Assert.AreEqual(0, wb2.GetSheetAt(1).FirstRowNum); Assert.AreEqual(0, wb2.GetSheetAt(1).LastRowNum); Assert.AreEqual(0, wb2.GetSheetAt(2).FirstRowNum); Assert.AreEqual(0, wb2.GetSheetAt(2).LastRowNum); sheet1 = wb2.GetSheetAt(0); Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001); Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String); pkg.Close(); Assert.AreEqual(0, Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.tmp").Length, "At Last: There are no temporary files."); }
/** * Creates an XSSFName object - called internally by XSSFWorkbook. * * @param name - the xml bean that holds data represenring this defined name. * @param workbook - the workbook object associated with the name * @see NPOI.XSSF.usermodel.XSSFWorkbook#CreateName() */ public XSSFName(CT_DefinedName name, XSSFWorkbook workbook) { _workbook = workbook; _ctName = name; }
protected XSSFEvaluationWorkbook(IWorkbook book) { _uBook = (XSSFWorkbook)book; }
private LoadPatternList() { using (FileStream fs = new FileStream(path,FileMode.Open,FileAccess.Read)) { IWorkbook workbook = new XSSFWorkbook(fs); int sheetnumber = workbook.NumberOfSheets; int [][][] sheetelements = new int[sheetnumber][][]; for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){ ISheet _isheet = workbook.GetSheetAt(sheetindex); int lastrownum = _isheet.LastRowNum; int [][] rowelements = new int[lastrownum+1][]; for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){ IRow row = _isheet.GetRow(rowindex); if(row == null)continue; int lastcellnum = row.LastCellNum; int[] cellelements = new int[lastcellnum+1]; for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){ ICell cell = row.GetCell(cellindex); if(cell != null){ cellelements[cellindex] = Convert.ToInt32(cell.ToString()); } } rowelements[rowindex] = cellelements; } sheetelements[sheetindex] = rowelements; } data = sheetelements; } if(data.Length > 0){ checksign = "No"; } }
public static DataSet CreateDataSetFromExcel(Stream streamToProcess, string fileExtentison = "xlsx") { DataSet model = new DataSet(); if (streamToProcess != null) { if (fileExtentison.ToLower() == "xlsx") { XSSFWorkbook workbook = new XSSFWorkbook(streamToProcess); model = ProcessXLSX(workbook); } else { HSSFWorkbook workbook = new HSSFWorkbook(streamToProcess); model = ProcessXLSX(workbook); } } return model; }