org.apache.poi.xssf.usermodel.XSSFRow Java Examples

public static List<String> writeExcelHeader(XSSFWorkbook wb, XSSFSheet sheet, List<String> extraFields) {
    try {
        List<String> columns = externalNames(extraFields);
        sheet.setDefaultColumnWidth(20);
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0; i < columns.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        return columns;
    } catch (Exception e) {
        return null;
    }
}
 

private MetaFile writeExcel(Map<String, List<String[]>> data) throws IOException {

    XSSFWorkbook workBook = new XSSFWorkbook();

    for (String model : data.keySet()) {
      XSSFSheet sheet = workBook.createSheet(model);
      int count = 0;
      for (String[] record : data.get(model)) {
        XSSFRow row = sheet.createRow(count);
        int cellCount = 0;
        for (String val : record) {
          XSSFCell cell = row.createCell(cellCount);
          cell.setCellValue(val);
          cellCount++;
        }
        count++;
      }
    }

    File excelFile = MetaFiles.createTempFile("Data", ".xls").toFile();
    FileOutputStream out = new FileOutputStream(excelFile);
    workBook.write(out);
    out.close();

    return metaFiles.upload(excelFile);
  }
 

public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
    int firstRow1 = sheet1.getFirstRowNum();
    int lastRow1 = sheet1.getLastRowNum();
    boolean equalSheets = true;
    for (int i = firstRow1; i <= lastRow1; i++) {

        XSSFRow row1 = sheet1.getRow(i);
        XSSFRow row2 = sheet2.getRow(i);
        if (!compareTwoRows(row1, row2)) {
            equalSheets = false;
            break;
        }
    }
    return equalSheets;
}
 

public static ArrayList readXlsx(String path) throws IOException {
    XSSFWorkbook xwb = new XSSFWorkbook(path);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row;
    String[] cell = new String[sheet.getPhysicalNumberOfRows() + 1];
    ArrayList<String> arrayList = new ArrayList<>();
    for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        cell[i] = "";
        row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
            cell[i] += row.getCell(j).toString();
            cell[i] += " | ";
        }
        arrayList.add(cell[i]);
    }
    return arrayList;
}
 

@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new XSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       XSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			XSSFCell cell = headerRow.createCell(col);
			cell.setCellType(XSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(wb.getCreationHelper().createRichTextString(s));
			col++;
		}
	}		
}
 

private static XSSFWorkbook writeSheetData(List<List<String>> data,String sheetName){
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
	XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
	XSSFRow[] rows = new XSSFRow[data.size()];
	for(int i=0;i<data.size();i++){
		List<String> columns = data.get(i);
		rows[i] = xssfSheet.createRow(i);
		//xssfSheet.setDefaultColumnWidth(columnWidth);//设置列的长度
		XSSFCell[] cells = new XSSFCell[columns.size()];
		for(int j=0;j<columns.size();j++){
			cells[j] = rows[i].createCell(j);
			//cells[j].setCellStyle(XSSFCellStyle);
			cells[j].setCellValue(columns.get(j));
		}
	}
	return xssfWorkbook;
}
 

private static List<List<String>> readSheet(XSSFSheet xssfSheet){
	int rows = xssfSheet.getPhysicalNumberOfRows();
	List<List<String>> rowList = new ArrayList<>();
	for(int i=0;i<rows;i++){//遍历每一行
		XSSFRow row = xssfSheet.getRow(i);
		if(row==null){
			continue;
		}
		int cells = row.getPhysicalNumberOfCells();
		List<String> cellList = new ArrayList<>();
		for(int j=0;j<cells;j++){//遍历每一列
			XSSFCell cell = row.getCell(j);
			cell.setCellType(Cell.CELL_TYPE_STRING);
			//new Double("1.0").intValue()
			String cellValue = cell.getStringCellValue();
			cellList.add(cellValue);
		}
		rowList.add(cellList);
	}
	return rowList;
}
 

