public static Row FindRow(SheetData sheetData, uint rowIndex)
{
Row newChild = null;
uint index = rowIndex;
if (sheetData.Elements <Row>().Where(r => r.RowIndex.Value == rowIndex).Count <Row>() != 0)
{
return(sheetData.Elements <Row>().Where(r => r.RowIndex.Value == rowIndex).First());
}
newChild = new Row();
newChild.RowIndex = index;
int num = 0;
foreach (Row row2 in sheetData.Elements <Row>())
{
if (row2.RowIndex.Value > rowIndex)
{
sheetData.InsertAt <Row>(newChild, num);
return(newChild);
}
num++;
}
sheetData.AppendChild(newChild);
return(newChild);
}
public static int InsertRow(WorksheetPart worksheetPart)
{
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
if (lastRow != null)
{
sheetData.InsertAfter(new Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
return((int)UInt32Value.ToUInt32(lastRow.RowIndex));
}
else
{
sheetData.InsertAt(new Row()
{
RowIndex = 0
}, 0);
return(0);
}
}
/// <summary>
/// Add row moi vao sheetdata
/// </summary>
/// <param name="sheetName"></param>
/// <param name="workbookPart"></param>
/// <returns></returns>
public Row RowAddNew(SheetData sheetData)
{
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
Row newRow;
if (lastRow != null)
{
newRow = new Row()
{
RowIndex = (lastRow.RowIndex + 1)
};
sheetData.InsertAfter(newRow, lastRow);
}
else
{
newRow = new Row()
{
RowIndex = 0
};
sheetData.InsertAt(newRow, 0);
}
return(newRow);
}
/// <summary>
/// json rows to openXml excel object
/// see https://blog.johnwu.cc/article/asp-net-core-export-to-excel.html
/// </summary>
/// <param name="rows">json array</param>
/// <param name="docx"></param>
/// <param name="srcRowNo">excel start row, base 1</param>
/// <returns>error msg if any</returns>
public static string DocxByRows(JArray rows, SpreadsheetDocument docx, int srcRowNo)
{
#region 1.check docx
if (docx == null)
{
return("_Excel.cs RowsToDocx() failed, docx is null.");
}
#endregion
//2.get col name list from source rows[0]
var rowCount = (rows == null) ? 0 : rows.Count;
var cols = new List <string>();
if (rowCount > 0)
{
foreach (var item in (JObject)rows[0])
{
cols.Add(item.Key);
}
}
#region prepare excel variables
SheetData sheetData = null;
var colCount = cols.Count;
var sheet = docx.WorkbookPart.Workbook.Descendants <Sheet>().FirstOrDefault();
var wsPart = (WorksheetPart)docx.WorkbookPart.GetPartById(sheet.Id);
sheetData = wsPart.Worksheet.GetFirstChild <SheetData>();
#endregion
//3.loop of write excel rows, use template
for (var rowNo = 0; rowNo < rowCount; rowNo++)
{
//add row and fill data, TODO: copy row style
var row = (JObject)rows[rowNo];
var newRow = new Row();
for (var colNo = 0; colNo < colCount; colNo++)
{
newRow.Append(new Cell()
{
CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
DataType = CellValues.String,
});
}
//insert row into sheet
sheetData.InsertAt(newRow, rowNo + srcRowNo);
}
//case of ok
return("");
#region remark: no template file
/*
* else
* {
* //no template file
#region prepre excel-sheetData
* var bookPart = docx.AddWorkbookPart();
* bookPart.Workbook = new Workbook();
*
* var sheetPart = bookPart.AddNewPart<WorksheetPart>();
* sheetPart.Worksheet = new Worksheet(new SheetData());
*
* var sheets = bookPart.Workbook.AppendChild(new Sheets());
* sheets.Append(new Sheet()
* {
* Id = bookPart.GetIdOfPart(sheetPart),
* SheetId = 1,
* Name = "Sheet1",
* });
* sheetData = sheetPart.Worksheet.GetFirstChild<SheetData>();
#endregion
*
* //add header row
* //srcRowNo = 1; //base 1
* var newRow = new Row();
* for (var i = 0; i < colCount; i++)
* {
* newRow.Append(new Cell()
* {
* CellValue = new CellValue(cols[i]),
* DataType = CellValues.String,
* });
* }
* sheetData.AppendChild(newRow);
*
* //write excel row, no template
* for (var rowNo = 0; rowNo < rowCount; rowNo++)
* {
* //var excelRow = NewRow(row, colCount, cols);
* var row = (JObject)rows[rowNo];
* newRow = new Row();
* for (var colNo = 0; colNo < colCount; colNo++)
* {
* newRow.Append(new Cell()
* {
* CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
* DataType = CellValues.String,
* });
* }
* sheetData.AppendChild(newRow);
* }
* }
*/
#endregion
}
/*
* public static void DocxByCrud(ReadModel crud, JObject findJson, SpreadsheetDocument docx, string sheetName, List<string> headers = null, List<string> cols = null, string dbStr = "")
* {
* DocxByRows(new CrudRead(dbStr).GetAllRows(crud, findJson), docx, sheetName, headers, cols);
* }
*
* public static void DocxBySql(string sql, SpreadsheetDocument docx, string sheetName, List<string> headers = null, List<string> cols = null, string dbStr = "")
* {
* var rows = _Db.GetJsons(sql, null, dbStr);
* DocxByRows(rows, docx, sheetName, headers, cols);
* }
*/
/// <summary>
/// json rows to openXml object
/// see https://blog.johnwu.cc/article/asp-net-core-export-to-excel.html
/// </summary>
/// <param name="rows">json array</param>
/// <param name="docx"></param>
/// <param name="srcRowNo">資料開始列數, base 1, 如果為0表示沒有使用template file, 則此參數自動設為1</param>
/// <param name="sheetName">default 'Sheet1', excel save sheet name</param>
/// //<param name="template">excel template, 如果空白, 則欄位名稱會使用row field id</param>
/// //<param name="headers">excel欄位header, 如果空白, 則使用欄位id</param>
/// //<param name="cols">要輸出的excel欄位, 如果空白, 則全部輸出</param>
//public static void DocxByRows(JArray rows, SpreadsheetDocument docx, string template, string sheetName, List<string> headers = null, List<string> cols = null)
public static void DocxByRows(JArray rows, SpreadsheetDocument docx, int srcRowNo = 1)
{
//check
if (docx == null)
{
return;
}
//set cols
var rowCount = (rows == null) ? 0 : rows.Count;
var cols = new List <string>();
if (rowCount > 0)
{
foreach (var item in (JObject)rows[0])
{
cols.Add(item.Key);
}
}
//if (headers == null)
// headers = cols;
SheetData sheetData = null;
var hasTemplate = (srcRowNo > 0);
var colCount = cols.Count;
//Row srcRow = null;
//Row nowRow = null;
//Row newRow = null;
if (hasTemplate)
{
//使用範本
#region prepre excel-sheetData
var sheet = docx.WorkbookPart.Workbook.Descendants <Sheet>().FirstOrDefault();
var wsPart = (WorksheetPart)docx.WorkbookPart.GetPartById(sheet.Id);
//var ws = wsPart.Worksheet;
sheetData = wsPart.Worksheet.GetFirstChild <SheetData>();
#endregion
//var count = sheetData.Elements<Row>().Count();
//TODO: copy row style
//srcRow = sheetData.Elements<Row>().ElementAt(srcRowNo);
//寫入excel row, 使用範本
for (var rowNo = 0; rowNo < rowCount; rowNo++)
{
//第一筆時不必新增一列
var row = (JObject)rows[rowNo];
/*
* if (rowNo == 0)
* {
* nowRow = sheetData.Elements<Row>().ElementAt(srcRowNo);
* Cell cell;
* for (var colNo = 0; colNo < colCount; colNo++)
* {
* cell = nowRow.Elements<Cell>().ElementAt(colNo);
* cell.CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString());
* cell.DataType = CellValues.String;
* }
* }
* else
* {
*/
//新增一列 & 填入欄位
//TODO: copy row style
var newRow = new Row();
for (var colNo = 0; colNo < colCount; colNo++)
{
newRow.Append(new Cell()
{
CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
DataType = CellValues.String,
});
}
sheetData.InsertAt(newRow, rowNo + srcRowNo);
//}
}
}
else
{
//沒有範本
#region prepre excel-sheetData
var bookPart = docx.AddWorkbookPart();
bookPart.Workbook = new Workbook();
var sheetPart = bookPart.AddNewPart <WorksheetPart>();
sheetPart.Worksheet = new Worksheet(new SheetData());
var sheets = bookPart.Workbook.AppendChild(new Sheets());
sheets.Append(new Sheet()
{
Id = bookPart.GetIdOfPart(sheetPart),
SheetId = 1,
Name = "Sheet1",
});
sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
#endregion
//add header row
//srcRowNo = 1; //base 1
var newRow = new Row();
for (var i = 0; i < colCount; i++)
{
newRow.Append(new Cell()
{
CellValue = new CellValue(cols[i]),
DataType = CellValues.String,
});
}
sheetData.AppendChild(newRow);
//寫入excel row, 沒有範本
for (var rowNo = 0; rowNo < rowCount; rowNo++)
{
//var excelRow = NewRow(row, colCount, cols);
var row = (JObject)rows[rowNo];
newRow = new Row();
for (var colNo = 0; colNo < colCount; colNo++)
{
newRow.Append(new Cell()
{
CellValue = new CellValue(row[cols[colNo]] == null ? "" : row[cols[colNo]].ToString()),
DataType = CellValues.String,
});
}
sheetData.AppendChild(newRow);
}
}
}
