下面列出了org.apache.poi.ss.usermodel.SheetConditionalFormatting#org.apache.poi.xssf.usermodel.XSSFRow 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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;
}
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 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;
}
}
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;
}
}
/**
* @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);
}
}
/**
* 复制行高
*
* @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());
}
}
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;
}
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;
}
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();
}
}
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;
}
@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++;
}
}
}
/**
* 创建Excel表格
* @throws Exception
*/
public static boolean createXlsx(File target, String[] columnNames, Object[][] data) {
try {
int num = 0;
// 创建工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 工作表
XSSFSheet sheet = wb.createSheet("学生信息表");
// 标头行，代表第一行
XSSFRow header = sheet.createRow(num++);
// 创建单元格，0代表第一行第一列
for (int i = 0; i < columnNames.length; i++) {
//设置excel表格某一行的值
header.createCell(i).setCellValue(columnNames[i]);
}
for (int i = 0; i < data.length; i++) {
//设置操作行为下一行
XSSFRow row = sheet.createRow(num++);
for (int j = 0; j < data[i].length; j++) {
//设置excel表格某一行的值
row.createCell(j).setCellValue(data[i][j].toString());
}
}
/**
* 输出表格
*/
FileOutputStream fos = new FileOutputStream(target);
wb.write(fos);
fos.close();
return true;
} catch (Exception e) {
return false;
}
}
/**
* 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();
}
}
public static void writeExcel(XSSFSheet sheet, int i, EpidemicReport report, List<String> extraFields) {
try {
List<String> row = values(report, extraFields);
XSSFRow sheetRow = sheet.createRow(i + 1);
for (int j = 0; j < row.size(); j++) {
XSSFCell cell = sheetRow.createCell(j);
cell.setCellValue(row.get(j));
}
} catch (Exception e) {
}
}
public static void writeExcel(XSSFSheet sheet, int i, GeographyCode code) {
try {
List<String> row = values(code);
XSSFRow sheetRow = sheet.createRow(i + 1);
for (int j = 0;
j < row.size();
j++) {
XSSFCell cell = sheetRow.createCell(j);
cell.setCellValue(row.get(j));
}
} catch (Exception e) {
}
}
/**
* 导出Excel的例子
* 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
* @return
* @throws Exception
*/
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
logger.trace("exportExcel");
HttpHeaders responseHeaders = new HttpHeaders();
responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
//中文文件名需要用iso8859-1编码
InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
XSSFSheet sheet = workbook.getSheetAt(0);
List<SampleItem> list = getDataList();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));
for (int i=0; i<list.size(); i++) {
SampleItem si = list.get(i);
XSSFRow row = sheet.createRow(i + 1);
Cell cell1 = row.createCell(0);
cell1.setCellValue(si.getDate());
cell1.setCellStyle(cellStyle);
Cell cell2 = row.createCell(1);
cell2.setCellValue(si.getName());
Cell cell3 = row.createCell(2);
cell3.setCellValue(si.getScore());
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
workbook.close();
return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
@Test
public void lastRowNumXSSF() throws IOException {
String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
XSSFRow row = xssfSheet.getRow(0);
LOGGER.info("第一行数据:{}", row);
xssfSheet.createRow(20);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
@Test
public void lastRowNumXSSF() throws IOException {
String file = "D:\\test\\珠海.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
XSSFRow row = xssfSheet.getRow(0);
LOGGER.info("第一行数据:{}", row);
xssfSheet.createRow(20);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
@Test
public void lastRowNumXSSF() throws IOException {
String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
XSSFRow row = xssfSheet.getRow(0);
LOGGER.info("第一行数据:{}", row);
xssfSheet.createRow(20);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}
@Test
public void lastRowNumXSSF2() throws IOException {
String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
XSSFRow row = xssfSheet.getRow(0);
LOGGER.info("第一行数据:{}", row);
}
public static void writeXLSXFile() throws Exception {
XSSFWorkbook xssfwb = new XSSFWorkbook();
XSSFSheet sheet = xssfwb.createSheet(sheetName);
for (int row = 0; row < tableData.size(); row++) {
XSSFRow xddfrow = sheet.createRow(row);
rowData = tableData.get(row);
for (int col = 0; col < rowData.size(); col++) {
XSSFCell cell = xddfrow.createCell(col);
cell.setCellValue(rowData.get(col));
logger.info("Writing " + row + " " + col + " " + rowData.get(col));
}
}
try (OutputStream fileOutputStream = new FileOutputStream(
excelFileName)) {
xssfwb.write(fileOutputStream);
xssfwb.close();
fileOutputStream.flush();
fileOutputStream.close();
} catch (IOException e) {
String message = String.format("Exception saving XLSX file %s\n",
excelFileName) + e.getMessage();
logger.info(message);
// NOTE: throw exceptions with user friendly messages to be rendered
// by the master app
throw new Exception(message);
}
}
/**
* @param source
* the sheet to copy.
* @param destSheet
* the sheet to create.
* @param srcRow
* the row to copy.
* @param destRow
* the row to create.
* @param styleMap
*
*/
private static void copyRow(HSSFSheet source, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellStyle> styleMap) {
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
short dh = source.getDefaultRowHeight();
if (srcRow.getHeight() != dh) {
destRow.setHeight(srcRow.getHeight());
}
int j = srcRow.getFirstCellNum();
if (j < 0) {
j = 0;
}
for (; j <= srcRow.getLastCellNum(); j++) {
HSSFCell oldCell = srcRow.getCell(j);
XSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion = getMergedRegion(source, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
mergedRegions.add(wrapper);
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
}
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"));
}
}
/**
* 读取Excel 2007版，xlsx格式
*
* @param is
* @return
* @throws IOException
*/
private List<List<Object>> read2007Excel(InputStream is) throws IOException {
List<List<Object>> list = new LinkedList<>();
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(is);
int sheetCount = xwb.getNumberOfSheets();
for (int n = 0; n < sheetCount; n++) {
XSSFSheet sheet = xwb.getSheetAt(n);
Object value;
XSSFRow row;
XSSFCell cell;
int counter = 0;
for (int i = (startReadPos - 1); counter < sheet.getPhysicalNumberOfRows() - (startReadPos - 1); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
} else {
counter++;
}
List<Object> linked = new LinkedList<Object>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
continue;
}
value = getCellValue(cell);
if (value == null || "".equals(value)) {
continue;
}
linked.add(value);
}
list.add(linked);
}
}
return list;
}
private int buildMaxColumn(XSSFSheet sheet){
int rowCount=sheet.getPhysicalNumberOfRows();
int maxColumnCount=0;
for(int i=0;i<rowCount;i++){
XSSFRow row=sheet.getRow(i);
if(row==null){
continue;
}
int columnCount=row.getPhysicalNumberOfCells();
if(columnCount>maxColumnCount){
maxColumnCount=columnCount;
}
}
return maxColumnCount;
}
private String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException {
StringBuilder sb = new StringBuilder();
try (InputStream excelStream = new BufferedInputStream(inputStream)) {
if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
sb.append(excelExtractor.getText());
excelExtractor.close();
} else { // New format
XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
int numberOfSheets = workBook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
XSSFSheet sheet = workBook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
XSSFRow row = (XSSFRow) rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
XSSFCell cell = (XSSFCell) cellIterator.next();
sb.append(cell.toString());
sb.append(" ");
}
sb.append("\n");
}
sb.append("\n");
}
}
}
return sb.toString();
}
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;
}
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();
}
private static XSSFWorkbook writeSheetObject(List<Object> data,String sheetName) throws Exception {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
XSSFRow[] rows = new XSSFRow[data.size()];
for(int i=0;i<data.size();i++){
Object classes = data.get(i);
Object target = classes.getClass().newInstance();
Field[] fields = target.getClass().getDeclaredFields();
rows[i] = xssfSheet.createRow(i);
//xssfSheet.setDefaultColumnWidth(columnWidth);//设置列的长度
XSSFCell[] cells = new XSSFCell[fields.length];
for(int j=0;j<fields.length;j++){
String fieldName = fields[j].getName();
fieldName = fieldName.substring(0,1).toUpperCase()+fieldName.substring(1,fieldName.length());
Method method = target.getClass().getDeclaredMethod("get"+fieldName,new Class[]{});
cells[j] = rows[i].createCell(j);
Object value = method.invoke(classes,new Object[]{});
if(value instanceof Double){
cells[j].setCellValue(new Double(value.toString()));
}else if(value instanceof Integer){
cells[j].setCellValue(new Double(value.toString()).intValue());
}else if(value instanceof Float){
cells[j].setCellValue(new Double(value.toString()));
}else{
cells[j].setCellValue(value.toString());
}
//cells[j].setCellStyle(XSSFCellStyle);
}
}
return xssfWorkbook;
}
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();
}