/// <summary> Constructs this object from a string
///
/// </summary>
/// <param name="s">the string
/// </param>
/// <param name="w">the external sheet
/// </param>
/// <exception cref=""> FormulaException
/// </exception>
public CellReference3d(string s, ExternalSheet w)
{
workbook = w;
columnRelative = true;
rowRelative = true;
// Get the cell details
int sep = s.IndexOf((System.Char) '!');
string cellString = s.Substring(sep + 1);
column = CellReferenceHelper.getColumn(cellString);
row = CellReferenceHelper.getRow(cellString);
// Get the sheet index
string sheetName = s.Substring(0, (sep) - (0));
// Remove single quotes, if they exist
if (sheetName[0] == '\'' && sheetName[sheetName.Length - 1] == '\'')
{
sheetName = sheetName.Substring(1, (sheetName.Length - 1) - (1));
}
sheet = w.getExternalSheetIndex(sheetName);
if (sheet < 0)
{
throw new FormulaException(FormulaException.sheetRefNotFound, sheetName);
}
}
/**
* Gets the string version of this area
*
* @param buf the area to populate
*/
public override void getString(StringBuilder buf)
{
CellReferenceHelper.getCellReference
(sheet, columnFirst, rowFirst, workbook, buf);
buf.Append(':');
CellReferenceHelper.getCellReference(columnLast, rowLast, buf);
}
private async void btnView_Click(object sender, EventArgs e)
{
Excel.Worksheet sheet = _sheets[view.SelectedSheet];
if (sheet == null)
{
return;
}
var endCol = CellReferenceHelper.GetColumnIndex(view.EndColumnName);
if (endCol > ExcelLimits.ColumnsMax)
{
view.EndColumnName = ExcelLimits.ColumnsMaxName;
}
else if (string.IsNullOrWhiteSpace(view.EndColumnName))
{
view.EndColumnName = "A";
}
var endColName = view.EndColumnName;
stopwatch.Restart();
labelDataReadState.Text = string.Empty;
var data = await Task.Factory.StartNew(() => WorksheetHelper.GetRowContent(sheet, view.RataRowIndex, endColName));
stopwatch.Stop();
var dataElapsed = stopwatch.Elapsed;
stopwatch.Start();
ShowData(data, dataGridView);
stopwatch.Stop();
labelDataReadState.Text = $"总计行数：{data.Count}，数据解析耗时：{dataElapsed}，总计耗时：{stopwatch.Elapsed}";
}
/// <summary> Constructor invoked when parsing a text string
///
/// </summary>
/// <param name="s">the string being parsed
/// </param>
public CellReference(string s)
{
column = CellReferenceHelper.getColumn(s);
row = CellReferenceHelper.getRow(s);
columnRelative = CellReferenceHelper.isColumnRelative(s);
rowRelative = CellReferenceHelper.isRowRelative(s);
}
private static List <LocationWithValue> GetRowFieldsForFill(Row rowTemplate, WorkbookPart workbookPart, string[] tableNames)
{
var fields = new List <LocationWithValue>();
foreach (var cell in rowTemplate.Descendants <Cell>())
{
var cellValue = CellHelper.GetCellValue(cell, workbookPart);
if (String.IsNullOrWhiteSpace(cellValue) || cellValue.Length <= 4)
{
continue;
}
if (!cellValue.StartsWith("{{") || !cellValue.EndsWith("}}"))
{
continue;
}
cellValue = cellValue.Substring(2, cellValue.Length - 4);
foreach (var tableName in tableNames)
{
if (cellValue.IndexOf($"{tableName}.", StringComparison.Ordinal) != -1)
{
var rowIndex = CellReferenceHelper.GetRowIndex(cell.CellReference.Value);
var columnIndex = CellReferenceHelper.GetColumnIndex(cell.CellReference.Value);
fields.Add(new LocationWithValue(rowIndex, columnIndex, cellValue));
}
}
}
return(fields);
}
/**
* Constructor invoked when parsing a string formula
*
* @param s the string to parse
*/
public ColumnRange(string s)
: base()
{
int seppos = s.IndexOf(":");
Assert.verify(seppos != -1);
string startcell = s.Substring(0, seppos);
string endcell = s.Substring(seppos + 1);
int columnFirst = CellReferenceHelper.getColumn(startcell);
int rowFirst = 0;
int columnLast = CellReferenceHelper.getColumn(endcell);
int rowLast = 0xffff;
bool columnFirstRelative =
CellReferenceHelper.isColumnRelative(startcell);
bool rowFirstRelative = false;
bool columnLastRelative = CellReferenceHelper.isColumnRelative(endcell);
bool rowLastRelative = false;
setRangeData(columnFirst, columnLast,
rowFirst, rowLast,
columnFirstRelative, columnLastRelative,
rowFirstRelative, rowLastRelative);
}
/// <summary>
/// 显示数据
/// </summary>
/// <param name="data"></param>
/// <param name="dataGridView"></param>
private static void ShowData(SortedDictionary <int, RowContent> data, DataGridView dataGridView)
{
dataGridView.Columns.Clear();
dataGridView.Rows.Clear();
if (data.Count == 0)
{
return;
}
// 添加第一列，用于显示原始数据所在excel中的列
dataGridView.Columns.Add(new DataGridViewColumn()
{
SortMode = DataGridViewColumnSortMode.NotSortable,
HeaderText = "Excel",
Width = 40,
});
// 开始列索引
var startIndex = data.Max(x => x.Value.MinColoumnIndex);
// 结束列索引
var endIndex = data.Max(x => x.Value.MaxColoumnIndex);
for (int i = startIndex; i <= endIndex; i++)
{
DataGridViewColumn dc = new DataGridViewColumn()
{
SortMode = DataGridViewColumnSortMode.NotSortable,
HeaderText = CellReferenceHelper.GetColumnReference(i - 1),
CellTemplate = new DataGridViewTextBoxCell(),
};
dataGridView.Columns.Add(dc);
}
foreach (var item in data)
{
DataGridViewRow dr = new DataGridViewRow();
dr.Cells.Add(new DataGridViewTextBoxCell()
{
Value = item.Key,
Style = new DataGridViewCellStyle()
{
Alignment = DataGridViewContentAlignment.MiddleCenter
},
});
for (int i = startIndex; i <= endIndex; i++)
{
var dc = new DataGridViewTextBoxCell();
bool s = item.Value.TryGetValue(i, out var val);
if (s)
{
dc.Value = val.Value;
dc.ToolTipText = $"{val.Value} {val.Value?.GetType().Name}";
}
dr.Cells.Add(dc);
}
dataGridView.Rows.Add(dr);
}
}
/**
* Gets the string representation of this column range
*
* @param buf the string buffer to append to
*/
public override void getString(StringBuilder buf)
{
buf.Append('\'');
buf.Append(workbook.getExternalSheetName(sheet));
buf.Append('\'');
buf.Append('!');
CellReferenceHelper.getColumnReference(getFirstColumn(), buf);
buf.Append(':');
CellReferenceHelper.getColumnReference(getLastColumn(), buf);
}
/**
* Constructor
*
* @param w The workbook to interrogate
* @param out The output stream to which the CSV values are written
* @param encoding The encoding used by the output stream. Null or
* unrecognized values cause the encoding to default to UTF8
* @exception java.io.IOException
*/
public Features(Workbook w, TextWriter os, string encoding)
{
if (encoding == null || encoding != "UnicodeBig")
{
encoding = "UTF8";
}
try
{
//OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
//BufferedWriter os = new BufferedWriter(osw);
for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++)
{
Sheet s = w.getSheet(sheet);
os.Write(s.getName());
os.WriteLine();
Cell[] row = null;
Cell c = null;
for (int i = 0; i < s.getRows(); i++)
{
row = s.getRow(i);
for (int j = 0; j < row.Length; j++)
{
c = row[j];
if (c.getCellFeatures() != null)
{
CellFeatures features = c.getCellFeatures();
StringBuilder sb = new StringBuilder();
CellReferenceHelper.getCellReference
(c.getColumn(), c.getRow(), sb);
os.Write("Cell " + sb.ToString() +
" contents: " + c.getContents());
os.Flush();
os.Write(" comment: " + features.getComment());
os.Flush();
os.WriteLine();
}
}
}
}
os.Flush();
//os.close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
/**
* Constructor invoked when parsing a string formula
*
* @param s the string to parse
* @param es the external sheet
* @exception FormulaException
*/
public ColumnRange3d(string s, ExternalSheet es)
: base(es)
{
workbook = es;
int seppos = s.LastIndexOf(":");
Assert.verify(seppos != -1);
string startcell = s.Substring(0, seppos);
string endcell = s.Substring(seppos + 1);
// Get the the start cell details
int sep = s.IndexOf('!');
string cellString = s.Substring(sep + 1, seppos);
int columnFirst = CellReferenceHelper.getColumn(cellString);
int rowFirst = 0;
// Get the sheet index
string sheetName = s.Substring(0, sep);
int sheetNamePos = sheetName.LastIndexOf(']');
// Remove single quotes, if they exist
if (sheetName[0] == '\'' &&
sheetName[sheetName.Length - 1] == '\'')
{
sheetName = sheetName.Substring(1, sheetName.Length - 1);
}
sheet = es.getExternalSheetIndex(sheetName);
if (sheet < 0)
{
throw new FormulaException(FormulaException.SHEET_REF_NOT_FOUND, sheetName);
}
// Get the last cell index
int columnLast = CellReferenceHelper.getColumn(endcell);
int rowLast = 0xffff;
bool columnFirstRelative = true;
bool rowFirstRelative = true;
bool columnLastRelative = true;
bool rowLastRelative = true;
setRangeData(sheet, columnFirst, columnLast, rowFirst, rowLast,
columnFirstRelative, rowFirstRelative,
columnLastRelative, rowLastRelative);
}
/// <summary> Constructor invoked when parsing a string formula
///
/// </summary>
/// <param name="s">the string to parse
/// </param>
internal Area(string s)
{
int seppos = s.IndexOf(":");
Assert.verify(seppos != -1);
string startcell = s.Substring(0, (seppos) - (0));
string endcell = s.Substring(seppos + 1);
columnFirst = CellReferenceHelper.getColumn(startcell);
rowFirst = CellReferenceHelper.getRow(startcell);
columnLast = CellReferenceHelper.getColumn(endcell);
rowLast = CellReferenceHelper.getRow(endcell);
columnFirstRelative = CellReferenceHelper.isColumnRelative(startcell);
rowFirstRelative = CellReferenceHelper.isRowRelative(startcell);
columnLastRelative = CellReferenceHelper.isColumnRelative(endcell);
rowLastRelative = CellReferenceHelper.isRowRelative(endcell);
}
/**
* Initializes the location from the data passed in
*
* @param sheet the sheet containing the cells to be linked to
* @param destcol the column number of the first destination linked cell
* @param destrow the row number of the first destination linked cell
* @param lastdestcol the column number of the last destination linked cell
* @param lastdestrow the row number of the last destination linked cell
*/
private void setLocation(WritableSheet sheet,
int destcol, int destrow,
int lastdestcol, int lastdestrow)
{
StringBuilder sb = new StringBuilder();
sb.Append('\'');
if (sheet.getName().IndexOf('\'') == -1)
{
sb.Append(sheet.getName());
}
else
{
// sb.append(sheet.getName().replaceAll("'", "''"));
// Can't use replaceAll as it is only 1.4 compatible, so have to
// do this the tedious way
string sheetName = sheet.getName();
int pos = 0;
int nextPos = sheetName.IndexOf('\'', pos);
while (nextPos != -1 && pos < sheetName.Length)
{
sb.Append(sheetName.Substring(pos, nextPos));
sb.Append("''");
pos = nextPos + 1;
nextPos = sheetName.IndexOf('\'', pos);
}
sb.Append(sheetName.Substring(pos));
}
sb.Append('\'');
sb.Append('!');
lastdestcol = System.Math.Max(destcol, lastdestcol);
lastdestrow = System.Math.Max(destrow, lastdestrow);
CellReferenceHelper.getCellReference(destcol, destrow, sb);
sb.Append(':');
CellReferenceHelper.getCellReference(lastdestcol, lastdestrow, sb);
location = sb.ToString();
}
/// <summary> Constructor invoked when parsing a string formula
///
/// </summary>
/// <param name="s">the string to parse
/// </param>
internal Area3d(string s, ExternalSheet es)
{
workbook = es;
int seppos = s.IndexOf(":");
Assert.verify(seppos != -1);
// String startcell = s.substring(0, seppos);
string endcell = s.Substring(seppos + 1);
// Get the the start cell details
int sep = s.IndexOf((System.Char) '!');
string cellString = s.Substring(sep + 1, (seppos) - (sep + 1));
columnFirst = CellReferenceHelper.getColumn(cellString);
rowFirst = CellReferenceHelper.getRow(cellString);
// Get the sheet index
string sheetName = s.Substring(0, (sep) - (0));
// Remove single quotes, if they exist
if (sheetName[0] == '\'' && sheetName[sheetName.Length - 1] == '\'')
{
sheetName = sheetName.Substring(1, (sheetName.Length - 1) - (1));
}
sheet = es.getExternalSheetIndex(sheetName);
if (sheet < 0)
{
throw new FormulaException(FormulaException.sheetRefNotFound, sheetName);
}
// Get the last cell index
columnLast = CellReferenceHelper.getColumn(endcell);
rowLast = CellReferenceHelper.getRow(endcell);
columnFirstRelative = true;
rowFirstRelative = true;
columnLastRelative = true;
rowLastRelative = true;
}
public async Task Spreadsheet_StartWorksheet_Freezing(int?columns, int?rows)
{
// Arrange
var worksheetOptions = new WorksheetOptions
{
FrozenColumns = columns,
FrozenRows = rows
};
var expectedColumnName = CellReferenceHelper.GetExcelColumnName((columns ?? 0) + 1);
var expectedCellReference = $"{expectedColumnName}{(rows ?? 0) + 1}";
var expectedActivePane = columns switch
{
not null when rows is not null => PaneValues.BottomRight,
not null => PaneValues.TopRight,
_ => PaneValues.BottomLeft
};
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
// Act
await spreadsheet.StartWorksheetAsync("My sheet", worksheetOptions);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var actual = SpreadsheetDocument.Open(stream, true);
var worksheet = actual.WorkbookPart !.WorksheetParts.Select(x => x.Worksheet).Single();
var sheetView = worksheet.SheetViews !.Cast <SheetView>().Single();
Assert.Equal(PaneStateValues.Frozen, sheetView.Pane !.State !.Value);
Assert.Equal(columns, (int?)sheetView.Pane.HorizontalSplit?.Value);
Assert.Equal(rows, (int?)sheetView.Pane.VerticalSplit?.Value);
Assert.Equal(expectedCellReference, sheetView.Pane.TopLeftCell?.Value);
Assert.Equal(expectedActivePane, sheetView.Pane.ActivePane?.Value);
}
/**
* Returns the cell for the specified location eg. "Sheet1!A4".
* This is identical to using the CellReferenceHelper with its
* associated performance overheads, consequently it should
* be use sparingly
*
* @param loc the cell to retrieve
* @return the cell at the specified location
*/
public override Cell getCell(string loc)
{
Sheet s = getSheet(CellReferenceHelper.getSheet(loc));
return(s.getCell(loc));
}
/**
* Gets the cell reference as a string for this item
*
* @param buf the string buffer to populate
*/
public override void getString(StringBuilder buf)
{
CellReferenceHelper.getCellReference(column, !columnRelative,
row, !rowRelative,
buf);
}
/// <summary> Gets the string representation of this item
///
/// </summary>
/// <param name="">buf
/// </param>
public override void getString(System.Text.StringBuilder buf)
{
CellReferenceHelper.getCellReference(columnFirst, rowFirst, buf);
buf.Append(':');
CellReferenceHelper.getCellReference(columnLast, rowLast, buf);
}
public override void getString(StringBuilder buf)
{
CellReferenceHelper.getCellReference(column, row, buf);
}
/**
* Returns the cell for the specified location eg. "A4", using the
* CellReferenceHelper
*
* @param loc the cell reference
* @return the cell at the specified co-ordinates
*/
public Cell getCell(string loc)
{
return(getCell(CellReferenceHelper.getColumn(loc),
CellReferenceHelper.getRow(loc)));
}
/// <summary> Gets the string version of this cell reference
///
/// </summary>
/// <param name="buf">the buffer to append to
/// </param>
public override void getString(System.Text.StringBuilder buf)
{
CellReferenceHelper.getCellReference(sheet, column, !columnRelative, row, !rowRelative, workbook, buf);
}
/**
* Gets the string representation of this item
*
* @param buf the string buffer
*/
public override void getString(StringBuilder buf)
{
CellReferenceHelper.getColumnReference(getFirstColumn(), buf);
buf.Append(':');
CellReferenceHelper.getColumnReference(getLastColumn(), buf);
}