1.1.0 • Published 3 years ago
noexcel v1.1.0
node-excel
A full featured xlsx file generation library allowing for the creation of advanced Excel files without consideration of cell detail. With this module, it can export excel easily only focus on your work and data. Some example.
Quick start
npm i noexcelBasic Usage
A simple excel example like this
import {NoExcel, Cell} from "noexcel";
const path = require("path");
// Create a new Instance of NodeExcel class
const excel = new NoExcel();
// Add a worksheet
const sheetName1 = "sheet1";
excel.addWorkSheet(sheetName1);
// Set save file path if need
const exportPath = path.join(__dirname, "../tmp");
excel.setSavePath(exportPath);
// set file name
const fileName = 'test'
excel.setFileName(fileName)
// render simple data
const simpleData = [
[{ text: "s/n" }, { text: "name" }, { text: "age" }, { text: "position" }],
[{ text: "1" }, { text: "ming" }, { text: "15" }, { text: "monitor" }],
[{ text: "2" }, { text: "hua" }, { text: "14" }, { text: "commissary" }],
[{ text: "3" }, { text: "ai" }, { text: "14" }, { text: "supervisor" }],
] as Cell[][];
// render simpleData
await excel.render(simpleData);
// save file to exportPath
await excel.saveFile()ColSpan
A colspan excel example like this
const exportPath = path.join(__dirname, "../tmp");
const fileName = "test";
const sheetName = "student";
const data = [
[
{ text: "class", colSpan: 2 },
{ text: "name" },
{ text: "age" },
{ text: "position" },
],
[
{
text: "class 1",
colSpan: 2,
},
{ text: "hua" },
{ text: "14" },
{ text: "commissary" },
],
[
{ text: "class 2", colSpan: 2 },
{ text: "hua" },
{ text: "14" },
{ text: "commissary" },
],
[
{
text: "class 3",
colSpan: 2,
rowSpan: 2,
childCells: [
[{ text: "ai" }, { text: "13" }, { text: "supervisor" }],
[{ text: "ai" }, { text: "13" }, { text: "supervisor" }],
],
},
],
] as Cell[][];
const excel = new NoExcel();
excel.addWorkSheet(sheetName).setSavePath(exportPath).setFileName(fileName);
await excel.render(data);
await excel.saveFile();RowSpan
A rowspan excel example like this
const exportPath = path.join(__dirname, "../tmp");
const fileName = "test";
const sheetName = "student";
const data = [
[
{ text: "class" },
{ text: "name" },
{ text: "age" },
{ text: "position" },
],
[
{
text: "class one",
rowSpan: 3,
childCells: [
[{ text: "ming" }, { text: "15" }, { text: "monitor" }],
[{ text: "ai" }, { text: "15" }, { text: "commissary" }],
[{ text: "ai" }, { text: "15" }, { text: "supervisor" }],
],
},
],
[{ text: "2" }, { text: "hua" }, { text: "14" }, { text: "commissary" }],
[{ text: "3" }, { text: "ai" }, { text: "13" }, { text: "supervisor" }],
] as Cell[][];
const excel = new NoExcel();
excel.addWorkSheet(sheetName).setSavePath(exportPath).setFileName(fileName);
await excel.render(data);
await excel.saveFile();Cell style
Some other example with cell style like this
const exportPath = path.join(__dirname, "../tmp");
const fileName = "test";
const sheetName = "student";
const data = [
[
{ text: "s/n" },
{ text: "name" },
{ text: "age" },
{ text: "position" },
{ text: "date" },
{ text: "link" },
],
[
{ text: "1", style: { font: { bold: true } } },
{ text: "ming" },
{ text: 15, type: "number" },
{ text: "monitor" },
{ text: new Date(), type: "date", style: { numberFormat: "yyyy-mm-dd" } },
{ text: "http://www.google.com", type: "link" },
],
[
{ text: "2" },
{ text: "hua", style: { font: { size: 14 } } },
{ text: 14, type: "number" },
{ text: "commissary" },
{ text: new Date(), type: "date", style: { numberFormat: "yyyy-mm-dd" } },
{ text: "http://www.google.com", type: "string" },
],
[
{ text: "3" },
{ text: "ai" },
{ text: 13 },
{ text: "supervisor" },
{ text: new Date(), type: "date", style: { numberFormat: "yyyy-mm-dd" } },
{
text: "http://www.google.com",
type: "link",
style: { font: { underline: true, bold: true, color: "black" } },
},
],
[
{ text: "4" },
{ text: "ai" },
{ text: 14 },
{ text: "supervisor" },
{ text: new Date(), type: "date", style: { numberFormat: "yyyy-mm-dd" } },
{
text: "http://www.cnlogo8.com/d/file/2021-05-20/97517b732413c71921c3a55726f4f299.png",
type: "image",
},
],
] as Cell[][];
const excel = new NoExcel();
excel.addWorkSheet(sheetName).setSavePath(exportPath).setFileName(fileName);
await excel.render(data);
excel.setRowHeight(5, 300).setColWidth(6, 70);
await excel.saveFile();Options and Cell types
WorkbookOptions ,WorkSheetOptions and cell style can see https://github.com/natergj/excel4node/blob/master/README.md
Cell {
text: string; // cell value, if type is image, text should be url
type?: CellType; // cellType, has five value, see below
rowSpan?: number; // default 1, the cell row merge number
colSpan?: number; // default 1, the cell column merge number
style?: CellStyle; // cell style, see types below or Style Objects in https://github.com/natergj/excel4node/blob/master/README.md
childCells?: Cell[][]; // if rowSpan >1 , childCells maybe not empty
}
CellType {
number = "number",
string = "string",
image = "image",
date = "date",
link = "link"
}
CellStyle {
alignment?: {
horizontal?: "center" | "centerContinuous" | "distributed" | "fill" | "general" | "justify" | "left" | "right";
indent?: number;
justifyLastLine?: boolean;
readingOrder?: "contextDependent" | "leftToRight" | "rightToLeft";
relativeIndent?: number;
shrinkToFit?: boolean;
textRotation?: number;
vertical?: "bottom" | "center" | "distributed" | "justify" | "top";
wrapText?: boolean;
};
font?: {
bold?: boolean;
charset?: number;
color?: string;
condense?: boolean;
extend?: boolean;
family?: string;
italics?: boolean;
name?: string;
outline?: boolean;
scheme?: string;
shadow?: boolean;
strike?: boolean;
size?: number;
underline?: boolean;
vertAlign?: string;
};
border?: {
left?: {
style?: string;
color?: string;
};
right?: {
style?: string;
color?: string;
};
top?: {
style?: string;
color?: string;
};
bottom?: {
style?: string;
color?: string;
};
diagonal?: {
style?: string;
color?: string;
};
diagonalDown?: boolean;
diagonalUp?: boolean;
outline?: boolean;
};
fill?: {
type?: string;
patternType?: string;
bgColor?: string;
fgColor?: string;
};
numberFormat?: string;
}
WorkBookOpts {
debug?: boolean;
jszip?: {
compression: "DEFLATE" | string;
};
defaultFont?: {
bold?: boolean;
charset?: number;
color?: string;
condense?: boolean;
extend?: boolean;
family?: string;
italics?: boolean;
name?: string;
outline?: boolean;
scheme?: string;
shadow?: boolean;
strike?: boolean;
size?: number;
underline?: boolean;
vertAlign?: string;
};
dateFormat?: string;
workbookView?: {
activeTab?: number;
autoFilterDateGrouping?: boolean;
firstSheet?: number;
minimized?: boolean;
showHorizontalScroll?: boolean;
showSheetTabs?: boolean;
showVerticalScroll?: boolean;
tabRatio?: number;
visibility?: "hidden" | "veryHidden" | "visible";
windowHeight?: number;
windowWidth?: number;
xWindow?: number;
yWindow?: number;
};
}
WorkSheetStyle {
margins?: {
bottom?: number;
footer?: number;
header?: number;
left?: number;
right?: number;
top?: number;
};
printOptions?: {
centerHorizontal?: boolean;
centerVertical?: boolean;
printGridLines?: boolean;
printHeadings?: boolean;
};
headerFooter?: {
evenFooter?: string;
evenHeader?: string;
firstFooter?: string;
firstHeader?: string;
oddFooter?: string;
oddHeader?: string;
alignWithMargins?: boolean;
differentFirst?: boolean;
differentOddEven?: boolean;
scaleWithDoc?: boolean;
};
pageSetup?: {
blackAndWhite?: boolean;
cellComments?: "none" | "asDisplayed" | "atEnd";
copies?: number;
draft?: boolean;
errors?: "displayed" | "blank" | "dash" | "NA";
firstPageNumber?: number;
fitToHeight?: number;
fitToWidth?: number;
horizontalDpi?: number;
orientation?: "default" | "portrait" | "landscape";
pageOrder?: "downThenOver" | "overThenDown";
paperHeight?: string;
paperSize?: number;
paperWidth?: string;
scale?: number;
useFirstPageNumber?: boolean;
usePrinterDefaults?: boolean;
verticalDpi?: number;
};
sheetView?: {
pane?: {
activePane?: "bottomLeft" | "bottomRight" | "topLeft" | "topRight";
state?: "split" | "frozen" | "frozenSplit";
topLeftCell?: string;
xSplit?: number;
ySplit?: number;
};
rightToLeft?: boolean;
showGridLines?: boolean;
zoomScale?: number;
zoomScaleNormal?: number;
zoomScalePageLayoutView?: number;
};
sheetFormat?: {
baseColWidth?: number;
defaultColWidth?: number;
defaultRowHeight?: number;
thickBottom?: boolean;
thickTop?: boolean;
};
sheetProtection?: {
autoFilter?: boolean;
deleteColumns?: boolean;
deleteRows?: boolean;
formatCells?: boolean;
formatColumns?: boolean;
formatRows?: boolean;
insertColumns?: boolean;
insertHyperlinks?: boolean;
insertRows?: boolean;
objects?: boolean;
password?: string;
pivotTables?: boolean;
scenarios?: boolean;
selectLockedCells?: boolean;
selectUnlockedCells?: boolean;
sheet?: boolean;
sort?: boolean;
};
outline?: {
summaryBelow?: boolean;
summaryRight?: boolean;
};
disableRowSpansOptimization?: boolean;
hidden?: boolean;
}worksheet and filename
Add wordSheet with options and set sheet name
excel.addWorkSheet(sheetName, sheetOptions);Set save file path and file name
excel.setFileName(fineName);
excel.setSuffix(suffix); // suffix should be .xlsx and .xlsRows and Columns
Set custom widths and heights of columns/rows
excel.setRowHeight(5, 300).setColWidth(6, 70);Set rows and/or columns to create a frozen pane with an optionall scrollTo
excel.setRowFreeze(1, 1);
excel.setColFreeze(1, 1);Hide a row or column
excel.setRowHide(1, 1);
excel.setColHide(1, 1);Save as file or read as buffer
It can be save as file or write to buffer
const filePath = await excel.saveFile(); // save file and return the file path
const buffer = await excel.writeToBuffer(); // you can return this buffer to http.response, some it can be export by browserParse excel file
It can also parse a excel file to array data
const data = await excel.readExcel(filePath, sheetIndex); // save file and return the file path