* 通过输入的编号范围查询出需要导出的数据
@RequestMapping
(
value
=
"/exportFixByMonths"
,
method
=
RequestMethod
.
GET
)
public
void
exportFixByMonths
(
ActivitiesBean bean
,
HttpServletResponse response
,
HttpServletRequest request
,
@RequestParam
String startnum
,
@RequestParam
String endnum
)
throws
IOException
{
int
STARTNUM
=
Integer
.
parseInt
(
startnum
)
;
int
ENDNUM
=
Integer
.
parseInt
(
endnum
)
;
List
<
File
>
srcfile
=
new
ArrayList
<
File
>
(
)
;
for
(
int
i
=
STARTNUM
;
i
<=
ENDNUM
;
i
++
)
{
bean
.
setCOMMITTEE
(
i
)
;
String rootpath
=
(
(
HttpServletRequest
)
request
)
.
getSession
(
)
.
getServletContext
(
)
.
getRealPath
(
"/muban"
)
;
File file
=
new
File
(
rootpath
+
File
.
separator
+
"tongjis.xlsx"
)
;
String filename
=
"测试表"
+
new
SimpleDateFormat
(
"yyyyMMddHHmmssSSS"
)
.
format
(
new
Date
(
)
)
+
".xlsx"
;
File newFile
=
ExcelUtil
.
createNewFile
(
filename
,
file
)
;
InputStream is
=
null
;
XSSFWorkbook workbook
=
null
;
XSSFSheet sheet
=
null
;
try
{
is
=
new
FileInputStream
(
newFile
)
;
workbook
=
new
XSSFWorkbook
(
is
)
;
sheet
=
workbook
.
getSheetAt
(
0
)
;
}
catch
(
Exception
e1
)
{
e1
.
printStackTrace
(
)
;
if
(
sheet
!=
null
)
{
try
{
FileOutputStream fos
=
new
FileOutputStream
(
newFile
)
;
XSSFRow row
=
sheet
.
getRow
(
1
)
;
if
(
row
==
null
)
{
row
=
sheet
.
createRow
(
1
)
;
XSSFCell cell
=
row
.
getCell
(
0
)
;
if
(
cell
==
null
)
{
cell
=
row
.
createCell
(
0
)
;
TestBean testBean
=
activitiesService
.
queryceshi
(
bean
)
;
if
(
testBean
!=
null
&&
!
""
.
equals
(
testBean
)
)
{
String committeename
=
testBean
.
getCOMMITTEENAME
(
)
!=
null
?
testBean
.
getCOMMITTEENAME
(
)
+
" "
:
" "
;
String cppccsession
=
testBean
.
getCPPCCSESSION
(
)
!=
null
?
testBean
.
getCPPCCSESSION
(
)
+
" "
:
" "
;
String positions
=
testBean
.
getPOSITIONS
(
)
!=
null
?
testBean
.
getPOSITIONS
(
)
+
" "
:
" "
;
cell
=
sheet
.
getRow
(
1
)
.
getCell
(
0
)
;
cell
.
setCellValue
(
committeename
)
;
cell
=
sheet
.
getRow
(
1
)
.
getCell
(
1
)
;
cell
.
setCellValue
(
cppccsession
)
;
cell
=
sheet
.
getRow
(
1
)
.
getCell
(
2
)
;
cell
.
setCellValue
(
i
)
;
cell
=
sheet
.
getRow
(
2
)
.
getCell
(
3
)
;
cell
.
setCellValue
(
positions
)
;
workbook
.
write
(
fos
)
;
fos
.
flush
(
)
;
fos
.
close
(
)
;
}
catch
(
Exception
e
)
{
e
.
printStackTrace
(
)
;
}
finally
{
try
{
if
(
null
!=
is
)
{
is
.
close
(
)
;
}
catch
(
Exception
e
)
{
e
.
printStackTrace
(
)
;
srcfile
.
add
(
newFile
)
;
String strpath
=
SysParamInit
.
zipPath
+
File
.
separator
;
String pathname
=
new
SimpleDateFormat
(
"yyyyMMddHHmmssSSS"
)
.
format
(
new
Date
(
)
)
+
".zip"
;
File zipfile
=
new
File
(
strpath
+
pathname
)
;
ExcelUtil
.
zipFiles
(
srcfile
,
zipfile
)
;
ExcelUtil
.
deleteFiles
(
srcfile
)
;
ExcelUtil
.
downFile
(
response
,
strpath
,
pathname
)
;
ExcelUtil
.
deleteZip
(
zipfile
)
;
* 读取excel模板,并复制到新文件中供写入和下载
* @return
public
static
File
createNewFile
(
String filename
,
File file
)
{
String realPath
=
SysParamInit
.
zipPath
;
;
File dir
=
new
File
(
realPath
)
;
if
(
!
dir
.
exists
(
)
)
{
dir
.
mkdirs
(
)
;
File newFile
=
new
File
(
realPath
,
filename
)
;
try
{
newFile
.
createNewFile
(
)
;
fileChannelCopy
(
file
,
newFile
)
;
}
catch
(
Exception
e
)
{
e
.
printStackTrace
(
)
;
return
newFile
;
public class SysParamInit {
private static final String SYS_CONFIG="config.properties";
public static String zipPath;
static {
java.util.Properties prop = Properties.loadProperties(SYS_CONFIG);
SysParamInit.zipPath=prop.getProperty("zipPath");
config.properties文件里指定压缩文件路径
zipPath=D://uploadfile
* 复制文件
* @param s
* 源文件
* @param t
* 复制到的新文件
public static void fileChannelCopy(File s, File t) {
try {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(s), 1024);
out = new BufferedOutputStream(new FileOutputStream(t), 1024);
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
} finally {
if (null != in) {
in.close();
if (null != out) {
out.close();
} catch (Exception e) {
e.printStackTrace();
* 将多个Excel打包成zip文件
* @param srcfile
* @param zipfile
public static void zipFiles(List<File> srcfile, File zipfile) {
byte[] buf = new byte[1024];
try {
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
for (int i = 0; i < srcfile.size(); i++) {
File file = srcfile.get(i);
FileInputStream in = new FileInputStream(file);
out.putNextEntry(new ZipEntry(file.getName()));
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
out.closeEntry();
in.close();
out.close();
} catch (IOException e) {
e.printStackTrace();
* 删除多个文件方法
* @param srcfile
public static void deleteFiles(List<File> srcfile) {
for (File file : srcfile) {
if (file.exists()) {
file.delete();
public static void downFile(HttpServletResponse response,String serverPath, String str) {
try {
String path = serverPath + str;
File file = new File(path);
if (file.exists()) {
InputStream ins = new FileInputStream(path);
BufferedInputStream bins = new BufferedInputStream(ins);
OutputStream outs = response.getOutputStream();
BufferedOutputStream bouts = new BufferedOutputStream(outs);
response.setContentType("application/x-download");
response.setHeader(
"Content-disposition",
"attachment;filename="
+ URLEncoder.encode(str, "GBK"));
int bytesRead = 0;
byte[] buffer = new byte[8192];
while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {
bouts.write(buffer, 0, bytesRead);
bouts.flush();
ins.close();
bins.close();
outs.close();
bouts.close();
} else {
response.sendRedirect("../error.jsp");
} catch (IOException e) {
e.printStackTrace();
* 删除zip
public static void deleteZip(File path) {
if (path.exists()) {
path.delete();


转自:专业IT技术发表平台