web 上传 excel

冷默言语 2021-12-01 18:19:04

引用来自“chendc”的评论

Controller层

1.public ModelAndView save(UploadItem uploadItem, HttpServletRequest request,

           HttpServletResponse response) throws Exception {

               使用UploadItem 接收数据

               saveExcel(uploadItem);

}

Service层

2.public boolean saveExcel(UploadItem uploadItem）{

   使用

   for (int i = 0; i < uploadItem.getFileData().size() - 1; i++)

   {

       MultipartFile file = uploadItem.getFileData().get(i + 1);

       if (file == null) continue;

       String fname = file.getOriginalFilename();

       int pos = fname.lastIndexOf(".") + 1;

       //获取文件类型

       String buff = fname.substring(pos, fname.length());

       InputStream fileStream;

       fileStream = file.getInputStream();

       byte[] fileData = new byte[(int) file.getSize()];

       fileStream.read(fileData);



       通过ExcelTool去解析byte[]



       getData(fileData, 0, buff);

   }

}

ExcelTool

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayInputStream;

import java.io.FileOutputStream;

3.public static Map<String, Object> getData(byte[] buf, int ignoreRows, String sufffx) throws Exception {

   Workbook wb = null;

   List<String[]> result = null;

   int rowSize = 0;



   ByteArrayInputStream is = new ByteArrayInputStream(buf);

   if (sufffx.equals("xlsx"))

   {

       wb = new XSSFWorkbook(is);

   }

   else

   {

       wb = new HSSFWorkbook(is);

   }



   Cell cell = null;



   //读取工作表个数

   for(int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++)

   {

       //初始化

       result = new ArrayList<String[]>();



       Sheet st = wb.getSheetAt(sheetIndex);



       TreeMap<String, Object> cellRelationsMap = new TreeMap<String, Object>();

       //读取每一行中每一列单元格数据

       for(int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++)

       {

           Row row = st.getRow(rowIndex);

           if(row == null)

           {

               continue;

           }

           //获取总列数

           int tempRowSize = row.getLastCellNum() + 1;

           if(tempRowSize > rowSize)

           {

               rowSize = tempRowSize;

           }

           //创建数组

           String[] values = new String[rowSize];

           //初始化数组

           Arrays.fill(values, "");



           boolean hasValue = false;



           for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++)

           {

               String value = "";



               if(columnIndex == 0 && rowIndex == 0)

               {

                   columnIndex = 1;

               }



               cell = row.getCell(columnIndex);

               if (cell != null)

               {

                   switch (cell.getCellType())

                   {

                       case HSSFCell.CELL_TYPE_STRING:

                           value = cell.getStringCellValue();

                           break;

                       case HSSFCell.CELL_TYPE_NUMERIC:

                           if (HSSFDateUtil.isCellDateFormatted(cell))

                           {

                               Date date = cell.getDateCellValue();



                               if (date != null)

                               {

                                   value = new SimpleDateFormat("yyyy-MM-dd").format(date);

                               }

                               else

                               {

                                   value = "";

                               }

                           }

                           else

                           {

                               value = cell.toString();

                               double s = Double.valueOf(value).doubleValue();

                               DecimalFormat formatter = new DecimalFormat("0.00");

                               formatter.setRoundingMode(RoundingMode.FLOOR);

                               value = formatter.format(s);

                           }

                           break;



                       case HSSFCell.CELL_TYPE_FORMULA:

                           value = cell.getStringCellValue();

                           break;



                       case HSSFCell.CELL_TYPE_BLANK:

                           break;



                       case HSSFCell.CELL_TYPE_ERROR:

                           value = "";

                           break;



                       case HSSFCell.CELL_TYPE_BOOLEAN:

                           value = (cell.getBooleanCellValue() == true ? "Y" : "N");

                           break;



                       default:

                           value = "";

                   }

               }

               if (columnIndex == 0 && value.trim().equals(""))

               {

                   break;

               }

               values[columnIndex] = rightTrim(value);

               hasValue = true;

               if(rowIndex <= 0)

               {

                   recordCellRelations(columnIndex,values[columnIndex],cellRelationsMap);

               }

           }



           if (hasValue && rowIndex >= 1)

           {

               result.add(values);

           }

       }

       sheetMap.put(st.getSheetName(), result);

       sheetMap.put(st.getSheetName()+ "Title", cellRelationsMap);

   }

   is.close();



   return sheetMap;

}

回复收藏 0

风苍溪 2021-12-01 18:19:04

不知道露珠想表达什么意思，用户就上传的是excel 就是excel格式，如果是其他格式，就是一个解析转换的过程，可以规定用户上传的文件后缀，再自己封装相关的方法。poi是针对于excel解析的，首先它必须有一个excel文件，能将其中的数据解析出来进行操作，或者有相关的数据能生成一个excel文件。

回复收藏 0

可是我不能没有你 2021-12-01 18:19:04

JSP:

<form id="dataform" method="POST" enctype="multipart/form-data" action="${ctxPath}/sjgl/pcsjgl/save.do">

<input type='file' name='fileData[1]' id='files_1' />

回复收藏 0

能否归途做我良人 2021-12-01 18:19:01

就是说，还得专门写方法去解析二进制是吗？

回复收藏 0

笑红尘 2021-12-01 18:18:59

不要你写咯，你调用相对应的方法读出来就行。然后解析下表格就可以了。

回复收藏 0

绝影如岚 2021-12-01 18:18:57