C# XSSFWorkbook.GetSheet方法代码示例

 public XLSXPlugin(string contentFormat, string contentEncoding, string currentFile, string currentWorkingDirectory)
 {
     this.contentFormat = new ContentType(contentFormat, contentEncoding);
     this.currentFile = currentFile;
     fileInput = new FileStream(Path.Combine(currentWorkingDirectory, this.currentFile), FileMode.Open, FileAccess.Read);
     workBook = new XSSFWorkbook(this.fileInput);
     currentPageName = currentFile.Substring(this.currentFile.LastIndexOf("/") + 1, this.currentFile.LastIndexOf("."));
     this.currentWorkingDirectory = currentWorkingDirectory;
     sheet = workBook.GetSheet("Sheet1");
     cmsBlockFactory = new CMSBlockFactory(currentPageName);
     processDataRows();
 }

        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 static void ReadYinTaiStoreExcel()
 {
     Stream excel=new FileStream(@"E:\工作资料\门店帐号信息完整版.xlsx",FileMode.Open);
     IWorkbook workbook = new XSSFWorkbook(excel);
     ISheet sheet = workbook.GetSheet("门店信息");
     var sb = new StringBuilder();
     for (int i = sheet.FirstRowNum+1; i < sheet.LastRowNum; i++)
     {
         var row = sheet.GetRow(i);
         int j = row.FirstCellNum+3;
         sb.AppendFormat(
                 "INSERT INTO [YintaiEvent].[dbo].[Store]([StoreId],[City],[StoreName],[CompanyFullName],[Address],[OfficeAddress],[Phone],[Bank1],[BankAccount1],[Bank2],[BankAccount2],[InUserId],[EditUserId],[InDate],[EditDate])VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',{13},{14})", i, row.GetCell(j-2),row.GetCell(j-1), row.GetCell(j), row.GetCell(j + 1), row.GetCell(j + 2), row.GetCell(j + 3), row.GetCell(j + 4), row.GetCell(j + 5), row.GetCell(j + 6), row.GetCell(j + 7), "0", "0", "GetDate()", "GetDate()");
         sb.Append("\n");
     }
     var str = sb.ToString();
 }

	// Use this for initialization
	void Start ()
    {
        Debug.Log(Application.dataPath + "/Resources/Data/Test.xlsx");

        if(File.Exists(Application.dataPath + "/Resources/Data/Test.xlsx"))
        {
            Debug.Log("OK");
        }
        else
        {
            Debug.Log("No file");
        }

        XSSFWorkbook workbook = new XSSFWorkbook(Application.dataPath+ "/Resources/Data/Test.xlsx");
        ISheet testSheet = workbook.GetSheet("Test");
        IEnumerator iter = testSheet.GetRowEnumerator();
        while (iter.MoveNext())
        {
            IRow row = iter.Current as IRow;
            string line = "";
            foreach(ICell cell in row.Cells)
            {
                switch (cell.CellType)
                {
                    case CellType.String:
                        line += cell.StringCellValue;
                        break;
                    case CellType.Numeric:
                        line += cell.NumericCellValue;
                        break;
                    case CellType.Boolean:
                        line += cell.BooleanCellValue;
                        break;
                    case CellType.Error:
                        line += cell.ErrorCellValue;
                        break;
                }

                line += "[" + cell.CellType + "]"+ (cell.CellComment!=null ? ("("+cell.CellComment.String.String+")"):"")+"\t";
                
            }

            Debug.Log(line);
        }

    }

        public void abc(String FilePath)
        {

            XSSFWorkbook hssfwb;
            using (FileStream file = new FileStream(FilePath), FileMode.Open, FileAccess.Read))
            {
                hssfwb = new XSSFWorkbook(file);
            }

         
            ISheet sheet = hssfwb.GetSheet("Sheet1");
         

           
           
            for (int row = 0; row <= sheet.LastRowNum+1; row++)
            {
                if (sheet.GetRow(row) != null) 
                {

        public void TestNoColsWithoutWidthWhenGrouping()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");

            sheet.SetColumnWidth(4, 5000);
            sheet.SetColumnWidth(5, 5000);

            sheet.GroupColumn((short)4, (short)7);
            sheet.GroupColumn((short)9, (short)12);

            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGrouping");
            sheet = (XSSFSheet)wb.GetSheet("test");

            CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
            //logger.log(POILogger.DEBUG, "test52186/cols:" + cols);
            foreach (CT_Col col in cols.GetColArray())
            {
                Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
            }
        }

        public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.HKHandleQuanDetail> hkHandleQuanDetails, DateTime dt, ref string fileName)
        {
            FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号12 香港新马激励量化单（5.5日之后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            ISheet sheet = workbook.GetSheet("香港新马");

            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            style.SetFont(font);

            IRow row = sheet.GetRow(1);
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("英亚网申3");
            cell.CellStyle = style;
            cell = row.CreateCell(6);
            cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
            cell.CellStyle = style;
            cell = row.CreateCell(10);
            cell.SetCellValue(hkHandleQuanDetails.Count + "个");
            cell.CellStyle = style;

            //设置单元格格式
            style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            style.SetFont(font);

            //方框样式
            ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
            trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
            trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
            trueOrFalseStyle.BorderTop = BorderStyle.Thin;
            trueOrFalseStyle.BorderRight = BorderStyle.Thin;
            trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
            trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
            font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 12;
            trueOrFalseStyle.SetFont(font);
            int i = 0;
            //for (int i = 0; i < ukHandleNumDetails.Count; i++)
            //{
            foreach (Model.DTO.HKHandleQuanDetail item in hkHandleQuanDetails.Values)
            {
                row = sheet.CreateRow(4 + i);
                row.HeightInPoints = 25;
                //序号
                cell = row.CreateCell(0);
                cell.CellStyle = style;
                cell.SetCellValue(i + 1);
                //合同号
                cell = row.CreateCell(1);
                cell.CellStyle = style;
                cell.SetCellValue(item.ContractNum);
                //学生姓名
                cell = row.CreateCell(2);
                cell.CellStyle = style;
                cell.SetCellValue(item.StudentName);
                //院校英文名称
                cell = row.CreateCell(3);
                cell.CellStyle = style;
                cell.SetCellValue(item.University);
                //量化类别
                cell = row.CreateCell(4);
                cell.CellStyle = style;
                cell.SetCellValue(item.ApplicationType);
                //网申寄出
                cell = row.CreateCell(5);
                cell.CellStyle = trueOrFalseStyle;
                cell.SetCellValue(item.SendQuan.Online);
                //翻译
                cell = row.CreateCell(6);
                cell.CellStyle = trueOrFalseStyle;
                cell.SetCellValue(item.SendQuan.Translation);
                //签证寄出
                cell = row.CreateCell(7);
                cell.CellStyle = trueOrFalseStyle;
                cell.SetCellValue(item.SendQuan.Visa);
                //寄出日期
                cell = row.CreateCell(8);
                cell.CellStyle = style;
                cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
                //资深文案
                cell = row.CreateCell(9);
                cell.CellStyle = style;
                cell.SetCellValue(item.CopyWriting.Senior);
                //制作文案
                cell = row.CreateCell(10);
                cell.CellStyle = style;
//.........这里部分代码省略.........

        public static bool Import(string TargetFolder)
        {
            bool Success = true;

            string[] Files = Directory.GetFiles(TargetFolder);
            List<string> ErrorList = new List<string>();

            foreach (string CurrentFile in Files)
            {
                try
                {
                    XSSFWorkbook MyWorkbook = null;

                    using (FileStream file = new FileStream(CurrentFile, FileMode.Open, FileAccess.Read))
                    {
                        MyWorkbook = new XSSFWorkbook(file);

                        // Read file values here        

                        #region Biographical
                        //Notes:  Did not make provisions for multiple Clinics, Schools or Grades - Client must confirm if it is necessary
                        string BioChowName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(0).StringCellValue;
                        string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(1).StringCellValue;
                        string BioDateOfScreen = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(2).StringCellValue;
                        string BioHeadOfHousehold = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(3).StringCellValue;
                        string BioName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(4).StringCellValue;
                        string BioSurname = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(5).StringCellValue;
                        string BioGPSLat = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(6).StringCellValue;
                        string BioGPSLong = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(7).StringCellValue;
                        string BioIDNum = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(8).StringCellValue;
                        string BioClinicUsed = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(9).StringCellValue;
                        string BioDateOfBirth = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(10).StringCellValue;
                        string BioMale = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(11).StringCellValue;
                        string BioFemale = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(12).StringCellValue;
                        string BioAttendingSchool = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(13).StringCellValue;
                        string BioSchoolName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(14).StringCellValue;
                        string BioGrade = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(15).StringCellValue;
                        #endregion

                        #region Environmental
                        //Notes:  Did not make provisions for multiple Clinics,  - Client must confirm if it is necessary
                        //Notes"  Made provision for 5 huts in total
                        //Notes"  Made provision for 2 water Supplies
                        string EnNoOfPeople = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(1).StringCellValue;
                        string EnNoLiveAway = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(2).StringCellValue;
                        string EnListWhere0 = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(3).StringCellValue;
                        string EnListWhere1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(3).StringCellValue;
                        string EnListWhere2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(3).StringCellValue;
                        string EnListWhere3 = MyWorkbook.GetSheet("Environmental").GetRow(7).GetCell(3).StringCellValue;
                        string EnListWhere4 = MyWorkbook.GetSheet("Environmental").GetRow(8).GetCell(3).StringCellValue;
                        string EnFamilyLastVisit0 = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(4).StringCellValue;
                        string EnFamilyLastVisit1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(4).StringCellValue;
                        string EnFamilyLastVisit2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(4).StringCellValue;
                        string EnFamilyLastVisit3 = MyWorkbook.GetSheet("Environmental").GetRow(7).GetCell(4).StringCellValue;
                        string EnFamilyLastVisit4 = MyWorkbook.GetSheet("Environmental").GetRow(8).GetCell(4).StringCellValue;
                        string EnWhichClinic = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(5).StringCellValue;
                        string EnMainHutStructure = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(6).StringCellValue;
                        string EnMainHutTypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(7).StringCellValue;
                        string EnMainHutVentilation = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(8).StringCellValue;
                        string EnMainHutTotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(9).StringCellValue;
                        string EnHut2Structure = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(6).StringCellValue;
                        string EnHut2TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(7).StringCellValue;
                        string EnHut2Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(8).StringCellValue;
                        string EnHut2TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(9).StringCellValue;
                        string EnHut3Structure = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(6).StringCellValue;
                        string EnHut3TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(7).StringCellValue;
                        string EnHut3Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(8).StringCellValue;
                        string EnHut3TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(9).StringCellValue;
                        string EnHut4Structure = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(6).StringCellValue;
                        string EnHut4TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(7).StringCellValue;
                        string EnHut4Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(8).StringCellValue;
                        string EnHut4TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(9).StringCellValue;
                        string EnHut5Structure = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(6).StringCellValue;
                        string EnHut5TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(7).StringCellValue;
                        string EnHut5Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(8).StringCellValue;
                        string EnHut5TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(9).StringCellValue;
                        string EnNoSleepingInOneRoom = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(10).StringCellValue;
                        string EnNoOfStructures = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(11).StringCellValue;
                        string EnRainWaterCollection = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(12).StringCellValue;
                        string EnWaterSupply = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(13).StringCellValue;
                        string EnWaterSupply1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(13).StringCellValue;
                        string EnWalkingDistanceWater = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(14).StringCellValue;
                        string EnTreatWater = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(15).StringCellValue;
                        string EnHutElectricity = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(16).StringCellValue;
                        string EnFridge = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(17).StringCellValue;
                        string EnUseForCooking = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(18).StringCellValue;
                        string EnUseForCooking1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(18).StringCellValue;
                        string EnUseForCooking2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(18).StringCellValue;
                        string EnTypeToilet = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(19).StringCellValue;
                        string EnDisposeWaste = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(20).StringCellValue;
                        string EnDisposeWaste1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(20).StringCellValue;
                        string EnSourceIncomeHousehold = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(21).StringCellValue;
                        string EnFoodParcel = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(22).StringCellValue;
                        #endregion

                        #region General
                        string GenWeight = MyWorkbook.GetSheet("General").GetRow(4).GetCell(1).StringCellValue;
                        string GenHeight = MyWorkbook.GetSheet("General").GetRow(4).GetCell(2).StringCellValue;
                        string GenBMI = MyWorkbook.GetSheet("General").GetRow(4).GetCell(3).StringCellValue;
                        string GenCurrentOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(5).StringCellValue;
//.........这里部分代码省略.........

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public bool SaveTransactionsFromFile(string filename, string sheetName)
        {
            try
            {
                //Using Npoi instead of the office components
                ISheet sheet;
                if (filename.Substring(filename.LastIndexOf('.')).ToLower() == EXCEL_2007_EXTENSION)
                {
                    XSSFWorkbook workbook;
                    using (var file = new FileStream(filename, FileMode.Open, FileAccess.Read))
                    {
                        workbook = new XSSFWorkbook(file);
                    }
                    sheet = workbook.GetSheet(sheetName);
                }
                else
                {
                    HSSFWorkbook workbook;
                    using (var file = new FileStream(filename, FileMode.Open, FileAccess.Read))
                    {
                        workbook = new HSSFWorkbook(file);
                    }
                    sheet = workbook.GetSheet(sheetName);
                }

                var transactionsList = new List<dynamic>();
                for (int row = 1; row <= sheet.LastRowNum; row++)
                {
                    if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
                    {
                        var theRow = sheet.GetRow(row);
                        var transaction = new
                                              {
                                                  campana = theRow.GetCell(0).StringCellValue,
                                                  factura = theRow.GetCell(1).StringCellValue,
                                                  fecha = theRow.GetCell(2).DateCellValue,
                                                  monto = theRow.GetCell(3).NumericCellValue,
                                                  puntos = theRow.GetCell(4).NumericCellValue,
                                                  comision = theRow.GetCell(5).NumericCellValue,
                                                  vendedor = theRow.GetCell(6).StringCellValue,
                                              };
                        transactionsList.Add(transaction);

                    }
                }
                //string connectionString =
                //    string.Format(
                //        filename.Substring(filename.LastIndexOf('.')).ToLower() == EXCEL_2007_EXTENSION
                //            ? EXCEL_2007_CONNECTION_STRING
                //            : EXCEL_2005_CONNECTION_STRING, filename);

                //var adapter = new OleDbDataAdapter(string.Format(SELECT_ALL_QUERY, sheetName), connectionString);
                //var dataSet = new DataSet();

                //adapter.Fill(dataSet, DATA_TABLE_NAME);

                //var reportData = dataSet.Tables[DATA_TABLE_NAME].AsEnumerable();

                //var transactionsList =
                //    reportData.Where(y => y.Field<string>("Campaña") != null)
                //        .Select(
                //            x => new
                //            {
                //                campana = x.Field<string>("Campaña"),
                //                factura = x.Field<string>("Factura"),
                //                fecha = x.Field<DateTime>("Fecha"),
                //                monto = x.Field<double>("Monto"),
                //                puntos = x.Field<double>("Puntos"),
                //                comision = x.Field<double>("Comision"),
                //                vendedor = x.Field<string>("Vendedor"),
                //            }).
                //        ToList(); //o por nombre de columna.
                foreach (var individualTransaction in transactionsList)
                {
                    int cedNumber = GetCedNumberFromString(individualTransaction.vendedor);
                    var customer = _usersRepository.GetUserByIdentificationNumber(cedNumber);

                    var company = _companiesRepository.GetCompany(individualTransaction.campana);

                    var transaction = new Transaction
                                          {
                                              Amount = individualTransaction.monto,
                                              BillBarCode = individualTransaction.factura,
                                              UserId = customer.UserId,
                                              Points = (int) individualTransaction.puntos,
                                              TransactionDate = Convert.ToDateTime(individualTransaction.fecha),
                                              CompanyId = company.CompanyId,
                                              CreatetedAt = DateTime.Now,
                                              UpdatedAt = DateTime.Now,
                                              Comision = individualTransaction.comision,
                                          };
                    if (_transactionsRepository.SaveTransaction(transaction))
                    {
                        if (!DistributeTransactionCashback(transaction))
//.........这里部分代码省略.........

        public ActionResult DownloadAsExcel(string id)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                TempData["Message"] = "Unable to download file.  No file was selected. Please select a file and try again.";
                return RedirectToAction("Index");
            }

            try
            {
                var file = GetNamedFile(id);
                var created = file.TimeStamp;
                var filePathAndFilename = file.FilePath;
                var filename = file.FileNameLessExtension;

                var streamReader = new StreamReader(filePathAndFilename);

                var engine = new FileHelperEngine<FeederSystemFixedLengthRecord>();

                var result = engine.ReadStream(streamReader);

                var transactions = result.ToList();

                // Opening the Excel template...
                var templateFileStream = new FileStream(Server.MapPath(@"~\Files\RevisedScrubberWithoutData.xlsx"),
                    FileMode.Open, FileAccess.Read);

                // Getting the complete workbook...
                var templateWorkbook = new XSSFWorkbook(templateFileStream);

                // Getting the worksheet by its name...
                var sheet = templateWorkbook.GetSheet("Sheet1");

                // We need this so the date will be formatted correctly; otherwise, the date format gets all messed up.
                var dateCellStyle = templateWorkbook.CreateCellStyle();
                var format = templateWorkbook.CreateDataFormat();
                dateCellStyle.DataFormat = format.GetFormat("[$-809]m/d/yyyy;@");

                // Here's another to ensure we get a number with 2 decimal places:
                var twoDecimalPlacesCellStyle = templateWorkbook.CreateCellStyle();
                format = templateWorkbook.CreateDataFormat();
                twoDecimalPlacesCellStyle.DataFormat = format.GetFormat("#0.00");

                var boldFont = templateWorkbook.CreateFont();
                    boldFont.FontHeightInPoints = 11;
                    boldFont.FontName = "Calibri";
                    boldFont.Boldweight = (short)FontBoldWeight.Bold;

                var boldCellStyle = templateWorkbook.CreateCellStyle();
                boldCellStyle.SetFont(boldFont);

                var boldTotalAmountStyle = templateWorkbook.CreateCellStyle();
                boldTotalAmountStyle.DataFormat = twoDecimalPlacesCellStyle.DataFormat;
                boldTotalAmountStyle.SetFont(boldFont);

                var grandTotal = 0.0;
                var i = 0;
                foreach (var transaction in transactions)
                {
                    i++;
                    // Getting the row... 0 is the first row.
                    var dataRow = sheet.GetRow(i);
                    dataRow.CreateCell(0).SetCellValue(transaction.FiscalYear);
                    dataRow.CreateCell(1).SetCellValue(transaction.ChartNum);
                    dataRow.CreateCell(2).SetCellValue(transaction.Account);
                    dataRow.CreateCell(3).SetCellValue(transaction.SubAccount);
                    dataRow.CreateCell(4).SetCellValue(transaction.ObjectCode);
                    dataRow.CreateCell(5).SetCellValue(transaction.SubObjectCode);
                    dataRow.CreateCell(6).SetCellValue(transaction.BalanceType);
                    dataRow.CreateCell(7).SetCellValue(transaction.ObjectType.Trim());
                    dataRow.CreateCell(8).SetCellValue(transaction.FiscalPeriod);
                    dataRow.CreateCell(9).SetCellValue(transaction.DocumentType);
                    dataRow.CreateCell(10).SetCellValue(transaction.OriginCode);
                    dataRow.CreateCell(11).SetCellValue(transaction.DocumentNumber);
                    dataRow.CreateCell(12).SetCellValue(transaction.LineSequenceNumber);
                    dataRow.CreateCell(13).SetCellValue(transaction.TransactionDescription);

                    var transactionAmount = Convert.ToDouble(transaction.Amount.Trim());
                    grandTotal += transactionAmount;
                    var cell = dataRow.CreateCell(14);
                    cell.CellStyle = twoDecimalPlacesCellStyle;
                    cell.SetCellValue(transactionAmount);

                    dataRow.CreateCell(15).SetCellValue(transaction.DebitCreditCode.Trim());

                    cell = dataRow.CreateCell(16);
                    cell.CellStyle = dateCellStyle;
                    cell.SetCellValue(Convert.ToDateTime(transaction.TransactionDate));

                    dataRow.CreateCell(17).SetCellValue(transaction.OrganizationTrackingNumber);
                    dataRow.CreateCell(18).SetCellValue(transaction.ProjectCode);
                    dataRow.CreateCell(19).SetCellValue(transaction.OrganizationReferenceId.Trim());
                    dataRow.CreateCell(20).SetCellValue(transaction.ReferenceTypeCode.Trim());
                    dataRow.CreateCell(21).SetCellValue(transaction.ReferenceOriginCode.Trim());
                    dataRow.CreateCell(22).SetCellValue(transaction.ReferenceNumber.Trim());
                    dataRow.CreateCell(23).SetCellValue(transaction.ReversalDate.Trim());
                    dataRow.CreateCell(24).SetCellValue(transaction.TransactionEncumbranceUpdateCode.Trim());
                }

                if (transactions.Any())
//.........这里部分代码省略.........

    public override void Initialize(DateTime Start, DateTime End, IEnumerable<Catchment> Catchments)
    {

      base.Initialize(Start, End, Catchments);
      Dictionary<XYPoint, List<double>> Data = new Dictionary<XYPoint,List<double>>();

      XSSFWorkbook hssfwb;
      using (FileStream file = new FileStream(ExcelFile.FileName, FileMode.Open, FileAccess.Read))
      {
        hssfwb = new XSSFWorkbook(file);
      }


      List<IRow> DataRows = new List<IRow>();
      var sheet = hssfwb.GetSheet("Ndep_Tot");
      for (int row = 1; row <= sheet.LastRowNum; row++)
      {
        if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
        {
          DataRows.Add(sheet.GetRow(row));
        }
      }


      using (ShapeReader sr = new ShapeReader(Shapefile.FileName))
      {
        FirstYear = (int)DataRows.First().Cells[0].NumericCellValue;
        for (int i = 0; i < sr.Data.NoOfEntries; i++)
        {
          int icoor = sr.Data.ReadInt(i, "i");
          int jcoor = sr.Data.ReadInt(i, "j");

          XYPoint point = (XYPoint)sr.ReadNext(i);
          
          //Create the timestampseries and set unit to kg/m2/s;
          var data = DataRows.Where(v => (int)v.Cells[3].NumericCellValue == icoor & (int)v.Cells[4].NumericCellValue == jcoor).OrderBy(v => (int)v.Cells[0].NumericCellValue).Select(v => v.Cells[6].NumericCellValue / (365.0 * 86400.0 * 1.0e6)).ToList();


          if(data.Count()>0)
            Data.Add(point, data);

        }
      }


      foreach (var c in Catchments)
      {
        XYPolygon poly = null;

        if (c.Geometry is XYPolygon)
          poly = c.Geometry as XYPolygon;
        else if (c.Geometry is MultiPartPolygon)
          poly = ((MultiPartPolygon)c.Geometry).Polygons.First(); //Just use the first polygon

        double LakeArea = c.Lakes.Sum(l => l.Geometry.GetArea()); //Get the area of the lakes
        if (c.BigLake != null) //Add the big lake
          LakeArea += c.BigLake.Geometry.GetArea();
        
        if (poly != null)
        {
          var point = new XYPoint(poly.PlotPoints.First().Longitude, poly.PlotPoints.First().Latitude); //Take one point in the polygon
          var closestpoint = Data.Keys.Select(p => new Tuple<XYPoint, double>(p, p.GetDistance(point))).OrderBy(s => s.Item2).First().Item1;
          deposition.Add(c.ID, new List<double>(Data[closestpoint].Select(v=>v*LakeArea)));
        }

      }
      NewMessage("Initialized");
      



    }

        public static bool Import(string TargetFolder)
        {
            bool Success = true;

            string[] Files = Directory.GetFiles(TargetFolder);
            List<string> ErrorList = new List<string>();

            foreach (string CurrentFile in Files)
            {
                try
                {
                    XSSFWorkbook MyWorkbook = null;

                    using (FileStream file = new FileStream(CurrentFile, FileMode.Open, FileAccess.Read))
                    {
                        MyWorkbook = new XSSFWorkbook(file);

                        // Read file values here


#region Biographical

                        string BioChowName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(0).StringCellValue;
                        string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(1).StringCellValue;
                        string BioFirstName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(2).StringCellValue;
                        string BioSecondName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(3).StringCellValue;
                        string BioIDNumber = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(4).StringCellValue;
                        string BioGPSLatitude = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(5).StringCellValue;
                        string BioGPSLongitude = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(6).StringCellValue;
                        string BioArea = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(7).StringCellValue;
                        string BioClinic = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(8).StringCellValue;
#endregion

#region VisitDetails
                        // VD prefix used to identify variables associated with Visit Details tab
                        string VDVisitNum = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(0).StringCellValue;
                        string VDVisitDate = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(1).StringCellValue;
                        string VDNextVisitDate = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(2).StringCellValue;
                        string VDOutcome = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(4).StringCellValue;
                        string VDHPT = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(5).StringCellValue;
                        string VDDiabetes = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(6).StringCellValue;
                        string VDEpilepsy = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(7).StringCellValue;
                        string VDHIV = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(8).StringCellValue;
                        string VDTB = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(9).StringCellValue;
                        string VDMatHealth = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(10).StringCellValue;
                        string VDChildHealth = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(11).StringCellValue;
                        string VDOther = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(12).StringCellValue;
                        string VDODoorToDoor = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(13).StringCellValue;
#endregion

#region HyperTension

                        //Hyper prefix used to identify variables associated with Hypertension tab.
                        //HiEHRef = HiEH Referral
                        //ClinicRef = Clinic Referral
                        //AOT = Already On Treatment
                        string HyperDateOfVisit = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(0).StringCellValue;
                        
                        string Hyper_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(1).StringCellValue;
                        string Hyper_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(2).StringCellValue;
                        string Hyper_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(3).StringCellValue;
                        string Hyper_HiEHRef_OnMeds = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(4).StringCellValue;
                        string Hyper_HiEHRef_StartDate = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(5).StringCellValue;
                        string Hyper_HiEHRef_BPReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(6).StringCellValue;
                        string Hyper_HiEHRef_TodayReadingTop = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(7).StringCellValue;
                        string Hyper_HiEHRef_TodayReadingBottom = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(8).StringCellValue;
                        string Hyper_HiEHRef_ReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(9).StringCellValue;
                        string Hyper_HiEHRef_RefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(10).StringCellValue;
                        
                        string Hyper_ClinicRef_ReReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(12).StringCellValue;
                        string Hyper_ClinicRef_ReRefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(13).StringCellValue;
                        
                        string Hyper_AOT_FollowUpReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(15).StringCellValue;
                        
                        string Hyper_DoorToDoorReading_CheckReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(17).StringCellValue;

                        string Hyper_Medication = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(18).StringCellValue;
#endregion

#region Diabetes
                        //HiEHRef = HiEH Referral
                        //ClinicRef = Clinic Referral
                        //AOT = Already On Treatment
                        string DiabetesDateOfVisit = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(0).StringCellValue;

                        string Diabetes_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(1).StringCellValue;
                        string Diabetes_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(2).StringCellValue;
                        string Diabetes_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(3).StringCellValue;
                        string Diabetes_HiEHRef_OnMeds = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(4).StringCellValue;
                        string Diabetes_HiEHRef_StartDate = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(5).StringCellValue;
                        string Diabetes_HiEHRef_FollowUpTestReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(6).StringCellValue;
                        string Diabetes_HiEHRef_ReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(7).StringCellValue;
                        string Diabetes_HiEHRef_RefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(8).StringCellValue;

                        string Diabetes_ClinicRef_ReReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(10).StringCellValue;
                        string Diabetes_ClinicRef_ReRefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(11).StringCellValue;

                        string Diabetes_AOT_FollowUpReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(13).StringCellValue;

                        string Diabetes_DoorToDoorReading_CheckReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(15).StringCellValue;
//.........这里部分代码省略.........

        public string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleNumDTO> hnDTOList, string fileName)
        {
            if (hnDTOList == null)
            {
                return string.Empty;
            }
            string url = "http://blessing.wang/ExcelModel/numModel.xlsx";
            WebRequest request = WebRequest.Create(url);
            WebResponse response = request.GetResponse();
            Stream stream = response.GetResponseStream();

            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            ISheet sheet = workbook.GetSheet("寄出个数");

            IRow row = sheet.GetRow(1);
            ICell cell = row.GetCell(1);
            cell.SetCellValue(hnDTOList.Count + "个");
            row = sheet.GetRow(2);
            cell = row.GetCell(7);
            cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
            //设置单元格格式
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.BorderTop = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            for (int i = 0; i < hnDTOList.Count; i++)
            {
                row = sheet.CreateRow(5 + i);
                cell = row.CreateCell(0);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].ContractNo);
                cell = row.CreateCell(1);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].Student);
                cell = row.CreateCell(2);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].Application);
                cell = row.CreateCell(3);
                cell.CellStyle = style;
                cell.SetCellValue("□拒");
                cell = row.CreateCell(4);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].Visa);
                cell = row.CreateCell(5);
                cell.CellStyle = style;
                cell.SetCellValue("□拒");
                cell = row.CreateCell(6);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].Author);
                cell = row.CreateCell(7);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].FirstFour);
                cell = row.CreateCell(8);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].Doctor);
                cell = row.CreateCell(9);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].Cooperation);
                cell = row.CreateCell(10);
                cell.CellStyle = style;
                cell.SetCellValue(hnDTOList[i].Note);
            }

            style = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 11;
            style.SetFont(font);

            row = sheet.CreateRow(5 + hnDTOList.Count);
            cell = row.CreateCell(0);
            cell.SetCellValue("编制人签名:");
            cell.CellStyle = style;

            cell = row.CreateCell(2);
            cell.SetCellValue("经理审核签名：");
            cell.CellStyle = style;

            cell = row.CreateCell(6);
            cell.SetCellValue("总经理/分管总裁批准签字：");
            cell.CellStyle = style;

            cell = row.CreateCell(9);
            cell.SetCellValue("保持：人力资源中心");
            cell.CellStyle = style;

            row = sheet.CreateRow(6 + hnDTOList.Count);
            cell = row.CreateCell(0);
            cell.SetCellValue("1．《文案工作进度（量化）日报单》汇总动态记录PM-16《文案工作量化日报表》；此单必须机打，手写量化将直接作废；");
            cell.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));

            row = sheet.CreateRow(7 + hnDTOList.Count);
            cell = row.CreateCell(0);
            cell.SetCellValue("2．本报单请于16:00之前申报， □确认√确认填报项。请勿集中中延期申报");
            cell.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
