public void TestCloneSheetIntStringValidName()
{
ISheet cloned = wb.CloneSheet(0, OTHER_SHEET_NAME);
Assert.AreEqual(OTHER_SHEET_NAME, cloned.SheetName);
Assert.AreEqual(2, wb.NumberOfSheets);
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("new sheet");
wb.CreateSheet("second sheet");
ISheet cloneSheet = wb.CloneSheet(0);
FileStream sw = File.Create("newWorksheet.xls");
wb.Write(sw);
sw.Close();
}
public void TestClone()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
XSSFSheet sheet1 = wb.GetSheetAt(0) as XSSFSheet;
XSSFSheet sheet2 = wb.CloneSheet(0) as XSSFSheet;
//the source sheet has one relationship and it is XSSFDrawing
List <POIXMLDocumentPart> rels1 = sheet1.GetRelations();
Assert.AreEqual(1, rels1.Count);
Assert.IsTrue(rels1[(0)] is XSSFDrawing);
List <POIXMLDocumentPart> rels2 = sheet2.GetRelations();
Assert.AreEqual(1, rels2.Count);
Assert.IsTrue(rels2[(0)] is XSSFDrawing);
XSSFDrawing drawing1 = (XSSFDrawing)rels1[0];
XSSFDrawing drawing2 = (XSSFDrawing)rels2[0];
Assert.AreNotSame(drawing1, drawing2); // Drawing2 is a clone of Drawing1
List <XSSFShape> shapes1 = drawing1.GetShapes();
List <XSSFShape> shapes2 = drawing2.GetShapes();
Assert.AreEqual(shapes1.Count, shapes2.Count);
for (int i = 0; i < shapes1.Count; i++)
{
XSSFShape sh1 = (XSSFShape)shapes1[(i)];
XSSFShape sh2 = (XSSFShape)shapes2[i];
Assert.IsTrue(sh1.GetType() == sh2.GetType());
Assert.AreEqual(sh1.GetShapeProperties().ToString(), sh2.GetShapeProperties().ToString());
}
checkRewrite(wb);
wb.Close();
}
public async Task <MemoryStream> Merge(Reports reports, IFormFile excelFile)
{
string sFileName = excelFile.FileName;
string sWebRootFolder = _webHostEnvironment.WebRootPath;
XSSFWorkbook workbook;
MemoryStream memory = new MemoryStream();
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.ReadWrite))
{
excelFile.CopyTo(stream);
stream.Position = 0;
workbook = new XSSFWorkbook(stream);
}
if (workbook.GetSheet("Final") == null)
{
workbook.CloneSheet(0, "Final");
}
var finalSheet = workbook.GetSheet("Final");
foreach (var reportVal in reports.Report.ReportVal)
{
int cellRow = 0;
int cellCol = 0;
for (int row = 10; row <= 11; row++)
{
if (finalSheet.GetRow(row).GetCell(1).StringCellValue.Substring(1) == reportVal.ReportRow)
{
cellRow = row;
break;
}
}
for (int col = 4; col <= 10; col++)
{
if (finalSheet.GetRow(9).GetCell(col).StringCellValue.Substring(1) == reportVal.ReportCol)
{
cellCol = col;
break;
}
}
if (cellRow != 0 && cellCol != 0)
{
var cell = finalSheet.GetRow(cellRow).GetCell(cellCol);
cell.SetCellValue(reportVal.Val);
}
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
return(memory);
}
public void GenerateDocument(List <string> tableNames, string templatePath, string outputDocPath)
{
const string sheetName4TableListTempalte = "#TableListTemplate";
const string sheetName4TableSchemaTempalte = "#TableSchemaTemplate";
const string tagName4TableNo = "#table.no";
const string tagName4TableName = "#table.name";
const string tagName4TableDesc = "#table.description";
const string tagName4TableView = "#table.view";
const string tagName4TableType = "#table.type";
const string tagName4ColumnNo = "#column.no";
const string tagName4ColumnName = "#column.name";
const string tagName4ColumnPK = "#column.pk";
const string tagName4ColumnFK = "#column.fk";
const string tagName4ColumnFKReference = "#column.fkreference";
const string tagName4ColumnNullable = "#column.nullable";
const string tagName4ColumnIdentity = "#column.identity";
const string tagName4ColumnDataType = "#column.datatype";
const string tagName4ColumnDefault = "#column.default";
const string tagName4ColumnDesc = "#column.description";
// open template file
XSSFWorkbook workbook;
using (FileStream fs = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fs);
}
// TABLE LIST
// clone tempalte table list sheet & rename sheet
var tableSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableListTempalte));
workbook.SetSheetName(workbook.NumberOfSheets - 1, "Table List");
var tableIndex = 1;
foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName)))
{
var noLocation = tableSheet.FindCellLocation(tagName4TableNo);
if (noLocation.HasValue)
{
// copy the table info row from tempalte
tableSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1);
var newRowIndex = noLocation.Value.Y;
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableNo, tableIndex.ToString());
tableSheet.SetFirstMatchCellHyperlinkInRow(newRowIndex, tagName4TableName, table.TableName, tableIndex.ToString());
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableDesc, table.Description);
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableView, table.IsViewTable ? "V" : "");
tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableType, table.TableType.ToTitleCase());
tableIndex++;
}
}
// remove the last template row
tableSheet.RemoveFirstMatchRow(tagName4TableNo);
// TABLE SCHEMA SHEETS
tableIndex = 1;
foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName)))
{
// clone tempalte schema sheet & rename sheet
var schemaSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableSchemaTempalte));
workbook.SetSheetName(workbook.NumberOfSheets - 1, tableIndex.ToString() /*table.TableName*/);
// table name & description
schemaSheet.SetFirstMatchCellContent(tagName4TableName, table.TableName);
schemaSheet.SetFirstMatchCellContent(tagName4TableDesc, table.Description);
schemaSheet.SetFirstMatchCellContent(tagName4TableView, table.IsViewTable ? "V" : "");
schemaSheet.SetFirstMatchCellContent(tagName4TableType, table.TableType.ToTitleCase());
// prepare each column info
foreach (var column in table.Columns)
{
var noLocation = schemaSheet.FindCellLocation(tagName4ColumnNo);
if (noLocation.HasValue)
{
// copy the column info row from tempalte
schemaSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1);
var newRowIndex = noLocation.Value.Y;
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNo, column.No.ToString());
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnName, column.ColumnName);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnPK, column.IsPrimaryKey ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFK, column.IsForeignKey ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFKReference, column.FkReferencedInfo);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNullable, column.IsNullable ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnIdentity, column.IsIdentity ? "V" : "");
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDataType, column.FullDataType);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDefault, column.Default);
schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDesc, column.Description);
}
}
// remove the last template row
schemaSheet.RemoveFirstMatchRow(tagName4ColumnNo);
tableIndex++;
}
// remove template sheet
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableListTempalte));
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableSchemaTempalte));
// save as another excel file
using (FileStream stream = new FileStream(outputDocPath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
// set null
workbook = null;
}