Пример #1
0
        /// <summary>
        /// Excel导出返回文件路径
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="title"></param>
        /// <param name="sheetName"></param>
        /// <param name="dt"></param>
        /// <param name="FilePath"></param>
        /// <param name="ext"></param>
        /// <returns></returns>
        public static string ExcelFilePath(string[] fileName, string[] title, string sheetName, DataTable dt, string FilePath, string ext)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
            NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
            for (int i = 0; i < title.Length; i++)
            {
                row.CreateCell(i).SetCellValue(title[i]);
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < fileName.Length; j++)
                    {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
                    }
                }
            }

            if (!Directory.Exists(HttpContext.Current.Server.MapPath(FilePath)))
            {
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath(FilePath));
            }
            FilePath += "/" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000) + Path.GetExtension(ext);
            FileStream fs2 = File.Create(HttpContext.Current.Server.MapPath(FilePath));

            book.Write(fs2);
            fs2.Close();


            return(FilePath);
            //写入到客户端
        }
Пример #2
0
        private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet)
        {
            SetCellRangeAddress(sheet, 0, 0, 0, 1);
            SetCellRangeAddress(sheet, 0, 0, 3, 4);
            SetCellRangeAddress(sheet, 0, 0, 6, 7);
            SetCellRangeAddress(sheet, 0, 0, 9, 10);
            SetCellRangeAddress(sheet, 0, 0, 12, 14);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("输入功率标定");
            row.CreateCell(3).SetCellValue("输出功率标定");
            row.CreateCell(6).SetCellValue("反射功率标定");
            row.CreateCell(9).SetCellValue("ALC功率标定");
            row.CreateCell(12).SetCellValue("衰减补偿");
            row.GetCell(0).CellStyle  = GetCellStyle(book);
            row.GetCell(3).CellStyle  = GetCellStyle(book);
            row.GetCell(6).CellStyle  = GetCellStyle(book);
            row.GetCell(9).CellStyle  = GetCellStyle(book);
            row.GetCell(12).CellStyle = GetCellStyle(book);

            row = sheet.CreateRow(1);
            for (int i = 0; i < 4; i++)
            {
                row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压");
                row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点");
                row.GetCell(3 * i).CellStyle     = GetCellStyle(book);
                row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book);
            }
            row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值");
            row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值");
            row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值");
            row.GetCell(12).CellStyle = GetCellStyle(book);
            row.GetCell(13).CellStyle = GetCellStyle(book);
            row.GetCell(14).CellStyle = GetCellStyle(book);
        }
        public static HSSFWorkbook CreateExecl(List <string> title, List <AppUserPerson> list)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            //row1.RowStyle.FillBackgroundColor = "";
            int i = 0;

            foreach (var item in title)
            {
                row1.CreateCell(i).SetCellValue(item);
                i++;
            }
            //将数据逐步写入sheet1各个行
            for (int j = 0; j < list.Count; j++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
                rowtemp.CreateCell(0).SetCellValue(list[j].adnm);
                rowtemp.CreateCell(1).SetCellValue(list[j].userName);
                rowtemp.CreateCell(2).SetCellValue(list[j].phone);
            }
            return(book);
        }
Пример #4
0
 public static MemoryStream ExportExcel(DataTable dt)
 {
     NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
     //添加一个sheet
     NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
     //给sheet1添加第一行的头部标题
     NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
     //row1.RowStyle.FillBackgroundColor = "";
     for (int i = 0; i < dt.Columns.Count; i++)
     {
         row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
     }
     //将数据逐步写入sheet1各个行
     for (int i = 0; i < dt.Rows.Count; i++)
     {
         NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
         for (int j = 0; j < dt.Columns.Count; j++)
         {
             rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
         }
     }
     // 写入到客户端
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     ms.Seek(0, SeekOrigin.Begin);
     return(ms);
 }
Пример #5
0
        public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");

            IRow  row  = sheet.CreateRow(0);
            ICell cell = row.CreateCell((short)0);

            cell.CellFormula = ("SUM(A32769:A32770)");

            // put some values in the cells to make the evaluation more interesting
            sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
            sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);

            //HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            NPOI.SS.UserModel.CellValue result;
            try
            {
                result = fe.Evaluate(cell);
            }
            catch (Exception e)
            {
                if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
                {
                    Assert.Fail("Identifed bug 44539");
                }
                throw;
            }
            Assert.AreEqual(NPOI.SS.UserModel.CellType.Numeric, result.CellType);
            Assert.AreEqual(42.0, result.NumberValue, 0.0);
        }