public static void export2007(String filePath) {
	try {
		// 输出流
		OutputStream os = new FileOutputStream(filePath);
		// 工作区
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		for (int i = 0; i < 1000; i++) {
			// 创建第一个sheet
			// 生成第一行
			XSSFRow row = sheet.createRow(i);
			// 给这一行的第一列赋值
			row.createCell(0).setCellValue("column" + i);
			// 给这一行的第一列赋值
			row.createCell(1).setCellValue("column" + i);
			// System.out.println(i);
		}
		// 写文件
		wb.write(os);
		// 关闭输出流
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

/**
 * 复制行高
 * 
 * @author      ZhengWei(HY)
 * @createDate  2020-05-29
 * @version     v1.0
 *
 * @param i_FromRow
 * @param io_ToRow
 */
public final static void copyRowHeight(Row i_FromRow ,Row io_ToRow)
{
    if ( i_FromRow instanceof HSSFRow )
    {
        io_ToRow.setHeight(        ((HSSFRow)i_FromRow).getHeight());
        io_ToRow.setHeightInPoints(((HSSFRow)i_FromRow).getHeightInPoints());
        io_ToRow.setZeroHeight(    ((HSSFRow)i_FromRow).getZeroHeight());
    }
    else if ( i_FromRow instanceof SXSSFRow )
    {
        io_ToRow.setHeight(        ((SXSSFRow)i_FromRow).getHeight());
        io_ToRow.setHeightInPoints(((SXSSFRow)i_FromRow).getHeightInPoints());
        io_ToRow.setZeroHeight(    ((SXSSFRow)i_FromRow).getZeroHeight());
    }
    else if ( i_FromRow instanceof XSSFRow )
    {
        io_ToRow.setHeight(        ((XSSFRow)i_FromRow).getHeight());
        io_ToRow.setHeightInPoints(((XSSFRow)i_FromRow).getHeightInPoints());
        io_ToRow.setZeroHeight(    ((XSSFRow)i_FromRow).getZeroHeight());
    }
}
 

public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {
    if ((row1 == null) && (row2 == null)) {
        return true;
    } else if ((row1 == null) || (row2 == null)) {
        return false;
    }

    int firstCell1 = row1.getFirstCellNum();
    int lastCell1 = row1.getLastCellNum();
    boolean equalRows = true;

    // Compare all cells in a row
    for (int i = firstCell1; i <= lastCell1; i++) {
        XSSFCell cell1 = row1.getCell(i);
        XSSFCell cell2 = row2.getCell(i);
        if (!compareTwoCells(cell1, cell2)) {
            equalRows = false;
            break;
        }
    }
    return equalRows;
}
 

/**
 * @param destination
 *            the sheet to create from the copy.
 * @param the
 *            sheet to copy.
 * @param copyStyle
 *            true copy the style.
 */
private static void copySheet(List<CellStyle> list, HSSFSheet source, XSSFSheet destination, boolean copyStyle) {
	int maxColumnNum = 0;
	List<CellStyle> styleMap = null;
	if (copyStyle) {
		styleMap = list;
	}

	for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
		HSSFRow srcRow = source.getRow(i);
		XSSFRow destRow = destination.createRow(i);

		if (srcRow != null) {

			copyRow(source, destination, srcRow, destRow, styleMap);
			if (srcRow.getLastCellNum() > maxColumnNum) {
				maxColumnNum = srcRow.getLastCellNum();
			}
		}
	}
	destination.shiftRows(destination.getFirstRowNum(), destination.getLastRowNum(), 3);
	for (int i = 0; i <= maxColumnNum; i++) {
		destination.autoSizeColumn(i);
	}
}
 

@SuppressWarnings("deprecation")
public void readXLSX(String path, int num) throws InvalidFormatException, IOException {
	File file = new File(path);
	@SuppressWarnings("resource")
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
	XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(num);

	int rowstart = xssfSheet.getFirstRowNum();
	int rowEnd = xssfSheet.getLastRowNum();
	for (int i = rowstart; i <= rowEnd; i++) {
		XSSFRow row = xssfSheet.getRow(i);
		if (null == row) {
			continue;
		}
		int cellStart = row.getFirstCellNum();
		int cellEnd = row.getLastCellNum();

		for (int k = cellStart; k <= cellEnd; k++) {
			XSSFCell cell = row.getCell(k);
			if (null == cell) {
				continue;
			}

			switch (cell.getCellTypeEnum()) {
				case NUMERIC: // 数字
					System.out.print(cell.getNumericCellValue() + "   ");
					break;
				case STRING: // 字符串
					System.out.print(cell.getStringCellValue() + "   ");
					break;
				case BOOLEAN: // Boolean
					System.out.println(cell.getBooleanCellValue() + "   ");
					break;
				case FORMULA: // 公式
					System.out.print(cell.getCellFormula() + "   ");
					break;
				case BLANK: // 空值
					System.out.println(" ");
					break;
				case ERROR: // 故障
					System.out.println(" ");
					break;
				default:
					System.out.print("未知类型   ");
					break;
			}

		}
		System.out.print("\n");
	}

}
 

public static List<String> writeExcelHeader(XSSFWorkbook wb, XSSFSheet sheet) {
    try {
        List<String> columns = externalNames();
        sheet.setDefaultColumnWidth(20);
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0;
                i < columns.size();
                i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        return columns;
    } catch (Exception e) {
        return null;
    }
}
 

private XSSFCell getOrCreatePoiCell(int rowNum, int col) {
    XSSFRow row = getOrCreatePoiRow(rowNum);
    XSSFCell cell = row.getCell(col);
    if (cell == null) {
        cell = row.createCell(col);
    }
    return cell;
}
 

public static void writeExcel(HttpServletResponse response,List<String> list) throws Exception {
	response.setContentType("application/vnd.ms-excel");//文件格式,此处设置为excel
	response.setHeader("Content-Disposition","attachment;filename=file.xls");//此处设置了下载文件的默认名称
	ServletOutputStream sos = response.getOutputStream();
    //创建一个新的excel
	XSSFWorkbook wb = new XSSFWorkbook();//XSSFWorkbook
	/**
	 * 采用现成Excel模板
	 * 用这种方式得先保证每个cell有值,不然会报空指针
	 * 有时我们用row.getCell(i)会得到null,那么此时就要用Iterator<Cell> it = row.cellIterator();
	 * XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File("D://a.xlsx")));
	 * XSSFSheet sheet = wb.getSheet("Sheet1");
	 * row[i] = sheet.getRow(i);
	 * headerCell[j] = row[i].getCell(j);
	 */
	//创建sheet页
	XSSFSheet sheet = wb.createSheet("sheet1");//sheet名
	//创建行数
	XSSFRow[] row = new XSSFRow[list.size()];
	//插入数据
	for (int i = 0; i < row.length; i++) {
		row[i] = sheet.createRow(i);
		sheet.setDefaultColumnWidth(30);//设置列的长度
		String info[] = list.get(i).split(",");
		XSSFCell[] headerCell = new XSSFCell[info.length];
		for (int j = 0; j < headerCell.length; j++) {
			headerCell[j] = row[i].createCell(j);
			headerCell[j].setCellValue(new XSSFRichTextString(info[j]));
			/**设置模板样式*/
			//headerCell[j].setCellStyle(setStyle(wb));
		}
	}
	wb.write(sos);
	wb.close();
    sos.flush();
    sos.close();
    response.flushBuffer();
}
 

/**
 * poi导出，没用easypoi
 * 不要用swagger测试导出功能，下载的文件异常
 * 直接在浏览器访问http://localhost:8080/swagger-ui.html
 * 火狐浏览器可能出现文件名乱码，谷歌浏览器正常
 * @param response
 * @throws Exception
 */
@ApiOperation("导出excel")
@GetMapping("excel/export")
public void exportExcel(HttpServletResponse response) throws Exception{

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();

    try {
        //表头
        XSSFRow r0 = sheet.createRow(0);
        XSSFCell c00 = r0.createCell(0);
        c00.setCellValue("列1");
        XSSFCell c01 = r0.createCell(1);
        c01.setCellValue("列2");

        //数据
        XSSFRow r1 = sheet.createRow(1);
        XSSFCell c10 = r1.createCell(0);
        c10.setCellValue("数据1");
        XSSFCell c11 = r1.createCell(1);
        c11.setCellValue("数据2");

        response.reset();

        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试.xlsx", "utf-8"));

        //将文件输出
        workbook.write(response.getOutputStream());
    } catch (Exception e) {
        log.error("导出excel错误{}", e.getMessage());
        e.printStackTrace();
    } finally {
        workbook.close();
    }
}
 

@Override
protected void startSerialize( RootNode rootNode, OutputStream outputStream ) throws Exception
{
    workbook = new XSSFWorkbook();
    sheet = workbook.createSheet( "Sheet1" );

    XSSFFont boldFont = workbook.createFont();
    boldFont.setBold( true );

    XSSFCellStyle boldCellStyle = workbook.createCellStyle();
    boldCellStyle.setFont( boldFont );

    // build schema
    for ( Node child : rootNode.getChildren() )
    {
        if ( child.isCollection() )
        {
            if ( !child.getChildren().isEmpty() )
            {
                Node node = child.getChildren().get( 0 );

                XSSFRow row = sheet.createRow( 0 );

                int cellIdx = 0;

                for ( Node property : node.getChildren() )
                {
                    if ( property.isSimple() )
                    {
                        XSSFCell cell = row.createCell( cellIdx++ );
                        cell.setCellValue( property.getName() );
                        cell.setCellStyle( boldCellStyle );
                    }
                }
            }
        }
    }
}
 

private XSSFRow getOrCreatePoiRow(int rowNum) {
    XSSFRow row = sheet.getRow(rowNum);
    if (row == null) {
        row = sheet.createRow(rowNum);
    }
    return row;
}
 

public static boolean isBlankRow(XSSFRow row, int index, int rowCount){
	if(row == null)
		return true;
	for(int i=index; i < rowCount; i++){
		if(row.getCell(i) != null || 
				!"".equals(row.getCell(i).getStringCellValue().trim())){
			return false;
		}
	}
	return true;
}
 

@Override
public String[] read(String sheetName, int index, int headerSize) {

  if (sheetName == null || book == null) {
    return null;
  }

  XSSFSheet sheet = book.getSheet(sheetName);
  if (sheet == null) {
    return null;
  }

  XSSFRow row = sheet.getRow(index);
  if (row == null) {
    return null;
  }

  if (headerSize == 0) {
    headerSize = row.getLastCellNum();
  }

  String[] vals = new String[headerSize];

  for (int i = 0; i < headerSize; i++) {
    Cell cell = row.getCell(i);
    if (cell == null) {
      continue;
    }
    vals[i] = formatter.formatCellValue(cell);
    if (Strings.isNullOrEmpty(vals[i])) {
      vals[i] = null;
    }
  }

  return vals;
}
 

private void writeRow(XSSFSheet sheet, String[] values) {

    XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());

    for (int i = 0; i < values.length; i++) {
      XSSFCell cell = row.createCell(i);
      cell.setCellValue(values[i]);
    }
  }
 

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,4,5,6);
		insert(4,sheet,row,cell,1,2,3);
		insert(5,sheet,row,cell,7,8,9);
		insert(6,sheet,row,cell,7,8,9);
		insert(7,sheet,row,cell,4,5,6);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_count;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,4,5,6);
		insert(4,sheet,row,cell,1,2,3);
		insert(5,sheet,row,cell,7,8,9);
		insert(6,sheet,row,cell,7,8,9);
		insert(7,sheet,row,cell,4,5,6);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_remove;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}
 

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,7,8,9);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" autokey;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,7,8,9);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\";" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 

public void writestep(Object s, AtomicInteger at, XSSFSheet sheet) {

        int index = at.getAndIncrement();
        Object data = null;

        XSSFRow roww1 = sheet.createRow(index);

        if (s instanceof LinkedTreeMap) {
            data = ((LinkedTreeMap) s).get("data");
        } else if (s instanceof Report.Step) {
            data = ((Report.Step) s).data;
        }

        if (data instanceof LinkedTreeMap) {

            LinkedTreeMap map = (LinkedTreeMap) data;

            roww1.createCell(1).setCellValue(Double.toString((Double) map.get("stepno")));
            roww1.createCell(2).setCellValue((String) map.get("stepName"));
            roww1.createCell(3).setCellValue((String) map.get("action"));
            roww1.createCell(4).setCellValue((String) map.get("description"));
            roww1.createCell(5).setCellValue((String) map.get("status"));
            roww1.createCell(6).setCellValue((String) map.get("tStamp"));

        }

    }