public static DataTable RemoveColumnsByRow(this DataTable dataTable, int rowIndex, Func<XlsCell, bool> filter) {
if (rowIndex>=dataTable.Rows.Count) {
throw new ArgumentOutOfRangeException(string.Format("行下标超出范围，最大行数为： {0}", dataTable.Rows.Count));
}
DataRow row=dataTable.Rows[rowIndex];
int index=0;
var removeIndexs=new List<int>();
foreach (object cell in row.ItemArray) {
XlsCell value=null;
var xlsCell=cell as XlsCell;
if (xlsCell!=null) {
value=xlsCell;
} else {
value=new XlsCell(cell);
}
if (filter(value)) {
removeIndexs.Add(index);
}
index++;
}
for (int i=removeIndexs.Count-1; i>=0; i--) {
dataTable.Columns.RemoveAt(removeIndexs[i]);
}
return dataTable;
}
private static string ToMd(this DataTable table)
{
table.Shrink();
//table.RemoveColumnsByRow(0, string.IsNullOrEmpty);
var sb = new StringBuilder();
int i = 0;
foreach (DataRow row in table.Rows)
{
//if (row.IsEmpty())
//{
// continue;
//}
sb.Append("|");
foreach (var cell in row.ItemArray)
{
string value = "";
XlsCell xlsCell = cell as XlsCell;
if (xlsCell != null)
{
value = xlsCell.MarkDownText;
}
else
{
value = cell.ToString();
}
sb.Append(value).Append("|");
}
sb.Append("\r\n");
if (i == 0)
{
sb.Append("|");
foreach (DataColumn col in table.Columns)
{
sb.Append(":--|");
}
sb.Append("\r\n");
}
i++;
}
return(sb.ToString());
}
public static bool IsEmpty(this DataRow row)
{
foreach (var cell in row.ItemArray)
{
string value = "";
XlsCell xlsCell = cell as XlsCell;
if (xlsCell != null)
{
value = xlsCell.Value.ToString();
}
else
{
value = cell.ToString();
}
if (value != null && !string.IsNullOrEmpty(value.Trim()))
{
return(false);
}
}
return(true);
}
public static DataTable RemoveColumnsByRow(this DataTable dataTable, int rowIndex, Func <XlsCell, bool> filter)
{
if (rowIndex >= dataTable.Rows.Count)
{
throw new ArgumentOutOfRangeException(string.Format("行下标超出范围，最大行数为： {0}", dataTable.Rows.Count));
}
var row = dataTable.Rows[rowIndex];
int index = 0;
var removeIndexs = new List <int>();
foreach (var cell in row.ItemArray)
{
XlsCell value = null;
XlsCell xlsCell = cell as XlsCell;
if (xlsCell != null)
{
value = xlsCell;
}
else
{
value = new XlsCell(cell);
}
if (filter(value))
{
removeIndexs.Add(index);
}
index++;
}
for (int i = removeIndexs.Count - 1; i >= 0; i--)
{
dataTable.Columns.RemoveAt(removeIndexs[i]);
}
return(dataTable);
}
private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table)
{
// ReadTo HyperLinks Node
if (m_xmlReader == null)
{
//Console.WriteLine("m_xmlReader is null");
return(false);
}
//Console.WriteLine(m_xmlReader.Depth.ToString());
m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
if (m_xmlReader.IsEmptyElement)
{
//Console.WriteLine("not find hyperlink");
return(false);
}
// Read Realtionship Table
//Console.WriteLine("sheetrel:{0}", sheet.Path);
var sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path);
var hyperDict = new Dictionary <string, string>();
if (sheetRelStream != null)
{
using (var reader = XmlReader.Create(sheetRelStream))
{
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel)
{
string rid = reader.GetAttribute(XlsxWorkbook.A_id);
hyperDict[rid] = reader.GetAttribute(XlsxWorkbook.A_target);
}
}
sheetRelStream.Close();
}
}
// Read All HyperLink Node
while (m_xmlReader.Read())
{
if (m_xmlReader.NodeType != XmlNodeType.Element)
{
break;
}
if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink)
{
break;
}
string aref = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref);
string display = m_xmlReader.GetAttribute(XlsxWorksheet.A_display);
string rid = m_xmlReader.GetAttribute(XlsxWorksheet.A_rid);
////Console.WriteLine("{0}:{1}", aref.Substring(1), display);
//Console.WriteLine("hyperlink:{0}",hyperDict[rid]);
var hyperlink = display;
if (hyperDict.ContainsKey(rid))
{
hyperlink = hyperDict[rid];
}
int col = -1;
int row = -1;
XlsxDimension.XlsxDim(aref, out col, out row);
//Console.WriteLine("{0}:[{1},{2}]",aref, row, col);
if (col >= 1 && row >= 1)
{
row = row - 1;
col = col - 1;
if (row == 0 && m_isFirstRowAsColumnNames)
{
// TODO(fanfeilong):
var value = table.Columns[col].ColumnName;
XlsCell cell = new XlsCell(value);
cell.SetHyperLink(hyperlink);
table.Columns[col].DefaultValue = cell;
}
else
{
var value = table.Rows[row][col];
var cell = new XlsCell(value);
cell.SetHyperLink(hyperlink);
//Console.WriteLine(cell.MarkDownText);
table.Rows[row][col] = cell;
}
}
}
// Close
m_xmlReader.Close();
if (m_sheetStream != null)
{
m_sheetStream.Close();
}
return(true);
}
private void pushCellValue(XlsBiffBlankCell cell)
{
double _dValue;
//LogManager.Log(this).Debug("pushCellValue {0}", cell.ID);
switch (cell.ID)
{
case BIFFRECORDTYPE.BOOLERR:
if (cell.ReadByte(7) == 0)
m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(6) != 0);
break;
case BIFFRECORDTYPE.BOOLERR_OLD:
if (cell.ReadByte(8) == 0)
m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(7) != 0);
break;
case BIFFRECORDTYPE.INTEGER:
case BIFFRECORDTYPE.INTEGER_OLD:
m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffIntegerCell)cell).Value);
break;
case BIFFRECORDTYPE.NUMBER:
case BIFFRECORDTYPE.NUMBER_OLD:
_dValue = ((XlsBiffNumberCell)cell).Value;
m_cellsValues[cell.ColumnIndex] =
new XlsCell(!ConvertOaDate ?_dValue : tryConvertOADateTime(_dValue, cell.XFormat));
//LogManager.Log(this).Debug("VALUE: {0}", _dValue);
break;
case BIFFRECORDTYPE.LABEL:
case BIFFRECORDTYPE.LABEL_OLD:
case BIFFRECORDTYPE.RSTRING:
m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffLabelCell)cell).Value);
//LogManager.Log(this).Debug("VALUE: {0}", m_cellsValues[cell.ColumnIndex]);
break;
case BIFFRECORDTYPE.LABELSST:
string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex);
//LogManager.Log(this).Debug("VALUE: {0}", tmp);
////Console.WriteLine("SST Index:{0}", ((XlsBiffLabelSSTCell)cell).SSTIndex);
m_cellsValues[cell.ColumnIndex] = new XlsCell(tmp);
break;
case BIFFRECORDTYPE.RK:
_dValue = ((XlsBiffRKCell)cell).Value;
m_cellsValues[cell.ColumnIndex] = new XlsCell(!ConvertOaDate ?
_dValue : tryConvertOADateTime(_dValue, cell.XFormat));
//LogManager.Log(this).Debug("VALUE: {0}", _dValue);
break;
case BIFFRECORDTYPE.MULRK:
XlsBiffMulRKCell _rkCell = (XlsBiffMulRKCell)cell;
for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++)
{
_dValue = _rkCell.GetValue(j);
//LogManager.Log(this).Debug("VALUE[{1}]: {0}", _dValue, j);
m_cellsValues[j] = new XlsCell(!ConvertOaDate ? _dValue : tryConvertOADateTime(_dValue, _rkCell.GetXF(j)));
}
break;
case BIFFRECORDTYPE.BLANK:
case BIFFRECORDTYPE.BLANK_OLD:
case BIFFRECORDTYPE.MULBLANK:
// Skip blank cells
break;
case BIFFRECORDTYPE.FORMULA:
case BIFFRECORDTYPE.FORMULA_OLD:
object _oValue = ((XlsBiffFormulaCell)cell).Value;
if (null != _oValue && _oValue is FORMULAERROR)
{
_oValue = null;
}
else
{
m_cellsValues[cell.ColumnIndex] = new XlsCell(!ConvertOaDate ?
_oValue : tryConvertOADateTime(_oValue, (ushort)(cell.XFormat)));//date time offset
}
break;
default:
//Console.WriteLine("CellId:",cell.ID);
break;
}
////Console.WriteLine("CellId:{0},Value:{1}", cell.ID, m_cellsValues[cell.ColumnIndex]);
var hyperLink = m_globals.GetHyperLink(cell.RowIndex, cell.ColumnIndex);
if (hyperLink != null)
{
m_cellsValues[cell.ColumnIndex].SetHyperLink(hyperLink.Url);
}
}
private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table) {
// ReadTo HyperLinks Node
if (m_xmlReader == null) {
return false;
}
m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
if (m_xmlReader.IsEmptyElement) {
return false;
}
// Read Realtionship Table
Stream sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path);
var hyperDict = new Dictionary<string, string>();
if (sheetRelStream != null) {
using (XmlReader reader = XmlReader.Create(sheetRelStream)) {
while (reader.Read()) {
if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel) {
string rid = reader.GetAttribute(XlsxWorkbook.A_id);
Debug.Assert(rid!=null);
hyperDict[rid] = reader.GetAttribute(XlsxWorkbook.A_target);
}
}
sheetRelStream.Close();
}
}
// Read All HyperLink Node
while (m_xmlReader.Read()) {
if (m_xmlReader.NodeType != XmlNodeType.Element)
break;
if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink)
break;
string aref = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref);
string display = m_xmlReader.GetAttribute(XlsxWorksheet.A_display);
string rid = m_xmlReader.GetAttribute(XlsxWorksheet.A_rid);
string hyperlink = display;
Debug.Assert(rid!=null);
if (hyperDict.ContainsKey(rid)) {
hyperlink = hyperDict[rid];
}
int col = -1;
int row = -1;
XlsxDimension.XlsxDim(aref, out col, out row);
if (col >= 1 && row >= 1) {
row = row - 1;
col = col - 1;
if (row == 0 && m_isFirstRowAsColumnNames) {
// TODO(fanfeilong):
string value = table.Columns[col].ColumnName;
var cell = new XlsCell(value);
cell.SetHyperLink(hyperlink);
table.Columns[col].DefaultValue = cell;
} else {
object value = table.Rows[row][col];
var cell = new XlsCell(value);
cell.SetHyperLink(hyperlink);
table.Rows[row][col] = cell;
}
}
}
// Close
m_xmlReader.Close();
if (m_sheetStream != null) {
m_sheetStream.Close();
}
return true;
}
/// <summary>
/// Add a row to the input DataTable for each row match in the input MatchCollection
/// </summary>
/// <param name="rowMatches">A collection of all the rows to add to the DataTable</param>
/// <param name="dataTable">The DataTable to which we add rows</param>
private static void ParseRows(IEnumerable rowMatches, DataTable dataTable) {
foreach (Match rowMatch in rowMatches) {
// if the row contains header tags don't use it - it is a header not a row
if (!rowMatch.Value.Contains("<th")) {
DataRow dataRow = dataTable.NewRow();
var rowArray = new List<XlsCell>();
MatchCollection cellMatches = Regex.Matches(
rowMatch.Value,
CellPattern,
ExpressionOptions);
for (int columnIndex = 0; columnIndex < cellMatches.Count; columnIndex++) {
var cellValue = cellMatches[columnIndex].Groups[1].ToString();
cellValue = cellValue.RemoveSpan();
var hyperLinkMach = Regex.Match(cellValue, HyperLinkPattern);
if (hyperLinkMach.Captures.Count >0) {
var xlsCell = new XlsCell(hyperLinkMach.Groups[2].Value);
xlsCell.SetHyperLink(hyperLinkMach.Groups[1].Value);
rowArray.Add(xlsCell);
Console.WriteLine("have hyperlink");
} else {
rowArray.Add(new XlsCell(cellValue));
}
}
XlsCell[] cells = rowArray.ToArray();
dataRow.ItemArray = cells;
dataTable.Rows.Add(dataRow);
}
}
}
private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table)
{
// ReadTo HyperLinks Node
if (m_xmlReader == null)
{
//Console.WriteLine("m_xmlReader is null");
return false;
}
//Console.WriteLine(m_xmlReader.Depth.ToString());
m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
if (m_xmlReader.IsEmptyElement)
{
//Console.WriteLine("not find hyperlink");
return false;
}
// Read All HyperLink Node
while (m_xmlReader.Read())
{
if (m_xmlReader.NodeType != XmlNodeType.Element) break;
if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink) break;
string aref = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref);
string display = m_xmlReader.GetAttribute(XlsxWorksheet.A_display);
////Console.WriteLine("{0}:{1}", aref.Substring(1), display);
int col = -1;
int row = -1;
XlsxDimension.XlsxDim(aref, out col,out row);
//Console.WriteLine("{0}:[{1},{2}]",aref, row, col);
if (col >=1 && row >=1)
{
row = row - 1;
col = col - 1;
if (row==0 &&m_isFirstRowAsColumnNames)
{
// TODO(fanfeilong):
var value = table.Columns[col].ColumnName;
XlsCell cell = new XlsCell(value);
cell.SetHyperLink(display);
table.Columns[col].DefaultValue = cell;
}
else
{
var value = table.Rows[row][col];
var cell = new XlsCell(value);
cell.SetHyperLink(display);
//Console.WriteLine(cell.MarkDownText);
table.Rows[row][col] = cell;
}
}
}
// Close
m_xmlReader.Close();
if (m_sheetStream != null)
{
m_sheetStream.Close();
}
return true;
}
private void pushCellValue(XlsBiffBlankCell cell)
{
double _dValue;
//LogManager.Log(this).Debug("pushCellValue {0}", cell.ID);
switch (cell.ID)
{
case BIFFRECORDTYPE.BOOLERR:
if (cell.ReadByte(7) == 0)
{
m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(6) != 0);
}
break;
case BIFFRECORDTYPE.BOOLERR_OLD:
if (cell.ReadByte(8) == 0)
{
m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(7) != 0);
}
break;
case BIFFRECORDTYPE.INTEGER:
case BIFFRECORDTYPE.INTEGER_OLD:
m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffIntegerCell)cell).Value);
break;
case BIFFRECORDTYPE.NUMBER:
case BIFFRECORDTYPE.NUMBER_OLD:
_dValue = ((XlsBiffNumberCell)cell).Value;
m_cellsValues[cell.ColumnIndex] =
new XlsCell(!ConvertOaDate ?_dValue : tryConvertOADateTime(_dValue, cell.XFormat));
//LogManager.Log(this).Debug("VALUE: {0}", _dValue);
break;
case BIFFRECORDTYPE.LABEL:
case BIFFRECORDTYPE.LABEL_OLD:
case BIFFRECORDTYPE.RSTRING:
m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffLabelCell)cell).Value);
//LogManager.Log(this).Debug("VALUE: {0}", m_cellsValues[cell.ColumnIndex]);
break;
case BIFFRECORDTYPE.LABELSST:
string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex);
//LogManager.Log(this).Debug("VALUE: {0}", tmp);
////Console.WriteLine("SST Index:{0}", ((XlsBiffLabelSSTCell)cell).SSTIndex);
m_cellsValues[cell.ColumnIndex] = new XlsCell(tmp);
break;
case BIFFRECORDTYPE.RK:
_dValue = ((XlsBiffRKCell)cell).Value;
m_cellsValues[cell.ColumnIndex] = new XlsCell(!ConvertOaDate ?
_dValue : tryConvertOADateTime(_dValue, cell.XFormat));
//LogManager.Log(this).Debug("VALUE: {0}", _dValue);
break;
case BIFFRECORDTYPE.MULRK:
XlsBiffMulRKCell _rkCell = (XlsBiffMulRKCell)cell;
for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++)
{
_dValue = _rkCell.GetValue(j);
//LogManager.Log(this).Debug("VALUE[{1}]: {0}", _dValue, j);
m_cellsValues[j] = new XlsCell(!ConvertOaDate ? _dValue : tryConvertOADateTime(_dValue, _rkCell.GetXF(j)));
}
break;
case BIFFRECORDTYPE.BLANK:
case BIFFRECORDTYPE.BLANK_OLD:
case BIFFRECORDTYPE.MULBLANK:
// Skip blank cells
break;
case BIFFRECORDTYPE.FORMULA:
case BIFFRECORDTYPE.FORMULA_OLD:
object _oValue = ((XlsBiffFormulaCell)cell).Value;
if (null != _oValue && _oValue is FORMULAERROR)
{
_oValue = null;
}
else
{
m_cellsValues[cell.ColumnIndex] = new XlsCell(!ConvertOaDate ?
_oValue : tryConvertOADateTime(_oValue, (ushort)(cell.XFormat)));//date time offset
}
break;
default:
//Console.WriteLine("CellId:",cell.ID);
break;
}
////Console.WriteLine("CellId:{0},Value:{1}", cell.ID, m_cellsValues[cell.ColumnIndex]);
var hyperLink = m_globals.GetHyperLink(cell.RowIndex, cell.ColumnIndex);
if (hyperLink != null)
{
m_cellsValues[cell.ColumnIndex].SetHyperLink(hyperLink.Url);
}
}
private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table)
{
// ReadTo HyperLinks Node
if (m_xmlReader == null)
{
//Console.WriteLine("m_xmlReader is null");
return false;
}
//Console.WriteLine(m_xmlReader.Depth.ToString());
m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
if (m_xmlReader.IsEmptyElement)
{
//Console.WriteLine("not find hyperlink");
return false;
}
// Read Realtionship Table
//Console.WriteLine("sheetrel:{0}", sheet.Path);
var sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path);
var hyperDict = new Dictionary<string, string>();
if (sheetRelStream != null)
{
using (var reader = XmlReader.Create(sheetRelStream))
{
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel)
{
string rid = reader.GetAttribute(XlsxWorkbook.A_id);
hyperDict[rid] = reader.GetAttribute(XlsxWorkbook.A_target);
}
}
sheetRelStream.Close();
}
}
// Read All HyperLink Node
while (m_xmlReader.Read())
{
if (m_xmlReader.NodeType != XmlNodeType.Element) break;
if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink) break;
string aref = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref);
string display = m_xmlReader.GetAttribute(XlsxWorksheet.A_display);
string rid = m_xmlReader.GetAttribute(XlsxWorksheet.A_rid);
////Console.WriteLine("{0}:{1}", aref.Substring(1), display);
//Console.WriteLine("hyperlink:{0}",hyperDict[rid]);
var hyperlink = display;
if (hyperDict.ContainsKey(rid))
{
hyperlink = hyperDict[rid];
}
int col = -1;
int row = -1;
XlsxDimension.XlsxDim(aref, out col,out row);
//Console.WriteLine("{0}:[{1},{2}]",aref, row, col);
if (col >=1 && row >=1)
{
row = row - 1;
col = col - 1;
if (row==0 &&m_isFirstRowAsColumnNames)
{
// TODO(fanfeilong):
var value = table.Columns[col].ColumnName;
XlsCell cell = new XlsCell(value);
cell.SetHyperLink(hyperlink);
table.Columns[col].DefaultValue = cell;
}
else
{
var value = table.Rows[row][col];
var cell = new XlsCell(value);
cell.SetHyperLink(hyperlink);
//Console.WriteLine(cell.MarkDownText);
table.Rows[row][col] = cell;
}
}
}
// Close
m_xmlReader.Close();
if (m_sheetStream != null)
{
m_sheetStream.Close();
}
return true;
}