Пример #6
0
        public void NpoiExcel(DataTable dt, string title, string s)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("Sheet1");

            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
            ICellStyle             style     = book.CreateCellStyle();

            style.Alignment         = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            string[] ss = s.Split(',');
            for (int i = 0; i < ss.Length; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(ss[i]);
            }
            for (int I = 0; I <= dt.Rows.Count - 1; I++)
            {
                HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    //double a = 0;
                    //string b = "";
                    //try
                    //{
                    //    a = Convert.ToDouble(dt.Rows[I][j].ToString());
                    //    row2.CreateCell(j).SetCellValue(a);
                    //    sheet.SetColumnWidth(j, 20 * 150);
                    //}
                    //catch
                    //{
                    //    b = dt.Rows[I][j].ToString();
                    //    row2.CreateCell(j).SetCellValue(b);
                    //    sheet.SetColumnWidth(j, 20 * 150);
                    //}
                    string DgvValue = dt.Rows[I][j].ToString();
                    if (j == 8 || j == 9)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue));
                    }
                    else
                    {
                        row2.CreateCell(j).SetCellValue(DgvValue);
                    }

                    sheet.SetColumnWidth(j, 20 * 150);
                }
            }
            MemoryStream ms = new MemoryStream();

            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
Пример #7
0
        void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
        {
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

            // 添加表头
            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.Caption);
                index++;
            }

            // 添加数据
            for (int i = 0; i < dt.Rows.Count; 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++;
                }
            }
        }
Пример #8
0
        public static Stream RenderDataTableToExcel(string[] headers, DataTable SourceTable)
        {
            MemoryStream ms = new MemoryStream();

            NPOI.HSSF.UserModel.HSSFWorkbook workbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet     = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow           headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                headerRow.CreateCell(i).SetCellValue(headers[i]);
            }
            int rowIndex = 1;

            foreach (DataRow row in SourceTable.Rows)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.CreateCell(0).SetCellValue(row["ItemName"].ToString());
                dataRow.CreateCell(1).SetCellValue(row["Count"].ToString());
                dataRow.CreateCell(2).SetCellValue(row["Percent"].ToString());
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet       = null;
            headerRow   = null;
            workbook    = null;
            return(ms);
        }
        public byte[] GetExcel(AddmissionContext _context)
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("录取学生表");
            var students = _context.Admissions.Include(a => a.AcceptedStudent).Where(a => a.Uname == userName).ToList();

            NPOI.SS.UserModel.IRow row;
            row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("考号");
            row.CreateCell(1).SetCellValue("姓名");
            row.CreateCell(2).SetCellValue("总分");
            row.CreateCell(3).SetCellValue("毕业学校");
            row.CreateCell(4).SetCellValue("录取方式");
            int count = 1;//行号游标

            foreach (var student in students)
            {
                row = sheet.CreateRow(count);
                row.CreateCell(0).SetCellValue(student.Sid);
                row.CreateCell(1).SetCellValue(student.AcceptedStudent.Sname);
                row.CreateCell(2).SetCellValue(student.AcceptedStudent.TotalGrade);
                row.CreateCell(3).SetCellValue(student.AcceptedStudent.GraduateSchool);
                row.CreateCell(4).SetCellValue(student.AdmissionMethod);
                count++;
            }
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            wb.Write(ms);
            return(ms.ToArray());
        }
Пример #10
0
        /**
         * All multi-binomial operator Tests use this to Create a worksheet with a
         * huge set of x operator y formulas.  Next we call BinomialVerify and verify
         * that they are all how we expect.
         */
        private static void BinomialOperator(String operator1)
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow  r = null;
            ICell c = null;

            //get our minimum values
            r             = s.CreateRow(0);
            c             = r.CreateCell(1);
            c.CellFormula = (1 + operator1 + 1);

            for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
            {
                r = s.CreateRow(x);

                for (int y = 1; y < 256 && y > 0; y++)
                {
                    c             = r.CreateCell(y);
                    c.CellFormula = ("" + x + operator1 + y);
                }
            }

            //make sure we do the maximum value of the Int operator
            if (s.LastRowNum < short.MaxValue)
            {
                r             = s.GetRow(0);
                c             = r.CreateCell(0);
                c.CellFormula = ("" + short.MaxValue + operator1 + short.MaxValue);
            }
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            BinomialVerify(operator1, wb);
        }
