com.github.xiaoyuge5201.util.WorkbookUtils Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of datasource-spring-boot-starter Show documentation
Show all versions of datasource-spring-boot-starter Show documentation
数据库连接工具，查询mysql、oracle、sqlserver、postgresql的数据表以及字段信息；同时支持导出数据库设计文档
package com.github.xiaoyuge5201.util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Random;
/**
* @author xiaoyuge
*/
public class WorkbookUtils {
private static Logger log = LoggerFactory.getLogger(WorkbookUtils.class);
/**
* 传入原图名称，，获得一个以时间格式的新名称
*
* @param fileName 原图名称
* @return 结果
*/
public static String generateFileName(String fileName) {
DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
String formatDate = format.format(new Date());
int random = new Random().nextInt(10000);
int position = fileName.lastIndexOf(".");
String extension = fileName.substring(position);
return formatDate + random + extension;
}
/**
* 取得html网页内容 UTF8编码
*
* @param urlStr 网络地址
* @return String
*/
public static String getInputHtmlUTF8(String urlStr) {
URL url;
try {
url = new URL(urlStr);
HttpURLConnection httpsURLConnection = (HttpURLConnection) url.openConnection();
httpsURLConnection.setRequestMethod("GET");
httpsURLConnection.setConnectTimeout(5 * 1000);
httpsURLConnection.connect();
if (httpsURLConnection.getResponseCode() == 200) {
// 通过输入流获取网络图片
InputStream inputStream = httpsURLConnection.getInputStream();
String data = readHtml(inputStream, "UTF-8");
inputStream.close();
return data;
}
} catch (Exception e) {
log.error("信息:" + e.toString(), e);
return null;
}
return null;
}
/**
* 取得html网页内容 GBK编码
*
* @param urlStr 网络地址
* @return String
*/
public static String getInputHtmlGBK(String urlStr) {
URL url;
try {
url = new URL(urlStr);
HttpURLConnection httpsURLConnection = (HttpURLConnection) url.openConnection();
httpsURLConnection.setRequestMethod("GET");
httpsURLConnection.setConnectTimeout(5 * 1000);
httpsURLConnection.connect();
if (httpsURLConnection.getResponseCode() == 200) {
// 通过输入流获取网络图片
InputStream inputStream = httpsURLConnection.getInputStream();
String data = readHtml(inputStream, "GBK");
inputStream.close();
return data;
}
} catch (Exception e) {
log.error("信息:" + e.toString(), e);
return null;
}
return null;
}
public static double parseStrToDouble(String str) {
if (str.endsWith("%")) {
str = str.substring(0, str.lastIndexOf("%"));
return Double.parseDouble(str) / 100;
}
return 0;
}
/**
* @param inputStream 流
* @param uncode 编码 GBK 或 UTF-8
* @return 結果
*/
private static String readHtml(InputStream inputStream, String uncode) throws Exception {
InputStreamReader input = new InputStreamReader(inputStream, uncode);
BufferedReader bufReader = new BufferedReader(input);
String line;
StringBuilder contentBuf = new StringBuilder();
while ((line = bufReader.readLine()) != null) {
contentBuf.append(line);
}
return contentBuf.toString();
}
/**
* @param inputStream 输入流
* @return 结果
*/
public static byte[] readInputStream(InputStream inputStream) {
// 定义一个输出流向内存输出数据
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
// 定义一个缓冲区
byte[] buffer = new byte[1024];
// 读取数据长度
int len;
// 当取得完数据后会返回一个-1
try {
while ((len = inputStream.read(buffer)) != -1) {
// 把缓冲区的数据 写到输出流里面
byteArrayOutputStream.write(buffer, 0, len);
}
} catch (IOException e) {
log.error("信息:" + e.toString(), e);
return null;
} finally {
try {
byteArrayOutputStream.close();
} catch (IOException e) {
log.error("信息:" + e.toString(), e);
}
}
// 得到数据后返回
return byteArrayOutputStream.toByteArray();
}
/**
* 将 dst的内容追加到src中
*
* @param src 源表
* @param dst 目的表
* @param distance 两个内容之间的间隔
* @return 结果
*/
public static Workbook copyWorkBook(Workbook src, Workbook dst, int distance) {
int d = distance + 1;
Sheet sheet = src.getSheetAt(0);
Sheet sheetDst = dst.getSheetAt(0);
int num = sheet.getLastRowNum();
int dstRowNum = 0;
int i = num + d;
while (dstRowNum <= sheetDst.getLastRowNum()) {
Row row = sheet.createRow(i++);
Row dstRow = sheetDst.getRow(dstRowNum++);
// 行高
row.setHeight(dstRow.getHeight());
Iterator it = dstRow.cellIterator();
int m1 = 0;
while (it.hasNext()) {
Cell cell = it.next();
Cell newCell = row.createCell(m1);
// 列宽
sheet.setColumnWidth(newCell.getColumnIndex(), sheetDst.getColumnWidth(cell.getColumnIndex()));
newCell.setCellValue(cell.getStringCellValue());
// 样式
CellStyle newStyle = src.createCellStyle();
newStyle.cloneStyleFrom(cell.getCellStyle());
newCell.setCellStyle(newStyle);
m1++;
}
}
// 拿到目标book的所有合并单元格
List mergedRegions = sheetDst.getMergedRegions();
for (CellRangeAddress cellRangeAddress : mergedRegions) {
int firstRow = cellRangeAddress.getFirstRow();
int lastRow = cellRangeAddress.getLastRow();
int newFirstRow = firstRow + num + d;
int newLastRow = lastRow + num + d;
// 源sheet单元格合并
sheet.addMergedRegion(new CellRangeAddress(newFirstRow, newLastRow, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()));
}
return src;
}
/**
* 复制单元格
*
* @param fromStyle 源
* @param toStyle 目标
*/
private static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
toStyle.cloneStyleFrom(fromStyle);
}
/**
* 合并单元格
*
* @param fromSheet 源
* @param toSheet 目标
*/
private static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
int num = fromSheet.getNumMergedRegions();
CellRangeAddress cellR;
for (int i = 0; i < num; i++) {
cellR = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(cellR);
}
}
/**
* 复制单元格
*
* @param wb 文档
* @param fromCell 源
* @param toCell 目标
*/
private static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
XSSFCellStyle newstyle = wb.createCellStyle();
if (fromCell.getCellStyle() != null) {
copyCellStyle(fromCell.getCellStyle(), newstyle);
}
toCell.setCellStyle(newstyle);
if (fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// 不同数据类型处理
CellType fromCellType = fromCell.getCellType();
toCell.setCellType(fromCellType);
if (fromCellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(fromCell)) {
toCell.setCellValue(fromCell.getDateCellValue());
} else {
toCell.setCellValue(fromCell.getNumericCellValue());
}
} else if (fromCellType == CellType.STRING) {
toCell.setCellValue(fromCell.getRichStringCellValue());
} else if (fromCellType == CellType.BLANK) {
//
} else if (fromCellType == CellType.BOOLEAN) {
toCell.setCellValue(fromCell.getBooleanCellValue());
} else if (fromCellType == CellType.ERROR) {
toCell.setCellErrorValue(fromCell.getErrorCellValue());
} else if (fromCellType == CellType.FORMULA) {
toCell.setCellFormula(fromCell.getCellFormula());
}
}
/**
* 行复制功能
*
* @param wb 文檔
* @param oldRow 源
* @param toRow 目標
*/
@SuppressWarnings("rawtypes")
private static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
toRow.setHeight(oldRow.getHeight());
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
XSSFCell tmpCell = (XSSFCell) cellIt.next();
XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb, tmpCell, newCell);
}
}
/**
* Sheet复制
*
* @param wb 文檔
* @param fromSheet 源
* @param toSheet 目標
*/
@SuppressWarnings("rawtypes")
static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
mergeSheetAllRegion(fromSheet, toSheet);
// 设置列宽
int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
for (int i = 0; i <= length; i++) {
toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
}
for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
XSSFRow oldRow = (XSSFRow) rowIt.next();
XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
copyRow(wb, oldRow, newRow);
}
}
}
|