public static WorksheetsCollection GetAllWorksheets(string fileName)
{
//Initialize a new Workboook object
Workbook workbook = new Workbook();
//Load the document
workbook.LoadFromFile(fileName);
//Get all worksheets
WorksheetsCollection worksheets = workbook.Worksheets;
return(worksheets);
}
// Add table to word
#region converting tables
public void Add(string filepath, List <string> names, bool boolResult, bool boolSteel)
{
//Initialize an instance of Workbook class and load excel from file
Workbook workbook = new Workbook();
workbook.LoadFromFile(filepath);
//Get all worksheets
WorksheetsCollection worksheets = workbook.Worksheets;
Section section1 = doc.Sections[0];
Worksheet sheet;
Paragraph title;
Table table;
List <int> ColumnList = new List <int>();
// Fit result's column in one table
if (boolResult)
{
numcol = 99;
names.Clear();
foreach (var item in worksheets)
{
names.Add(item.Name);
}
}
foreach (var name in names)
{
int iter = 0;
foreach (Worksheet item in worksheets)
{
if (name == item.Name)
{
//Get the worksheet
sheet = workbook.Worksheets[iter];
// Get which column to export
ColumnList.Clear();
if (!boolResult)
{
ColumnList = GetColumnList(name, boolSteel);
}
else
{
for (int i = 1; i <= sheet.LastColumn; i++)
{
ColumnList.Add(i);
}
}
if (ColumnList.Count <= numcol) // 1 part
{
//Write Name of Worksheet
title = section1.AddParagraph();
title.Text = item.Name;
title.ApplyStyle(style.Name);
//Write Table
table = section1.AddTable(true);
table.ResetCells(sheet.LastRow, ColumnList.Count);
if (!boolResult)
{
table.ApplyHorizontalMerge(0, 0, (ColumnList.Count - 1));
}
//Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table.
for (int r = 1; r <= sheet.LastRow; r++)
{
for (int c = 1; c <= ColumnList.Count; c++)
{
int c2 = ColumnList[c - 1];
CellRange xCell = sheet.Range[r, c2];
TableCell wCell = table.Rows[r - 1].Cells[c - 1];
//Fill data to Word table
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
}
}
//Set column width of Word table in Word
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Rows[i].Cells.Count; j++)
{
table.Rows[i].Cells[j].Width = 100f;
}
}
//NoBreakTable(table);
}
else if (ColumnList.Count < 2 * numcol - 1) // 2 part
{
//Part 1 of 2
title = section1.AddParagraph();
title.Text = item.Name + " Part 1 of 2";
title.ApplyStyle(style.Name);
//Write Table
table = section1.AddTable(true);
table.ResetCells(sheet.LastRow, numcol);
if (!boolResult)
{
table.ApplyHorizontalMerge(0, 0, numcol - 1);
}
//Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table.
for (int r = 1; r <= sheet.LastRow; r++)
{
for (int c = 1; c <= numcol; c++)
{
int c2 = ColumnList[c - 1];
CellRange xCell = sheet.Range[r, c2];
TableCell wCell = table.Rows[r - 1].Cells[c - 1];
//Fill data to Word table
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
}
}
//Set column width of Word table in Word
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Rows[i].Cells.Count; j++)
{
table.Rows[i].Cells[j].Width = 100f;
}
}
//NoBreakTable(table);
//Part 2 of 2
title = section1.AddParagraph();
title.Text = item.Name + " Part 2 of 2";
title.ApplyStyle(style.Name);
//Write Table
table = section1.AddTable(true);
table.ResetCells(sheet.LastRow, ColumnList.Count - numcol + 1); // first column and the rest
if (!boolResult)
{
table.ApplyHorizontalMerge(0, 0, ColumnList.Count - numcol);
}
//Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table.
for (int r = 1; r <= sheet.LastRow; r++)
{
//for c = 1
CellRange xCell = sheet.Range[r, 1];
TableCell wCell = table.Rows[r - 1].Cells[0];
//Fill data to Word table
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
for (int c = 2; c <= ColumnList.Count - numcol + 1; c++)
{
int c2 = ColumnList[numcol + c - 2];
xCell = sheet.Range[r, c2];
wCell = table.Rows[r - 1].Cells[c - 1];
//Fill data to Word table
textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
}
}
//Set column width of Word table in Word
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Rows[i].Cells.Count; j++)
{
table.Rows[i].Cells[j].Width = 100f;
}
}
//NoBreakTable(table);
}
else // more than 2 part
{
double value = (double)(ColumnList.Count - numcol) / (numcol - 1);
double nf = Math.Ceiling(value) + 1;
int n = (int)nf;
//Part 1 of n
title = section1.AddParagraph();
title.Text = item.Name + $" Part 1 of {n}";
title.ApplyStyle(style.Name);
//Write Table
table = section1.AddTable(true);
table.ResetCells(sheet.LastRow, numcol);
if (!boolResult)
{
table.ApplyHorizontalMerge(0, 0, (numcol - 1));
}
//Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table.
for (int r = 1; r <= sheet.LastRow; r++)
{
for (int c = 1; c <= numcol; c++)
{
int c2 = ColumnList[c - 1];
CellRange xCell = sheet.Range[r, c2];
TableCell wCell = table.Rows[r - 1].Cells[c - 1];
//Fill data to Word table
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
}
}
//Set column width of Word table in Word
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Rows[i].Cells.Count; j++)
{
table.Rows[i].Cells[j].Width = 100f;
}
}
//NoBreakTable(table);
//Part x of n (2 <= x <= n-1)
for (int i = 2; i < n; i++)
{
title = section1.AddParagraph();
title.Text = item.Name + $" Part {i} of {n}";
title.ApplyStyle(style.Name);
//Write Table
table = section1.AddTable(true);
table.ResetCells(sheet.LastRow, numcol); // first column and the rest
if (!boolResult)
{
table.ApplyHorizontalMerge(0, 0, (numcol - 1));
}
//Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table.
for (int r = 1; r <= sheet.LastRow; r++)
{
//for c = 1
CellRange xCell = sheet.Range[r, 1];
TableCell wCell = table.Rows[r - 1].Cells[0];
//Fill data to Word table
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
for (int c = 2; c <= numcol; c++)
{
int c2 = ColumnList[numcol + (i - 2) * (numcol - 1) + c - 2];
xCell = sheet.Range[r, c2];
wCell = table.Rows[r - 1].Cells[c - 1];
//Fill data to Word table
textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
}
}
//Set column width of Word table in Word
for (int it = 0; it < table.Rows.Count; it++)
{
for (int j = 0; j < table.Rows[it].Cells.Count; j++)
{
table.Rows[it].Cells[j].Width = 100f;
}
}
//NoBreakTable(table);
}
//Part n of n
title = section1.AddParagraph();
title.Text = item.Name + $" Part {n} of {n}";
title.ApplyStyle(style.Name);
//Write Table
table = section1.AddTable(true);
int lastcol = ColumnList.Count - (numcol + (n - 2) * (numcol - 1));
table.ResetCells(sheet.LastRow, lastcol + 1); // first column and the rest
if (!boolResult)
{
table.ApplyHorizontalMerge(0, 0, lastcol);
}
//Traverse the rows and columns of table in worksheet and get the cells, call a custom function CopyStyle() to copy the font style and cell style from Excel to Word table.
for (int r = 1; r <= sheet.LastRow; r++)
{
//for c = 1
CellRange xCell = sheet.Range[r, 1];
TableCell wCell = table.Rows[r - 1].Cells[0];
//Fill data to Word table
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
for (int c = 2; c <= lastcol + 1; c++)
{
int c2 = ColumnList[numcol + (n - 2) * (numcol - 1) + c - 2];
xCell = sheet.Range[r, c2];
wCell = table.Rows[r - 1].Cells[c - 1];
//Fill data to Word table
textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//Copy the formatting of table to Word
CopyStyle(textRange, xCell, wCell);
}
}
//Set column width of Word table in Word
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Rows[i].Cells.Count; j++)
{
table.Rows[i].Cells[j].Width = 100f;
}
}
//NoBreakTable(table);
}
section1.AddParagraph().Text = ""; //Buffer
//continue;
break;
}
iter++;
}
}
}
public int ReadFile(string fileLocation)
{
string[] files = System.IO.Directory.GetFiles(fileLocation, "*.xlsx");
if (files.Length < 1)
{
return(0);
}
var reportingModel = new List <ReportingModel>();
foreach (var file in files)
{
int weekNumberRow = 5;
Workbook workbook = new Workbook();
workbook.LoadFromFile(file);
WorksheetsCollection sheets = workbook.Worksheets;
foreach (var sheet in sheets)
{
int startingRow = 9;
var sheetName = sheet.CodeName;
if (sheet.CodeName.ToLower().Contains("household"))
{
startingRow = 10;
}
int columnCount = sheet.Columns.Length;
int rowCount = sheet.Rows.Length;
for (int start = startingRow; start < rowCount; start++)
{
var location = sheet.Rows[start].Columns[0].Value;
var weekNumber = sheet.Rows[weekNumberRow].Columns[1].Value;
for (int c = 1; c < columnCount; c++)
{
var product = sheet.Rows[startingRow - 1].Columns[c].Value;
var sales = sheet.Rows[start].Columns[c].Value;
if (!string.IsNullOrWhiteSpace(sales))
{
var data = new ReportingModel
{
Location = location,
Week = weekNumber,
Product = product,//.Split("|")[0],
ProductType = sheetName,
Sales = sales
};
reportingModel.Add(data);
}
}
}
}
}
try
{
CreateFile(reportingModel);
MoveFile(files);
}
catch (Exception ex)
{
LogFile.Write("An Error has accure white creating entries");
}
return(reportingModel.Count);
}