Пример #11
0
 /// <summary>
 /// 服务商结算订单导出
 /// </summary>
 /// <param name="fileName"></param>
 /// <param name="title"></param>
 /// <param name="sheetName"></param>
 /// <param name="dt"></param>
 public static System.IO.MemoryStream ExcelSettlementOrder(string[] fileName, string[] title, string sheetName, DataTable dt)
 {
     NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
     NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet(sheetName);
     NPOI.SS.UserModel.IRow           row   = sheet.CreateRow(0);
     for (int i = 0; i < title.Length; i++)
     {
         row.CreateCell(i).SetCellValue(title[i]);
     }
     if (dt != null && dt.Rows.Count > 0)
     {
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
             for (int j = 0; j < fileName.Length; j++)
             {
                 row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
             }
         }
     }
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     //ms.Seek(0, SeekOrigin.Begin);
     ms.Flush();
     ms.Position = 0;//流位置归零
     return(ms);
     //写入到客户端
 }
Пример #12
0
        private static void floatTest(String operator1)
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow  r = null;
            ICell c = null;

            //get our minimum values

            r             = s.CreateRow(0);
            c             = r.CreateCell(1);
            c.CellFormula = ("" + float.MinValue + operator1 + float.MinValue);

            for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
            {
                r = s.CreateRow(x);

                for (int y = 1; y < 256 && y > 0; y = (short)(y + 2))
                {
                    c             = r.CreateCell(y);
                    c.CellFormula = ("" + x + "." + y + operator1 + y + "." + x);
                }
            }
            if (s.LastRowNum < short.MaxValue)
            {
                r             = s.CreateRow(0);
                c             = r.CreateCell(0);
                c.CellFormula = ("" + float.MaxValue + operator1 + float.MaxValue);
            }
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            floatVerify(operator1, wb);
        }
Пример #13
0
        public void TestDateFormulas()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s = wb.CreateSheet("TestSheet1");
            IRow  r = null;
            ICell c = null;

            r = s.CreateRow(0);
            c = r.CreateCell(0);

            NPOI.SS.UserModel.ICellStyle cellStyle = wb.CreateCellStyle();
            cellStyle.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"));
            c.SetCellValue(new DateTime());
            c.CellStyle = (cellStyle);

            // Assert.AreEqual("Checking hour = " + hour, date.GetTime().GetTime(),
            //              NPOI.SS.UserModel.DateUtil.GetJavaDate(excelDate).GetTime());

            for (int k = 1; k < 100; k++)
            {
                r             = s.CreateRow(k);
                c             = r.CreateCell(0);
                c.CellFormula = ("A" + (k) + "+1");
                c.CellStyle   = cellStyle;
            }

            HSSFTestDataSamples.WriteOutAndReadBack(wb);
        }
Пример #14
0
 /// <summary>
 /// 批量导出需要导出的列表
 /// </summary>
 /// <returns></returns>
 public static MemoryStream GetMemoryStream(DataTable source, List <string> caption = null)
 {
     //创建Excel文件的对象
     NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
     NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1");
     //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了
     NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
     if (caption == null)
     {
         caption = source.Columns.Cast <DataColumn>().ToList().Select(item => item.Caption).ToList();
     }
     for (int i = 0; i < caption.Count; i++)
     {
         var element = caption[i];
         row1.CreateCell(i).SetCellValue(element);
     }
     //将数据逐步写入sheet1各个行
     for (int i = 0; i < source.Rows.Count; i++)
     {
         var dataRow = source.Rows[i];
         NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
         for (int j = 0; j < caption.Count; j++)
         {
             var keyValue = caption[j];
             rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
         }
     }
     // 写入到客户端
     System.IO.MemoryStream ms = new System.IO.MemoryStream();
     book.Write(ms);
     ms.Seek(0, SeekOrigin.Begin);
     return(ms);
     //return File(ms, "application/vnd.ms-excel", fileName);
 }
Пример #15
0
        public void TestDataStyle()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet     s  = wb.CreateSheet();
            NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
            IRow row = s.CreateRow((short)0);

            // with Date:
            cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
            row.RowStyle  = (cs);
            row.CreateCell(0);


            // with Calendar:
            row           = s.CreateRow((short)1);
            cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
            row.RowStyle  = (cs);
            row.CreateCell(0);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            SanityChecker sanityChecker = new SanityChecker();

            sanityChecker.CheckHSSFWorkbook(wb);

            Assert.AreEqual(1, s.LastRowNum, "LAST ROW ");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
        }