//.........这里部分代码省略.........

        private void button2_Click(object sender, EventArgs e)
        {
            string ruta = this.buscarDoc();
            ConexionPostgres conn = new ConexionPostgres();
            FileStream fs;
            try
            {
                fs = new FileStream(ruta, FileMode.Open, FileAccess.Read);
            }
            catch
            {
                MessageBox.Show("El archivo está siendo usado por otro programa. Asegurese de haber seleccionado el archivo correcto.");
                return;
            }
            XSSFWorkbook wb = new XSSFWorkbook(fs);
            string nombreHoja = "";
            for (int i = 0; i < wb.Count; i++)
            {
                nombreHoja = wb.GetSheetAt(i).SheetName;
            }
            XSSFSheet sheet = (XSSFSheet)wb.GetSheet(nombreHoja);
            try
            {
                for (int row = 1; row <= sheet.LastRowNum; row++)
                {
                    if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
                    {
                        var fila = sheet.GetRow(row);
                        var nit = fila.GetCell(0).ToString();
                        var numero_unidad = fila.GetCell(1).ToString();
                        var nombre_completo = fila.GetCell(2).ToString();
                        var coeficiente = fila.GetCell(3).ToString().Replace(",", ".");
                        var documento = fila.GetCell(4).ToString();
                        var cadenaSql = string.Format(
                            @"INSERT INTO modelo.unidad_residencial
                            (
                            nit,
                            numero_unidad,
                            nombre_completo,
                            coeficiente,
                            documento
                            )
                            VALUES
                            (
                            '{0}',
                            '{1}',
                            '{2}',
                            '{3}',
                            '{4}'
                            );",
                                nit,
                                numero_unidad,
                                nombre_completo,
                                coeficiente,
                                documento
                            );
                        var resultado = conn.registrar(cadenaSql);
                        if (!resultado)//Falló la consulta
                        {
                            MessageBox.Show("El registro NIT:" + nit
                                + ", Número Unidad:" + numero_unidad
                                + ", Nombre Completo:" + nombre_completo
                                + ", Coeficiente:" + coeficiente
                                + ", Número Documento:" + documento
                                + " ,no pudo ser completado de manera exitosa, revise los datos (están mal o ya existen o se encontro elemento en blanco).");
                            MessageBox.Show(cadenaSql);
                            return;
                        }
                    }
                    else
                    {
                        MessageBox.Show("realizado");
                    }

                }
                MessageBox.Show("Datos cargados correctamente.");
            }
            catch
            {
                MessageBox.Show("Algo pasó con la carga de archivos, asegúrese de que ha cargado el archivo correcto con datos válidos.");
            }
            //Se cierra el archivo
            fs.Close();
        }

 public void SetUp()
 {
     if (workbook == null)
     {
         Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(SS.FILENAME);
         OPCPackage pkg = OPCPackage.Open(is1);
         workbook = new XSSFWorkbook(pkg);
         sheet = workbook.GetSheet(SS.TEST_SHEET_NAME);
     }
     _functionFailureCount = 0;
     _functionSuccessCount = 0;
     _EvaluationFailureCount = 0;
     _EvaluationSuccessCount = 0;
 }