1、excel文件導(dǎo)入
controller層接口內(nèi)容
?service層代碼
?
?serviceImpl內(nèi)代碼內(nèi)容
@Override
@Transactional(rollbackFor = Exception.class)
public void importCheckItemExcel(MultipartFile file, Long checkPkgId) throws Exception {
if (file.isEmpty()){
throw new IOException("請(qǐng)選擇上傳文件");
}
Workbook work = this.getWorkbook(file);
if (null == work) {
throw new HolliException(DeviceExceptionEnum.EXCEL_IS_NULL_EXCEPTION);
}
Iterator<Sheet> sheetIterator = work.sheetIterator();
while (sheetIterator.hasNext()) {
Sheet sheet = sheetIterator.next();
if (sheet == null) {
return;
}
//先獲取首列標(biāo)題
Map<String, Integer> validColumns = new HashMap<>();
List<String> headerCells = getFirstRow(sheet, 0);
if (headerCells == null) {
return;
}
for (int i = 0; i < headerCells.size(); i++) {
Object header = headerCells.get(i);
if (header == null) {
continue;
}
if (ObjectUtil.contains(header, "修程")) {
// validColumns.put("checkLevel", i);//數(shù)字
validColumns.put("checkLevelName", i);//含義
}else if (ObjectUtil.contains(header, "工作項(xiàng)目")) {
validColumns.put("checkName", i);
}else if (ObjectUtil.contains(header, "單位")) {
// validColumns.put("checkUnit", i);//數(shù)字
validColumns.put("checkUnitName", i);//含義
}else if (ObjectUtil.contains(header, "周期")) {
validColumns.put("checkCycle", i);
}else if (ObjectUtil.contains(header, "工作內(nèi)容及標(biāo)準(zhǔn)")) {
validColumns.put("checkContent", i);
}else if (ObjectUtil.contains(header, "ATP型號(hào)")) {
validColumns.put("atpModel", i);
}
}
//標(biāo)題集合
if (validColumns.size() == 0) {
return;
}
Map<String, Object> map = new HashMap<String, Object>();
//遍歷當(dāng)前sheet中的所有行
//包涵頭部，所以要小于等于最后一列數(shù),這里也可以在初始值加上頭部行數(shù)，以便跳過頭部
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
//讀取一行
List<Object> rows = getRow(sheet, j);
if (rows == null) {
continue;
}
Set<String> keySet = validColumns.keySet();
for (String key : keySet) {
int col_index = validColumns.get(key);
if (col_index >= rows.size()) {
continue;
}
Object cell = rows.get(col_index);
map.put(key, cell);
}
//取出對(duì)應(yīng)屬性及值內(nèi)容 以便存儲(chǔ)數(shù)據(jù)庫
//業(yè)務(wù)邏輯，數(shù)據(jù)存儲(chǔ)
DevCheckItemVo devCheckItemVo = new DevCheckItemVo();
BeanUtil.copyProperties(map, devCheckItemVo);
this.insertDevCheckItem(checkPkgId, devCheckItemVo, j + 1);
}
}
}
?判斷excel的格式，同時(shí)兼容2003和2007
protected final static String excel2003L = ".xls"; //2003- 版本的excel
protected final static String excel2007U = ".xlsx"; //2007+ 版本的excel
/**
* 描述：根據(jù)文件后綴，自適應(yīng)上傳文件的版本
*/
public static Workbook getWorkbook(MultipartFile file) throws Exception {
Workbook wb = null;
InputStream is = file.getInputStream();
String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
if (excel2003L.equals(fileType)) {
wb = new XSSFWorkbook(is); //2007+
// 因HSSFWorkbook報(bào)（You need to call a different part of POI to process this data (eg XSSF instead of HSSF)），故直接使用XSSFWorkbook處理
// wb = new HSSFWorkbook(is); //2003-
} else if (excel2007U.equals(fileType)) {
wb = new XSSFWorkbook(is); //2007+
} else {
throw new IOException("解析的文件格式有誤！");
}
return wb;
}
?獲取行數(shù)據(jù)
/**
* 獲取行數(shù)據(jù)
* @param sheet
* @param rowIndex
* @return
*/
public List<Object> getRow(Sheet sheet, int rowIndex) {
List<Object> li = new ArrayList<Object>();
if (sheet == null) {
return null;
}
Row row = null;
Cell cell = null;
//讀取一行
row = sheet.getRow(rowIndex);
//去掉空行和表頭
if (row == null) {
return null;
}
/**為去掉空行，若第一列為空，則直接跳過該行*/
if (ObjectUtil.isEmpty(row.getCell(0))) {
return null;
}
//遍歷所有的列
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(this.getCellFormatValue(cell));
}
return li;
}?//獲取excel列表內(nèi)的對(duì)應(yīng)數(shù)據(jù)格式
//獲取excel列表內(nèi)的對(duì)應(yīng)數(shù)據(jù)格式
protected Object getCellFormatValue(Cell cell) {
Object val = "";
if (null != cell) {
if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
{
val = cell.getNumericCellValue();
if (DateUtil.isCellDateFormatted(cell))
{
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式轉(zhuǎn)換
}
else
{
if ((Double) val % 1 != 0)
{
val = new BigDecimal(val.toString());
}
else
{
val = new DecimalFormat("0").format(val);
}
}
}
else if (cell.getCellType() == CellType.STRING)
{
val = cell.getStringCellValue();
}
else if (cell.getCellType() == CellType.BOOLEAN)
{
val = cell.getBooleanCellValue();
}
else if (cell.getCellType() == CellType.ERROR)
{
val = cell.getErrorCellValue();
}
} else {
val = "";
}
return val;
}
?2、excel導(dǎo)出
controller層接口內(nèi)容
??service層代碼
??serviceImpl內(nèi)代碼內(nèi)容
@Override
public void exportCheckItemExcel(String strIds, HttpServletResponse response) throws Exception {
List<DevCheckItem> devCheckItemList = new ArrayList<>();
long[] itemIdArray = StrUtil.splitToLong(strIds,",");
//查詢檢修項(xiàng)id
for (int i = 0; i< itemIdArray.length; i++){
DevCheckItem devCheckItem = this.getById(itemIdArray[i]);
if (ObjectUtil.isNotEmpty(devCheckItem)){
devCheckItemList.add(devCheckItem);
}
}
if (ObjectUtil.isNotEmpty(devCheckItemList) && devCheckItemList.size() > 0){
//導(dǎo)出excel
XSSFWorkbook workbook=new XSSFWorkbook();
//獲得名字為sheet的工作本對(duì)象，這個(gè)是看你的模板工作表的名字
XSSFSheet sheet = workbook.createSheet("sheet1");
// 序號(hào)
int serialNo = 1;
//行號(hào)
int rowIndex = 1;
//創(chuàng)建表頭
XSSFRow row_excel = sheet.createRow(0);
//行業(yè)務(wù)數(shù)據(jù)填充
XSSFCell cell = row_excel.createCell(0);
cell.setCellValue("序號(hào)");
cell = row_excel.createCell(1);
cell.setCellValue("ATP型號(hào)");
cell = row_excel.createCell(2);
cell.setCellValue("修程");
cell = row_excel.createCell(3);
cell.setCellValue("工作項(xiàng)目");
cell = row_excel.createCell(4);
cell.setCellValue("單位");
cell = row_excel.createCell(5);
cell.setCellValue("周期（小時(shí)）");
cell = row_excel.createCell(6);
cell.setCellValue("工作內(nèi)容及標(biāo)準(zhǔn)");
//遍歷填充數(shù)據(jù)
for (DevCheckItem devCheckItem : devCheckItemList){
//獲取當(dāng)前行
row_excel = sheet.getRow(rowIndex);
if (row_excel == null){
//創(chuàng)建行
row_excel = sheet.createRow(rowIndex);
}
//內(nèi)容填充
if (ObjectUtil.isNotEmpty(devCheckItem)){
XSSFCell cellNew = row_excel.createCell(0);
cellNew.setCellValue(serialNo);
cell = row_excel.createCell(1);
if (ObjectUtil.isNotEmpty(devCheckItem.getAtpModel())){
cell.setCellValue(devCheckItem.getAtpModel());
}
cell = row_excel.createCell(2);
if (ObjectUtil.isNotEmpty(devCheckItem.getCheckLevel())){
String levelName = DevCheckLevelEnum.getLevelNameByType(devCheckItem.getCheckLevel());
cell.setCellValue(levelName);
}
cell = row_excel.createCell(3);
if (ObjectUtil.isNotEmpty(devCheckItem.getCheckName())){
cell.setCellValue(devCheckItem.getCheckName());
}
cell = row_excel.createCell(4);
if (ObjectUtil.isNotEmpty(devCheckItem.getCheckUnit())){
String checkUnit = this.getCheckUnitNameByNum(devCheckItem.getCheckUnit());
cell.setCellValue(checkUnit);
}
cell = row_excel.createCell(5);
if (ObjectUtil.isNotEmpty(devCheckItem.getCheckCycle())){
cell.setCellValue(devCheckItem.getCheckCycle());
}
cell = row_excel.createCell(6);
if (ObjectUtil.isNotEmpty(devCheckItem.getCheckContent())){
cell.setCellValue(devCheckItem.getCheckContent());
}
}
serialNo++;
rowIndex++;
}
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Type", "application/vnd.ms-excel");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
}
}以上已導(dǎo)出完成。
3、以下為可能會(huì)用到的導(dǎo)出實(shí)例文件，上傳文件服務(wù)器的過程?
File格式轉(zhuǎn)換MultipartFile格式的例子
//上傳minio服務(wù)器
String fileName = "檢修工作項(xiàng)目.xlsx";
File cacheFile = new File(fileName);
if (cacheFile.exists()) {
cacheFile.delete();
}
cacheFile.createNewFile();//生成文件
OutputStream out = new FileOutputStream(cacheFile);
workbook.write(out);
---------------------------------------------------------------------------------------（上面為導(dǎo)出實(shí)例文件）
//下面為上傳文件流格式轉(zhuǎn)換的格式。
FileInputStream fileInputStream = new FileInputStream(cacheFile);
MultipartFile multipartFile = new MockMultipartFile("cacheFile", cacheFile.getName(), "text/plain", IOUtils.toByteArray(fileInputStream));
?
-------------------------------------以下無正文-----------------------------------------------------------文章來源：http://www.zghlxwxcb.cn/news/detail-589319.html
注:僅供學(xué)習(xí)，記錄問題和參考，共勉！文章來源地址http://www.zghlxwxcb.cn/news/detail-589319.html
到了這里，關(guān)于excel文件導(dǎo)入或?qū)С鯦ava代碼示例的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