Пример #16
0
        /// <summary>
        /// 批量导出本校第一批派位学生
        /// </summary>
        /// <returns></returns>
        public FileResult ExportStu2()
        {
            // schoolname = "401";
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            List <Staff> listRainInfo = db.Staffs.ToList();

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("电脑号");
            row1.CreateCell(1).SetCellValue("姓名");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < listRainInfo.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].StaffNumber.ToString());
                rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].Name.ToString());
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls"));
        }
Пример #17
0
        public void NpoiExcel(DataTable dt, string title)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book      = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet     = book.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow           headerrow = sheet.CreateRow(0);
            ICellStyle style = book.CreateCellStyle();

            style.Alignment         = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = headerrow.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            for (int I = 0; I <= dt.Rows.Count - 1; I++)
            {
                HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    string DgvValue = dt.Rows[I][j].ToString();
                    row2.CreateCell(j).SetCellValue(DgvValue);
                    sheet.SetColumnWidth(j, 20 * 150);
                }
            }
            MemoryStream ms = new MemoryStream();

            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
            Response.BinaryWrite(ms.ToArray());
            Response.End();
            book = null;
            ms.Close();
            ms.Dispose();
        }
Пример #18
0
        public static void ExcelOut(DataTable dt, string fullXlsFilePath)
        {
            //创建Excel文件的对象
            NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1  = book.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow   headRow = sheet1.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                headRow.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
            }

            //将数据逐步写入sheet1各个行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //加上设置大小下载下来的.xlsx文件打开时才没有错误
            FileStream fileStream = new FileStream(fullXlsFilePath, FileMode.Create, FileAccess.Write);

            book.Write(fileStream);//调用这个后会关于文件流,在HSSFWorkbook不会关闭所以在处理时应注意
            FileStream fs       = new FileStream(fullXlsFilePath, FileMode.Open, FileAccess.Read);
            long       fileSize = fs.Length;

            //加上设置大小下载下来的.xlsx文件打开时才没有错误
            HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
            byte[] fileBuffer = new byte[fileSize]; fs.Read(fileBuffer, 0, (int)fileSize); HttpContext.Current.Response.BinaryWrite(fileBuffer); fs.Close();
        }
Пример #19
0
        protected static MemoryStream GetMS(List <FishEntity.CompanyEntity> list)
        {
            MemoryStream ms = new MemoryStream();

            NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet    sheet    = workbook.CreateSheet("sheet1");

            ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.HeightInPoints = 26;

            CreateCell(row, 0, "客户市场需求预测表", CellType.String, cellStyle);
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12));
            row       = sheet.CreateRow(1);
            cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
            CreateCell(row, 0, "客户编号", CellType.String, cellStyle);
            CreateCell(row, 1, "客户名称", CellType.String, cellStyle);
            CreateCell(row, 2, "类别", CellType.String, cellStyle);
            CreateCell(row, 3, "综合等级", CellType.String, cellStyle);
            CreateCell(row, 4, "需求量等级", CellType.String, cellStyle);
            CreateCell(row, 5, "活跃程度", CellType.String, cellStyle);
            CreateCell(row, 6, "忠诚度", CellType.String, cellStyle);
            CreateCell(row, 7, "主要产品", CellType.String, cellStyle);
            CreateCell(row, 8, "业务员", CellType.String, cellStyle);
            CreateCell(row, 9, "联系人", CellType.String, cellStyle);
            CreateCell(row, 10, "最近联系日期", CellType.String, cellStyle);
            CreateCell(row, 11, "最近周预估", CellType.String, cellStyle);
            CreateCell(row, 12, "最近月预估", CellType.String, cellStyle);

            if (list != null && list.Count > 0)
            {
                int rowidx = 1;
                foreach (FishEntity.CompanyEntity model in list)
                {
                    #region row
                    rowidx++;
                    row = sheet.CreateRow(rowidx);
                    CreateCell(row, 0, model.code);
                    CreateCell(row, 1, model.fullname);
                    CreateCell(row, 2, model.type);
                    CreateCell(row, 3, model.generallevel);
                    CreateCell(row, 4, model.requiredlevel);
                    CreateCell(row, 5, model.managestandard);
                    CreateCell(row, 6, model.activelevel);
                    CreateCell(row, 7, model.products);
                    CreateCell(row, 8, model.salesman);
                    CreateCell(row, 9, model.linkman);
                    CreateCell(row, 10, model.currentlink);
                    CreateCell(row, 11, model.currentweekestimate);
                    CreateCell(row, 12, model.currentmonthestimate);
                    # endregion
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            return(ms);
        }
Пример #20
0
        public void TestSheetFunctions()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s = wb.CreateSheet("A");
            IRow  r = null;
            ICell c = null;

            r = s.CreateRow(0);
            c = r.CreateCell(0); c.SetCellValue(1);
            c = r.CreateCell(1); c.SetCellValue(2);

            s = wb.CreateSheet("B");
            r = s.CreateRow(0);
            c = r.CreateCell(0); c.CellFormula = ("AVERAGE(A!A1:B1)");
            c = r.CreateCell(1); c.CellFormula = ("A!A1+A!B1");
            c = r.CreateCell(2); c.CellFormula = ("A!$A$1+A!$B1");

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            s = wb.GetSheet("B");
            r = s.GetRow(0);
            c = r.GetCell(0);
            Assert.IsTrue(("AVERAGE(A!A1:B1)").Equals(c.CellFormula), "expected: AVERAGE(A!A1:B1) got: " + c.CellFormula);
            c = r.GetCell(1);
            Assert.IsTrue(("A!A1+A!B1").Equals(c.CellFormula), "expected: A!A1+A!B1 got: " + c.CellFormula);
        }
