@RequestMapping("/export")
public void exportcc(HttpServletResponse response){
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight((short)18);
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFRow row = sheet.createRow(0);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFont(font);
HSSFCell cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("创建者");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("IP");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("MachineId");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("AppId");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("EnvId");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("Tag");
cell.setCellStyle(style);
List<EnvMachineRelation> list = envMachineRelationRepository.findByAppId(195);
for(int i =1;i<list.size()+1;i++){
export(i,sheet,wb,list.get(i-1),style);
}
FileOutputStream fout = new FileOutputStream("旧咨询编号1.xls");
try {
String fileName = new String("test1.xls".getBytes(),"ISO8859-1");
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}
private void export(int index,HSSFSheet sheet,HSSFWorkbook wb,EnvMachineRelation rank,HSSFCellStyle style){
HSSFRow row = sheet.createRow(index);
HSSFCell cell = row.createCell(0);
cell.setCellValue(rank.getId());
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(rank.getCreator());
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue(rank.getMachineIp());
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue(rank.getMachineId());
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue(rank.getAppId());
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue(rank.getEnvId());
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue(rank.getTag());
cell.setCellStyle(style);
}
pom依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
导入EXCEl
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import jxl.Sheet;
import jxl.Workbook;
public class 导入EXCEL {
public static void main(String[] args) throws Exception {
File file = new File("D://111.xls");
readExcel(file);
}
public static void readExcel(File file) throws Exception{
InputStream is = new FileInputStream(file.getAbsolutePath());
Workbook wb = Workbook.getWorkbook(is);
int sheet_size = wb.getNumberOfSheets();
Connection conn = getConn();
for (int index = 0; index < sheet_size; index++) {
Sheet sheet = wb.getSheet(index);
for (int i = 0; i < sheet.getRows(); i++) {
String sql = "INSERT INTO table1 ("
+ "id,dt_month,ua,count"
+ ") VALUES(0,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, sheet.getCell(0, i).getContents().trim());
ps.setString(2, sheet.getCell(1, i).getContents().trim());
ps.setInt(3, Integer.parseInt(sheet.getCell(2, i).getContents().trim()));
ps.executeUpdate();
System.out.println("sheet:" + (index+1) + "remark:" + i + "----------更新完毕---------");
}
}
is.close();
conn.close();
}
private static final String url = "jdbc:mysql://mysqlAdress:8464/databaseName";
private static final String username = "userName";
private static final String password = "password";
private static final String driver = "com.mysql.jdbc.Driver";
public static Connection getConn() {
Connection conn = null;
try {
Class.forName(driver); //加载数据库驱动
try {
conn = DriverManager.getConnection(url, username, password); //连接数据库
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}
}
pom依赖
<dependency>
<groupId>jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.4.2</version>
</dependency>