C# UserModel.XSSFWorkbook类代码示例

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

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

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

        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();

            
        }

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

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

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

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

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

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

        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 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 ExcelParser(string fileName)
 {
     FileStream fs = new FileStream(fileName,
        FileMode.Open);
        template = new XSSFWorkbook(fs);
        TestCases = new List<TestCase>();
 }