Пример #21
0
        /// <summary>
        /// 批量导出需要导出的列表
        /// </summary>
        /// <returns></returns>
        public static MemoryStream ExportMemory(DataTable source, List <string> caption)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1");
            //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            for (int i = 0; i < caption.Count; i++)
            {
                var element = caption[i];
                row1.CreateCell(i).SetCellValue(element);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < source.Rows.Count; i++)
            {
                var dataRow = source.Rows[i];
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                for (int j = 0; j < caption.Count; j++)
                {
                    var keyValue = caption[j];
                    rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            DateTime dt       = DateTime.Now;
            string   dateTime = dt.ToString("yyMMddHHmmssfff");
            string   fileName = "查询结果" + dateTime + ".xls";

            return(ms);
            //return File(ms, "application/vnd.ms-excel", fileName);
        }
Пример #22
0
        public void NpoiExcel(System.Data.DataTable dt, string file)
        {
            try
            {
                double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000;
                int    sheetCount    = (int)(Math.Ceiling(sheetCountdbl));


                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

                for (int c = 0; c < sheetCount; c++)
                {
                    string sheetname = "Sheet" + (c + 1).ToString();

                    NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);

                    NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
                    ICellStyle             style     = book.CreateCellStyle();
                    style.Alignment         = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = headerrow.CreateCell(i);
                        cell.CellStyle = style;
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                    }

                    int js = 0;//计数
                    //表内容
                    for (int i = c * 60000; i < dt.Rows.Count; i++)
                    {
                        if (js > 59999)
                        {
                            break;
                        }
                        IRow row = sheet.CreateRow(js + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row.CreateCell(j);
                            cell.CellStyle = style;
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }

                        js++;
                    }
                }


                FileStream fs = File.Create(file);
                book.Write(fs);
                fs.Flush();
                fs.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
Пример #23
0
        protected static MemoryStream GetMS(List <FishEntity.CallRecordsEntity> list)
        {
            MemoryStream ms = new MemoryStream();

            NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet    sheet    = workbook.CreateSheet("sheet1");

            ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);

            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
            row.HeightInPoints = 26;

            CreateCell(row, 0, "通话记录表", CellType.String, cellStyle);
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12));
            row       = sheet.CreateRow(1);
            cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
            CreateCell(row, 0, "记录单号", CellType.String, cellStyle);
            CreateCell(row, 1, "客户名称", CellType.String, cellStyle);
            CreateCell(row, 2, "联系人", CellType.String, cellStyle);
            CreateCell(row, 3, "移动电话", CellType.String, cellStyle);
            CreateCell(row, 4, "固定电话", CellType.String, cellStyle);
            CreateCell(row, 5, "客户等级", CellType.String, cellStyle);
            CreateCell(row, 6, "日期", CellType.String, cellStyle);
            CreateCell(row, 7, "沟通内容", CellType.String, cellStyle);
            CreateCell(row, 8, "品质要求", CellType.String, cellStyle);
            CreateCell(row, 9, "主要产品", CellType.String, cellStyle);
            CreateCell(row, 10, "估计周用量", CellType.String, cellStyle);
            CreateCell(row, 11, "估计月用量", CellType.String, cellStyle);
            CreateCell(row, 12, "地址", CellType.String, cellStyle);
            if (list != null && list.Count > 0)
            {
                int rowidx = 1;
                foreach (FishEntity.CallRecordsEntity model in list)
                {
                    #region row
                    rowidx++;
                    row = sheet.CreateRow(rowidx);
                    CreateCell(row, 0, model.code);
                    CreateCell(row, 1, model.customer);
                    CreateCell(row, 2, model.linkman);
                    CreateCell(row, 3, model.mobile);
                    CreateCell(row, 4, model.telephone);
                    CreateCell(row, 5, model.customerlevel);
                    CreateCell(row, 6, model.currentdate.Value.ToString("yyyy-MM-dd"));
                    CreateCell(row, 7, model.communicatecontent);
                    CreateCell(row, 8, model.requiredquantity);
                    CreateCell(row, 9, model.products);
                    CreateCell(row, 10, model.weekestimate);
                    CreateCell(row, 11, model.monthestimate);
                    CreateCell(row, 12, model.address);
                    # endregion
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            return(ms);
        }
Пример #24
0
 public FileResult ShunFengExcel()
 {
     try
     {
         //创建Excel文件的对象
         NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
         //添加一个sheet
         NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
         ExamUserInfoModel        models = new ExamUserInfoModel();
         models.GetUserInfo(null);
         //获取list数据
         var tlst = models.ListUserInfo;
         //给sheet1添加第一行的头部标题
         NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
         row1.CreateCell(0).SetCellValue("工号");
         row1.CreateCell(1).SetCellValue("姓名");
         row1.CreateCell(2).SetCellValue("入职日期");
         row1.CreateCell(3).SetCellValue("职等");
         row1.CreateCell(4).SetCellValue("职位");
         row1.CreateCell(5).SetCellValue("本职等对应技能等级");
         row1.CreateCell(6).SetCellValue("已经考取技能等级");
         row1.CreateCell(7).SetCellValue("最近一次理论考试成绩");
         row1.CreateCell(8).SetCellValue("最近一次通过理论时间");
         row1.CreateCell(9).SetCellValue("最近一次实践成绩");
         row1.CreateCell(10).SetCellValue("最近一次实践考核通过时间");
         row1.CreateCell(11).SetCellValue("最高可考技能等级");
         row1.CreateCell(12).SetCellValue("可申请技能等级");
         row1.CreateCell(13).SetCellValue("最近一次绩效成绩要求");
         //将数据逐步写入sheet1各个行
         for (int i = 0; i < tlst.Count; i++)
         {
             NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
             rowtemp.CreateCell(0).SetCellValue(tlst[i].UserCode);                          //工号
             rowtemp.CreateCell(1).SetCellValue(tlst[i].UserName);                          //姓名
             rowtemp.CreateCell(2).SetCellValue(tlst[i].EntryDate.ToString());              //入职日期
             rowtemp.CreateCell(3).SetCellValue(tlst[i].RankName);                          //职等
             rowtemp.CreateCell(4).SetCellValue(tlst[i].PostName);                          //职位
             rowtemp.CreateCell(5).SetCellValue(tlst[i].SkillLevel);                        //本职等对应技能等级
             rowtemp.CreateCell(6).SetCellValue(tlst[i].CurrentSkillLevel);                 //已经考取技能等级
             rowtemp.CreateCell(7).SetCellValue(tlst[i].ExamScore);                         //最近一次理论考试成绩
             rowtemp.CreateCell(8).SetCellValue(tlst[i].LastExamTime.ToString());           //最近一次通过理论时间
             rowtemp.CreateCell(9).SetCellValue(tlst[i].TheoreticalAchievement.ToString()); //最近一次实践成绩
             rowtemp.CreateCell(10).SetCellValue(tlst[i].PracticeTime.ToString());          //最近一次实践考核通过时间
             rowtemp.CreateCell(11).SetCellValue(tlst[i].HighestTestSkill);                 //最高可考技能等级
             rowtemp.CreateCell(12).SetCellValue(tlst[i].ApplicationLevel);                 //可申请技能等级
             rowtemp.CreateCell(13).SetCellValue(tlst[i].Achievement);                      //最近一次绩效成绩要求
         }
         // 写入到客户端
         System.IO.MemoryStream ms = new System.IO.MemoryStream();
         book.Write(ms);
         ms.Seek(0, SeekOrigin.Begin);
         return(File(ms, "application/vnd.ms-excel", "人员信息" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
     }
     catch (Exception)
     {
         throw;
     }
 }
Пример #25
0
        public FileResult OmitCheckCountExportExcel()
        {
            PatrolRouteStat_SW sw = new PatrolRouteStat_SW();

            sw.orgNo     = Request.Params["BYORGNO"];
            sw.TopORGNO  = Request.Params["BYORGNO"];
            sw.DateBegin = Request.Params["TIMEBegin"];
            sw.DateEnd   = Request.Params["TIMEEnd"];
            var list = HUReportCls.getPatrolRouteStatModel(sw);

            NPOI.HSSF.UserModel.HSSFWorkbook book   = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet         sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
            sheet1.IsPrintGridlines = true;                                       //打印时显示网格线
            sheet1.DisplayGridlines = true;                                       //查看时显示网格线
            sheet1.SetColumnWidth(0, 30 * 256);
            sheet1.SetColumnWidth(1, 10 * 256);
            sheet1.SetColumnWidth(2, 10 * 256);
            sheet1.SetColumnWidth(3, 10 * 256);
            sheet1.SetColumnWidth(4, 10 * 256);
            IRow row = sheet1.CreateRow(0);

            row.CreateCell(0).SetCellValue("未巡统计表");
            row.GetCell(0).CellStyle = getCellStyleTitle(book);
            row = sheet1.CreateRow(1);
            row.CreateCell(0).SetCellValue("单位/姓名");
            row.CreateCell(1).SetCellValue("应巡");
            row.CreateCell(2).SetCellValue("已巡");
            row.CreateCell(3).SetCellValue("未巡");
            row.CreateCell(4).SetCellValue("完成率");
            row.GetCell(0).CellStyle = getCellStyleHead(book);
            row.GetCell(1).CellStyle = getCellStyleHead(book);
            row.GetCell(2).CellStyle = getCellStyleHead(book);
            row.GetCell(3).CellStyle = getCellStyleHead(book);
            row.GetCell(4).CellStyle = getCellStyleHead(book);
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));

            int rowI = 0;

            foreach (var v in list)
            {
                row = sheet1.CreateRow(rowI + 2);

                row.CreateCell(0).SetCellValue(v.ORGName);
                row.CreateCell(1).SetCellValue(v.PointCount);
                row.CreateCell(2).SetCellValue(v.PointCount0);
                row.CreateCell(3).SetCellValue(v.PointCount1);
                row.CreateCell(4).SetCellValue(v.PointCount2);
                rowI++;
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";

            return(File(ms, "application/vnd.ms-excel", fileName));
        }
Пример #26
0
        public void TestShiftRow0()
        {
            HSSFWorkbook b = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s = b.CreateSheet();
            s.CreateRow(0).CreateCell(0).SetCellValue("TEST1");
            s.CreateRow(3).CreateCell(0).SetCellValue("TEST2");
            s.ShiftRows(0, 4, 1);
        }
Пример #27
0
        public FileResult Export(Pagination rq, AdminCredential User)
        {
            #region 报表导出
            var report = service.Get(rq.vid).Data;
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            ICellStyle style = workbook.CreateCellStyle();
            style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
            style.FillPattern         = FillPattern.SolidForeground;
            style.BorderTop           = BorderStyle.Thin;
            style.BorderLeft          = BorderStyle.Thin;
            style.BorderRight         = BorderStyle.Thin;
            style.BorderBottom        = BorderStyle.Thin;
            style.Alignment           = HorizontalAlignment.Center;
            style.VerticalAlignment   = VerticalAlignment.Center;

            IFont font = workbook.CreateFont();       //创建一个字体样式对象
            font.FontHeightInPoints = 10;             //字体大小
            font.FontName           = "宋体";           //和excel里面的字体对应
            font.Boldweight         = short.MaxValue; //字体加粗
            font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
            style.SetFont(font);                      //将字体样式赋给样式对象

            NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(report.Name);
            NPOI.SS.UserModel.IRow   row1   = sheet1.CreateRow(0);
            row1.Height = 430;

            var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(service.GetReportColumns(rq.vid.Value));
            int i         = 0;
            foreach (var item in FieldList)
            {
                ICell cell = row1.CreateCell(i);
                cell.SetCellValue(item.Title);
                cell.CellStyle = style;
                i++;
            }
            rq.Filter = Session[rq.vid.ToString() + "Filter"].ToString();
            DataTable dt = service.GetExportReportData(rq, User);
            //将数据逐步写入sheet1各个行
            for (int z = 0; z < dt.Rows.Count; z++)
            {
                var row = dt.Rows[z];
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
                int c = 0;
                foreach (var item in FieldList)
                {
                    rowtemp.CreateCell(c++).SetCellValue(row[item.Field].ToString());
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return(File(ms, "application/vnd.ms-excel", report.Name + ".xls"));

            #endregion
        }
Пример #28
0
        //导出 dt,和表头都传
        public Stream ExportExcel(DataTable dt, List <EColName> list)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);



            //组建表头
            int index = 0;

            foreach (var i in list)
            {
                row1.CreateCell(index).SetCellValue(i.col);
                i.sortseq = index;
                index     = index + 1;
            }

            //将数据逐步写入sheet1各个行
            double d0 = 0;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);

                for (int jj = 0; jj < list.Count; jj++)
                {
                    string colname = (from EColName a in list where a.sortseq == jj select a.colname).ToArray()[0];
                    string coldata = dt.Rows[i][colname].ToString();
                    string dbtype  = dt.Columns[colname].DataType.ToString();
                    if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
                    {
                        if (double.TryParse(coldata, out d0))
                        {
                            d0 = Convert.ToDouble(coldata);
                        }
                        rowtemp.CreateCell(jj).SetCellValue(d0);
                    }
                    else
                    {
                        rowtemp.CreateCell(jj).SetCellValue(coldata);
                    }
                }
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);

            return(ms);
        }
