package com.qs.file.service.excel import com.qs.file.utils.ExcelUtils import
org.apache. poi.hssf.usermodel.HSSFCell import org.apache.poi.hssf.
usermodel.HSSFRow import org.apac he.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook import org.apache
.poi.ss.usermodel. import org.apache.poi.xssf.usermodel. XSSFWorkbook
import org.springframework.stereotype.Service import org.
springframework.web.multipart.Multipart File import javax.servlet.
http.HttpServletRequest import java.io. import java.math.Big Decimal
import java.text.SimpleDateFormat import java.util. import java.util.
regex.Mat cher import java.util.regex.Pattern excelauthor qiusuo since
Service public class AnalysisExcelService private SimpleDateFormat fmt
new SimpleDateF ormat yyyyMMdd excel public void insertDataToExcel
String fileDir Usersq iusuoDesktop .xlxs List String sheetName new
ArrayList excel sheetName.add A sheetName.add B sheetName.add C
String title id name password Excel Utils.createExcelXls fileDir sheetNa
me title List Map String String userList new ArrayList Map map.put i
d map.put name map.put password Map String String map new HashM
ap String String map .put id map .put name m ap .put password Map S
tring String map new HashMap String String map . put id map .put na
me map .put password userList .add map userList .add map userList .a
dd map Map String List Map String users new HashMap users.pu
t A userList List Map String userList new Array List ma p new HashMap
String String map .put id map .put name map .put pa ssword Map Stri
ng String map new HashMap String map .put id map .put name
map .put password new ArrayList Map String String users.put C userList
List for Iterator String sheeNam
eIterator sheetName.iterator sheeNameIterator.hasNext String she et s
heeNameIterator.next sheet.C ifusers.get sheet .size sheeNameIterato
r.remove Excel ExcelUtils.createExcelXls fileDir sheetName title forint j
j sheetName.size j try ExcelUtils. writeToExcelXls fileDir sheetName.g
et j users.get sheetName.get j catch Exception e TODO Auto generated
catch block e.printStackTrace Excel param file param request public v
oid analyis Excel MultipartFile file HttpServletRequest request
Workbook workbook Excel Utils. getWorkBook file List String list new
ArrayList ifworkbook null forint sheetNum sheetNum workbook.getNu
mberOfSheets sheetNum sheet She et sheet workbook.getSheetAt she
etNum if sheet null continue sheet int first RowNum sheet.getFirstRow
Num sheet int lastRowNum sheet.getLastRowNum forint r owNum first
RowNum rowNum lastRowNum rowNum Row row sheet.getRow rowNu
m ifrow null continue int firstCellNum row.getFirstCellNum int lastCel
lNum row.getLastCellNum String cells new String row.getLastCellNum
forint cellNum firstCellNum cellNum lastCellNum cellNum Cell cell row
.getCell cellNu m cells cellNum ExcelUtils.getCellValue cell list.add cell
s package co m.qs.file.utils import org.apache.poi.hssf.usermodel.HSS
FCell import org.apache.poi.hssf .usermodel.HSSFRow import org.
.poi.hssf.usermodel. HSSFSheet import org.apache.poi. hssf.usermode
l.HSSFWorkbook import org. apache.poi.ss.usermodel. import org.apa
che.poi.xs sf.usermodel.XSSFWorkbook import org.springframework.w
eb.multipart.MultipartFile import java.io. import java.math.BigDecimal
import java.text.SimpleDateFormat import java.util .Date import java.
util.List import java.util.Map import java.util. regex.Matcher import jav
a.util.regex.Pattern Excel author qiusuo since public class ExcelUtils pr
ivate static HSSFWorkbook hWorkbook null private static XSSFWorkboo
k xWorkbook nul l private final static String xls xls private final static S
tring xlsx xlsx null . yyyy MM dd yyyy MM dd cellValue param cell r
eturn public static String getCellValue Cell cell String ce llValue null ifc
ell null cell.toString. trim.equals return int cellType cell.getCellType St
ring . if cell.getCellType Cell.CELL_TYPE_NU MERIC cell.setCellType Cel
l. CELL_TYPE_STRING switchcellType case Cell.CELL_TYP E_NUMERIC sh
ort format cell.getCellStyle.getDataFormat ifDateUtil.isCellDateForm at
ted cell SimpleDateFormat sdf null System.out.println cell.getCellStyle.
getDat aFormat cell.getCellStyle.getDataFormat ifformat format sdf ne
w SimpleDateFormat HH mm else ifformat format format format md i
d id sdf new SimpleDateFormat yyyy MM dd double value ce ll.getNum
ericCellValue Date date org.apache.poi.ss.usermodel.DateUtil .getJava
Date va lue cellValue sdf.format date else sdf new SimpleDateFormat y
yyy MM dd HH mm s s try cellValue sdf.format cell.getDateCellValue ca
tchException e try throw new Exception exception on get date data .co
ncat e.toString catch Excep tion e e .printStackTrace finally sdf null els
e BigDecimal bd new BigDecimal cell.getNumericCellValue cellValue bd
.toPlainString BigDecimalplainS tring cellValue String.valueOf cell.getN
umericCellValue break case Cell.CELL_TYP E_STRING cellValue judgeSt
ringDate String. valueOf cell.getStringCellValue break c ase Cell.CELL_
TYPE_BOOLEAN Boolean cellValue String.valueOf cell.getBooleanCellVa
lue break case Cell.CELL_TYPE_FORMULA cellValue String.valueOf cell.
getStringCellValue break case Cell.CELL_TYPE_BLANK cellValue break
case Cell.CELL_TYPE_ERROR cel lValue break default cellValue break r
eturn cellValue return public s tatic String judgeStringDate String value
try int num Integer.valueOf value if val ue.length SimpleDateFormat s
df new SimpleDateFormat yyyy MM dd Date tDate new Date long local
Offset tDate. getTimezoneOffset tDate.setTimelong Double.parseDoub
le value localOffset value sdf.format tDate poi catchException e System
.out.println String result value String pattern ^d . . String pattern ^d Pa
ttern r Pattern.compile pattern Matcher m r .matcher v alue Pattern r P
attern.compile pattern Matcher m r .matcher value if m .match es resu
lt value.replaceAll . result result. replaceAll else if m .matches if value.
length return result result value.subs tring value. substring value.subs
tring else return result String dateArr result.split int year Integer.value
Of dateArr int month Integer.valueOf dateArr int day Integer.valueOf d
ateArr int monthLengths new int ifyear year year monthLengths else
monthLengths int m onthLength monthLengths month if day monthLe
ngth return result result year month .substring month .length day .sub
string day .length return result file Excel param file MultipartFile retur
n Excel public static Workbook getWorkBook MultipartFile file String fil
eName file.getOrigin alFilename Workbook excel Workbook workbook
null try excelio InputStream is file .getInputStream xlsxlsx Workbook i
ffileName.endsWith xls workbook new HSS FWorkbook is else iffileNam
e.endsWith xlsx workbook new XSSFWorkbook is catchIOException e e
.printStackTrace return workbook sheet. param fileD ir param sheetNa
me retu rn boolean public static boolean XlsSheetExist String fileDir St
ring sheetName boolean flag false File file new File fileDir iffil e.exists
workbook try hWorkbook new HSSFWorkbook new FileInputStream file
HSSFSheet sheet hWorkbook.getSheet shee tName ifsheet null sheet fl
ag true catchIOExcep tion e TODO Auto generated catch block e.printS
tackTrace else f lag false r eturn flag excel xls . param fileDir excel par
am sh eetNames param titleRow excel pu blic static void createExcelXl
s S tring fileDir List String sheetNames String titleRow hWorkbook new
HSSFWorkbook FileOutputStream fileOutputS tream null HSSF Row row
null try CellStyle cellStyle hWork book.createCellStyle cellStyle.setAlig
nment HorizontalAlignment .LEFT cellStyle.setVerticalAlignment Vertic
alAlignment.BOTTOM Worksheet（sheetxlsfor int i i sheetNames.size i
hWo rkbook.createSheet sheet Names.get i hWorkbook.getSheet shee
tNames.get i.createRow row hWorkbook.getSh eet sheetNa mes.get i.c
reateRow row.setHeightshort forshort j j t itleRow.length j HSSFCell cel
l row.createCell j Ce llType.BLANK cell.setCellValu e titleRow j cell.setC
ellStyle cellSty le fileOutputStream new FileOutputStream fi leDir hWor
kboo k.write fileOutputStream catchException e TODO Auto gen erate
d c atch block e.printStackTrace finally iffileOutputStrea m null try file
Outpu tStream.close catchIOException e public private protected class
void static final String int long double float boolean byte bit Binnary s
hort char name phone email windowswindows linuxlinux clear JVMjava
JREjava JDKjava IDE eclipse JAVA_HOMEjava path CLASSPATH package
class main System out print println variable byte short int long float do
uble boolean char String bit Binnary true false age sex address salary s
core studentName englishTeacherName javaScorejava cardId cardPwd
love you import scanner day month year if else switch case break cont
inue max min count num whiledo for guess sum array length Math ran
dom arraycopy Arrayss copyOf sort sqrt flag say sayHi plus public priv
ate protected static void return workspace home OS profile Integrate D
evelopment Environment literal distance currentTimeMillis money pric
e totalPrice change Unicode ASCIIascii child run close percent size bala
nce width height index LeapYear console unitPrice amount command r
esult answer total inputPwd temp src srcPos dest destPos original new
Length bubble game count input generate next trim UpperCase equals
toCharArraychar check letter cashier tax taxIncome nine prime level J
ava extends implements Object field property method class gender Em
p public class null pointer Exception NullPointerException address stu
dy teach eat sleep student teacher cell row col column drop move left
right get info moveLeft moveRight getCellInfo print room classroom w
idth height image move shoot course pay this study teach cut eat slee
p tetromino extends implements Object field property method class a
nimal tiger person create apple banana super new override overload c
ase show test package import public private protected static final pass
word customer customerName customerPwd Shape area Circle Square
maxArea unionPay ICBC ABC CCB instance frame setSize printInfo prin
tCell totalRow totalCol return cashier pay payByCash payByCard payBy
Check companyName getCellInfo GC garbage collection tetromino valu
e printTetromino mammal bird color base point distance cat car tank
ostrich fly bird move parameter runner hunter speed getBalance draw
Money checkPwd payOnline payTelBill money pwd password number p
honeNum parse vehicle author ClassCastException Outer Inner time ex
ecute action insert out menu all exercise Object List add null Hexagon
payGasBill bill shoot game ShootGame airplane speed FlyingObject En
emy getScore step bounds outOfBounds DOUBLE_FIRE life bee getType
award awardType bullet image shoot shootBy hero addLife subtractLif
e moveTo other frame panel pause gameover start running score time
r interval buffered background fly IO read resource getResource excep
tion try catch paint graphics state always top setAlwaysOnTop default
visible action mouse adapter mouseEntered mouseExited mouseClick
mouseEvent key listener motion addMouseListener addMouseMotionLi
stener schedule interval nextOne flyEnteredIndex shootIndex bang Jav
aSE String encode unicode index length append match insert endsWidt
hstartsWidthsize equals hash code buffer builder build sleep block Qu
eue Stack add remove iterator hasNext next delete Collection List Link
edList Set HashSethash Map HashMaphash putmap key value Entry Ob
ject hashCode equals wait notify interrupt Exception NullPointerExcept
ion File FileNotFoundException IOExceptionIO StringIndexOutOfBound
sException Error Throwable FileFilter InputStream OutputStream read
write flush Reader Writer Thread Runnable ExecutorService Collections
sort asListList toArray synchronized Socket TCP IP mask gateway Serv
erSocket close e mail langjava util Scanner Math Random net io dom X
ML field get set XPathxml Runtime RuntimeException ClassCastExcepti
on OracleJDBC select fromwhere insert update delete into employee de
partment salary table database management relational row record colu
mn field query definition Manipulation Transaction unit resource conn
ect disconnected default unique constraint references foreign primary
modify condition truncate commit rollback purge recycle concatpad ce
il floor roundmodulus timestamp least greatest extract average group
manager joindistinct decode bonus salesman； analyst partition over…
rank dense union rollup cube Intersect Minus index view grant seque
nce cycle cache auto increment current modeler statement resultset re
gister execute property utility common pool Idle initial Prepared interp
reter injection sensitive insensitive forward concurrency atomicity con
sistency isolation durability transfer