/// <summary>
/// Sets the height of a row in a worksheet
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="hidden">if set to <c>true</c> [hidden].</param>
public static void SetRowHidden(this SheetData sheetData, uint rowIndex, bool hidden)
{
IEnumerable <Row> rows = sheetData.OfType <Row>();
Row row = rows.FirstOrDefault(r => r.RowIndex == rowIndex);
if (row != null)
{
row.Hidden = new BooleanValue(hidden);
}
}
/// <summary>
/// Sets the height of a row in a worksheet
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="height">The height.</param>
public static void SetRowHeight(this SheetData sheetData, uint rowIndex, double height)
{
IEnumerable <Row> rows = sheetData.OfType <Row>();
Row row = rows.FirstOrDefault(r => r.RowIndex == rowIndex);
if (row != null)
{
row.Height = new DoubleValue(height);
row.CustomHeight = new BooleanValue(true);
}
}
private static string[][] ParseSheet(SpreadsheetDocument doc, Worksheet sheet, string sheetName)
{
SheetData sd = sheet.OfType <SheetData>().FirstOrDefault();
if (sd == null)
{
return(new string[0][] { });
}
List <string[]> datas = new List <string[]>();
var rows = sd.OfType <Row>().Where(obj => obj.Spans != null && obj.Spans.Items != null && obj.Spans.Items.First() != null).ToArray();
//检查所有行中最大的列
var rowSpans = rows.Select(obj => obj.Spans.Items.First().Value).ToArray();
rowSpans = rowSpans.Select(obj => obj.Substring(obj.IndexOf(':') + 1)).ToArray();
int maxCount = rowSpans.Select(obj => int.Parse(obj)).Max();
foreach (var row in rows)
{
var data = new string[maxCount + 1];
data[0] = row.RowIndex.Value.ToString();
foreach (var cell in row.OfType <Cell>())
{
try
{
var v = cell.CellValue;
string address = cell.CellReference;//行列位置如 A1,B1
int index = address.IndexOfAny(NUMBERS);
if (index < 1)
{
throw new Exception("表格地址不对无法解析");
}
int colNumber = GetExcelColumnIndex(address.Substring(0, index));
data[colNumber] = ReadCellValue(doc, sheet, cell);
}
catch (Exception ex)
{
throw new Exception(string.Format("解析表:{0},位置:{1}{2}出错{3}", sheetName, cell.CellReference, Environment.NewLine, ex.Message), ex);
}
}
datas.Add(data);
}
return(datas.ToArray());
}
/// <summary>
/// Gets the cell at the specified coordinates or creates it if a cell does not exist there.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <returns>
/// The new cell.
/// </returns>
public static Cell GetCell(this SheetData sheetData, uint columnIndex, uint rowIndex)
{
while (rowIndex > sheetData.OfType <Row>().Count())
{
sheetData.AddRow();
}
var row = (Row)sheetData.ElementAt((int)rowIndex - 1);
while (columnIndex > row.OfType <Cell>().Count())
{
var paddingCell = new Cell();
paddingCell.SetCellReference((uint)row.OfType <Cell>().Count() + 1, rowIndex);
row.Append(paddingCell);
}
Cell cell = (Cell)row.ElementAt((int)columnIndex - 1);
return(cell);
}
private static Row GetRow(SheetData sheetData, uint rowIndex)
{
return(sheetData.OfType <Row>().FirstOrDefault(r => r.RowIndex == rowIndex));
}
/// <summary>
/// Gets the row count.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <returns>
/// The number of rows.
/// </returns>
public static int GetRowCount(this SheetData sheetData)
{
return(sheetData.OfType <Row>().Count());
}
/// <summary>
/// Gets a row if it exists in a worksheet. Returns null if row does not exist.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <returns></returns>
public static Row GetRowIfExists(this SheetData sheetData, uint rowIndex)
{
return(sheetData.OfType <Row>().FirstOrDefault(r => r.RowIndex == rowIndex));
}
