public static Cell InsertCell(this SheetData data, WorkbookPart book, string columnName,
uint rowIndex, string text,
CellValues dataType = CellValues.SharedString, string formula = null,
bool isPercentage = false, log4net.ILog logger = null,
Stopwatch sw = null)
{
var currentCell = data.GetCell(rowIndex, columnName);
//Log(logger, sw);
var shareStringPart = dataType == CellValues.SharedString ? GetSharedStringTablePart(book) : null;
//Log(logger, sw);
var index = dataType == CellValues.SharedString
? shareStringPart.InsertSharedStringItem(text).ToString() : text;
//Log(logger, sw);
var x = currentCell.StyleIndex != null
? currentCell.StyleIndex : dataType == CellValues.Number
? isPercentage ? book.GetPercentageStyleIndex()
: book.GetDoubleStyleIndex()
: null;
//Log(logger, sw);
currentCell.CellFormula = !string.IsNullOrWhiteSpace(formula) ? new CellFormula(formula) : null;
//Log(logger, sw);
currentCell.CellValue = new CellValue(dataType == CellValues.Number ? index.Replace(',', '.')
: index);
//Log(logger, sw);
currentCell.DataType = dataType != CellValues.Number ? new EnumValue <CellValues>(dataType) : null;
//Log(logger, sw);
currentCell.StyleIndex = x;
//Log(logger, sw);
return(currentCell);
}
/// <summary>
/// Sets the cell contents and style. Handles formatting of the column automatically.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="value">The value.</param>
/// <returns></returns>
public static Cell SetCell(this SheetData sheetData, uint columnIndex, uint rowIndex, object value)
{
Cell cell = sheetData.GetCell(columnIndex, rowIndex);
cell.SetDataType(value);
cell.SetCellValue(value);
return(cell);
}
/// <summary>
/// 修改单元格的内容.
/// </summary>
/// <param name="sheetData">
/// The sheet data.
/// </param>
/// <param name="cellName">
/// The cell name.
/// </param>
/// <param name="cellText">
/// The cell text.
/// </param>
public static void UpdateCellText(this SheetData sheetData, string cellName, string cellText)
{
var cell = sheetData.GetCell(cellName);
if (cell == null)
{
return;
}
cell.UpdateCellText(cellText);
}
/// <summary><see cref="SheetData"/>に 数字を設定</summary>
/// <param name="data"></param>
/// <param name="rowIndex"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
public static void SetNumber <T>(this SheetData data, uint rowIndex, string columnName, T value, uint?styleIndex = null) where T : struct
{
var cell = data.GetCell(rowIndex, columnName);
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(value.ToString());
if (styleIndex.HasValue)
{
cell.StyleIndex = styleIndex.Value;
}
}
/// <summary><see cref="SheetData"/>に 日付を設定</summary>
/// <param name="data"></param>
/// <param name="rowIndex"></param>
/// <param name="columnName"></param>
/// <param name="date"></param>
public static void SetDate(this SheetData data, uint rowIndex, string columnName, DateTime date, uint?styleIndex = null)
{
var cell = data.GetCell(rowIndex, columnName);
//cell.DataType = CellValues.Date;
//cell.CellValue = new CellValue(date);
cell.CellValue = new CellValue(date.ToOADate().ToString(System.Globalization.CultureInfo.InvariantCulture));
if (styleIndex.HasValue)
{
cell.StyleIndex = styleIndex.Value;
}
}
/// <summary><see cref="SheetData"/>に 文字を設定</summary>
/// <param name="data"></param>
/// <param name="rowIndex"></param>
/// <param name="columnName"></param>
/// <param name="text"></param>
public static void SetText(this SheetData data, uint rowIndex, string columnName, string text, uint?styleIndex = null)
{
var worksheet = data.GetWorksheet();
var bookPart = worksheet.WorksheetPart.GetWorkbookPart();
var tablePart = bookPart.GetSharedStringTablePart();
var cell = data.GetCell(rowIndex, columnName);
cell.DataType = CellValues.SharedString;
cell.CellValue = new CellValue(tablePart.GetIndex(text).ToString());
if (styleIndex.HasValue)
{
cell.StyleIndex = styleIndex.Value;
}
}
/// <summary><see cref="SheetData"/>に 数字を設定</summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="rowIndex"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <param name="styleIndex"></param>
public static void SetNumber <T>(this SheetData data, uint rowIndex, string columnName, T?value, uint?styleIndex = null) where T : struct
{
if (value.HasValue)
{
data.SetNumber(rowIndex, columnName, value.Value, styleIndex);
}
else
{
var cell = data.GetCell(rowIndex, columnName);
if (styleIndex.HasValue)
{
cell.StyleIndex = styleIndex.Value;
}
}
}
/// <summary>
/// Sets the value.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetData">The sheet data.</param>
/// <param name="cellReference">The cell reference.</param>
/// <param name="value">The value.</param>
public static void SetValue <T>(this SheetData sheetData, string cellReference, T value)
{
sheetData.GetCell(cellReference, true).SetValue(value);
}
/// <summary>特定セルに書式設定</summary>
/// <param name="data"></param>
/// <param name="rowIndex"></param>
/// <param name="columnName"></param>
/// <param name="styleIndex"></param>
public static void SetStyle(this SheetData data, uint rowIndex, string columnName, uint styleIndex)
{
var cell = data.GetCell(rowIndex, columnName);
cell.StyleIndex = styleIndex;
}