Пример #29
0
        public void TestMoveCell()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            IRow row  = sheet.CreateRow(0);
            IRow rowB = sheet.CreateRow(1);

            ICell cellA2 = rowB.CreateCell(0);

            Assert.AreEqual(0, rowB.FirstCellNum);
            Assert.AreEqual(0, rowB.FirstCellNum);

            Assert.AreEqual(-1, row.LastCellNum);
            Assert.AreEqual(-1, row.FirstCellNum);
            ICell cellB2 = row.CreateCell(1);
            ICell cellB3 = row.CreateCell(2);
            ICell cellB4 = row.CreateCell(3);

            Assert.AreEqual(1, row.FirstCellNum);
            Assert.AreEqual(4, row.LastCellNum);

            // Try to move to somewhere else that's used
            try
            {
                row.MoveCell(cellB2, (short)3);
                Assert.Fail("ArgumentException should have been thrown");
            }
            catch (ArgumentException)
            {
                // expected during successful Test
            }

            // Try to move one off a different row
            try
            {
                row.MoveCell(cellA2, (short)3);
                Assert.Fail("ArgumentException should have been thrown");
            }
            catch (ArgumentException)
            {
                // expected during successful Test
            }

            // Move somewhere spare
            Assert.IsNotNull(row.GetCell(1));
            row.MoveCell(cellB2, (short)5);
            Assert.IsNull(row.GetCell(1));
            Assert.IsNotNull(row.GetCell(5));

            Assert.AreEqual(5, cellB2.ColumnIndex);
            Assert.AreEqual(2, row.FirstCellNum);
            Assert.AreEqual(6, row.LastCellNum);
        }
