문서관리를 서비스하는 입장에서 엑셀이라는 도구는 필수였다.
그래서 서비스에서 엑셀을 Import/Export 하는 것을 작업했고,
나중에 또 삽질하지 않기위해 혹은 누군가 삽질하지말라고 작성 해본다.
https://github.com/exceljs/exceljs - exceljs라는 라이브러리인데, json으로 뽑아주고, 파일로 변환해주는등 많은 기능이 들어있는 라이브러리이다.
https://github.com/SheetJS/sheetjs - 대표적으로 사용하는 js excel 라이브러리이고, 사용하고 싶지 않았으나 어쩔 수 없이 사용했다..(?)
나는 위 두개 라이브러리를 사용했다.
1. Export to Excel
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet("sheet1"); // sheet1 이라는 이름으로 시트생성
// ~~~ sheet에 데이터를 이러쿵저러쿵 넣어줌 ~~~
const _filename = `temp${new Date().getTime()}.xlsx`;
await workbook.xlsx.writeFile(_filename); // filename은 임시 파일이므로 어지간하면 겹치지않게 getTime
res.setHeader("Content-disposition", "attachment; filename=" + getDownloadFilename(req, "ReviewComment.xlsx")); // 다운받아질 파일명 설정
res.setHeader("Content-type", "application/vnd.ms-excel; charset=utf-8"); // 파일 형식 지정
var filestream = fs.createReadStream(_filename); // readStream 생성
filestream.pipe(res); // express 모듈의 response를 pipe에 넣으면 스트림을 통해 다운로드된다.
fs.unlinkSync(_filename); // 다운했으니 삭제
해당 코드는 API작성 중간에 util에 정의해놓고 불러다 쓰게끔 하였다.
참고로 res 는 express.Response 이다.
2. Merge Cell
merge_list = [
"A1:B1",
"A2:B2",
"A3:B3",
"A4:B4",
"F1:I1",
"F2:I4",
"Q1:R1",
"Q2:R2",
];
위와 같이 병합할 셀들의 정보를 작성하고 (셀위치:셀위치)
// cell merged
...javascript
const sheet = workbook.addWorksheet("sheet1");
for (var merge of merge_list) {
sheet.mergeCells(merge);
}
sheet.mergeCells 로 병합하면 된다.
3. Cell Style
cell_styles: {
cell: string;
row?: number;
rowCount?: number;
horizontal?: "center" | "left" | "right";
vertical?: "top" | "middle" | "bottom" | "distributed" | "justify";
font?: {
name?: string;
size?: number;
family?: number;
scheme?: "minor" | "major" | "none";
charset?: number;
bold?: boolean;
italic?: boolean;
underline?: boolean | "none" | "single" | "double" | "singleAccounting" | "doubleAccounting";
vertAlign?: "superscript" | "subscript";
strike?: boolean;
outline?: boolean;
};
border?: { top?: Border; right?: Border; left?: Border; bottom?: Border };
fill?: Fill;
}[] = [
//header prev
{ cell: "C1", horizontal: "center" },
{ cell: "C2", horizontal: "center" },
{ cell: "C3", horizontal: "center" },
{ cell: "C4", horizontal: "center" },
{ cell: "D3", font: { bold: true } },
{ cell: "F1", horizontal: "center", font: { size: 12, bold: true } },
{ cell: "F2", horizontal: "center", vertical: "middle", font: { size: 20, bold: true } },
{ cell: "P1", horizontal: "center" },
{ cell: "P2", horizontal: "center" },
//
// header
{
cell: "A5",
row: 5,
rowCount: 31,
horizontal: "center",
vertical : "middle",
font: { bold: true, size: 11 },
border: {
top: { style: "medium", color: { argb: "000" } },
left: { style: "thin", color: { argb: "000" } },
right: { style: "thin", color: { argb: "000" } },
bottom: { style: "thin", color: { argb: "000" } },
},
fill: { type: "pattern", pattern: "solid", fgColor: { argb: "B6DDE8" } },
},
//
];
이런식으로 스타일을 정의 해준다면,
for (var i = 0; i < cell_styles.length; i++) {
let style = cell_styles[i];
if (style.row != undefined) { // 행에 일괄 적용되어야하는 스타일
let row = sheet.getRow(style.row);
for (var j = 1; j <= style.rowCount; j++) {
let cell = row.getCell(j);
let alignment = {};
if (style.horizontal) Object.assign(alignment, { horizontal: style.horizontal });
if (style.vertical) Object.assign(alignment, { vertical: style.vertical });
cell.alignment = alignment;
cell.font = { ...style.font };
cell.border = style.border;
cell.fill = style.fill;
}
} else {
let cell = sheet.getCell(style.cell);
cell.alignment = { horizontal: style.horizontal, vertical: style.vertical };
cell.font = { ...style.font };
cell.border = style.border;
cell.fill = style.fill;
}
}
위와같이 sheet.getRow Or sheet.getCell 로 데이터를 가져온다음 스타일을 넣어주면된다.
각 스타일별로의 설명은 난이도가 낮으므로 생략
4. Excel Image 처리
사실 이 velog 에서 내가 얘기하고 싶었던건, 이부분의 비중이 크다.
필자가 만나본 엑셀 사용자 중 대부분이 엑셀에 셀 안에 그림을 삽입해서 사용하는것을 확인했다. 해당 기능을 만들어봤다.
ExcelJs 라이브러리 에서는 이미지데이터를 가져오는것이 Pro? 라이센스로 빠져있어서 사용못하고,
기능좋아서 구매 방법 슬쩍봤는데 답도없더라..
xlsx(sheetjs) 로는 구현이 가능하다고 github 에서 소문을 듣고 만들어봤다.
4-1. Excel Import with Image
아래는 이미지를 엑셀에서 가져오는 코드이다.
const GetExcelImageData = async (
file: Express.Multer.File // 필자의 경우 multer 를 사용해서 이렇게 했다.
): Promise<{ row: number; col: number; name: string; ext: string; buffer: Excel.Buffer }[]> => {
try {
if (file.mimetype.indexOf("xml") != -1) { // 엑셀파일이라면
const uploadPath = path.resolve(__dirname, "../../", edmsUploadFolder) + "/";
const workbook = new Excel.Workbook();
const ret = await workbook.xlsx.readFile(path.resolve(__dirname, "../../", file.path)); // 엑셀데이터 불러오기
let imageDatas = [];
for (var sheet of ret.worksheets) { // 시트별로
let images = sheet.getImages(); // 시트내에 모든 이미지데이터를 가져온다.
for (var image of images) {
const img = workbook.model.media.find((m: any) => m.index === image.imageId); // 여기서 img 는 Excel.Image 형식이다.
const imgFileName = `${img.name}${new Date().getTime()}.${img.extension}`;
imageDatas.push({
row: image.range.tl.nativeRow, // 이미지가 위치해있는 행 번호
col: image.range.tl.nativeCol, // 이미지가 위치해 있는 열 번호
name: imgFileName,
ext: `${img.extension}`,
buffer: img.buffer,
});
fs.writeFileSync(uploadPath + imgFileName, Buffer.from(img.buffer)); // 버퍼파일 형태로 되어있는 이미지를 서버에 저장해주고 이후에 이걸 불러다가 쓸 예정
}
}
return imageDatas;
}
} catch (err) {
logger.error(err);
}
return null;
};
위 코드를 넣어서 돌려보면 imageDatas 는
imageDatas = [
{
row : 5,
col : 4,
name : "image123556",
ext : ".png",
buffer : buffer < 52 .. >
}
...
]
이렇게 나온다. 행과 열을 매칭해서 이미지를 파악하면 된다.
4-2. Excel Export with Image
엑셀 생성시 이미지 넣는 기능
Export to Excel 시에 workbook을 정의해주는데,
해당 workbook 객체를 사용해서 이미지를 넣어주면된다.
원리는 이미지 경로에서 이미지를 로드 한 후, workbook 에 이미지를 import 하고,
어느곳에 위치할건지 위치를 정해주는 방식이다.
아래코드는 이미지 파일을 특정 셀에 넣는 코드이다.
const file_path_to_sheet_image = (
workbook: ExcelJS.Workbook, // workbook
sheet: ExcelJS.Worksheet, // sheet
file_path: string, // image file path
col: number, // 열 번호
row: number // 행번호
) => {
try {
let repo_path = file_path;
let size = imageSizeOf(repo_path); // imagesizeOf 라이브러리로 이미지의 정보를 가져오는용도로 사용.
let ext: any = size.type;
let imgId = workbook.addImage({ filename: repo_path, extension: ext }); ///우선 workbook 에 이미지를 올리기
let height = size.height;
let width = size.width;
while (height > 300 || width > 300) {
height -= height / 2;
width -= width / 2;
}
sheet.addImage(imgId, { tl: { col: col - 1, row: row - 0.5 }, ext: { height: height, width: width } }); // tl 안에 col, row 로 위치를 정해줌
sheet.getRow(row).height = height * 0.5; // 이미지의 높이에 따른 행의 높이 조절
} catch (err) {
console.log(err);
}
};
ExcelJS, xlsx 두개 라이브러리를 사용해서 엑셀을 핸들링하는것은 상당히 귀찮은 일이지만, 라이브러리가 있다는것에 감사한 일이다.
끝.