Пример #30
0
        public void Test44636()
        {
            // Open the existing file, tweak one value and
            // re-calculate

            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44636.xls");

            NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
            IRow row = sheet.GetRow(0);

            row.GetCell(0).SetCellValue(4.2);
            row.GetCell(2).SetCellValue(25);

            HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
            Assert.AreEqual(4.2 * 25, row.GetCell(3).NumericCellValue, 0.0001);


            if (OUTPUT_TEST_FILES)
            {
                // Save
                FileStream existing = File.Open(tmpDirName + "44636-existing.xls", FileMode.Open);
                existing.Seek(0, SeekOrigin.End);
                wb.Write(existing);
                existing.Close();
                Console.Error.WriteLine("Existing file for bug #44636 written to " + existing.ToString());
            }

            wb.Close();
            // Now, do a new file from scratch
            wb    = new HSSFWorkbook();
            sheet = wb.CreateSheet();

            row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(1.2);
            row.CreateCell(1).SetCellValue(4.2);

            row = sheet.CreateRow(1);
            row.CreateCell(0).CellFormula = ("SUM(A1:B1)");

            HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
            Assert.AreEqual(5.4, row.GetCell(0).NumericCellValue, 0.0001);

            if (OUTPUT_TEST_FILES)
            {
                // Save
                FileStream scratch = File.Open(tmpDirName + "44636-scratch.xls", FileMode.Open);
                scratch.Seek(0, SeekOrigin.End);
                wb.Write(scratch);
                scratch.Close();
                Console.Error.WriteLine("New file for bug #44636 written to " + scratch.ToString());
            }

            wb.Close();
        }