public IEnumerable <Row> GetRows(string sheetName, uint startRowNo, uint endRowNo)
{
IEnumerable <Row> res = null;
if (!string.IsNullOrEmpty(sheetName))
{
SheetData sheetdata = GetSheetData(sheetName);
res = IncOpenExcel.GetRows(sheetdata, startRowNo, endRowNo);
}
return(res);
}
public bool CreateOrUpdateRowAt(string sheetName, DataRow dataRow, uint rowNo, uint columnNo, Dictionary <uint, uint> eachColumnStyle = null)
{
bool res = false;
SheetData sheetData = GetSheetData(sheetName);
if (sheetData != null)
{
res = IncOpenExcel.CreateOrUpdateRowAt(sheetData, dataRow, rowNo, columnNo, defaultCellStyle, eachColumnStyle);
}
return(res);
}
public List <string> GetRowsXml(string sheetName, uint startRowNo, uint endRowNo)
{
List <string> res = null;
if (!string.IsNullOrEmpty(sheetName))
{
var sheetdata = GetSheetData(sheetName);
res = IncOpenExcel.GetRowsXml(sheetdata, startRowNo, endRowNo);
}
return(res);
}
public bool DeleteRows(string sheetName, uint startRowNo, uint endRowNo)
{
bool res = false;
var rows = GetRows(sheetName, startRowNo, endRowNo);
if (rows != null)
{
IncOpenExcel.DeleteRows(rows);
}
res = true;
return(res);
}
//0:normal 1. 4lines 2. balck 3black
public static Dictionary <uint, uint> getRowStyles(DataColumnCollection dataColumn, uint startColumnNo, int cateogry, DefaultCellStyle defaultCellStyle)
{
Dictionary <uint, uint> styles = new Dictionary <uint, uint>();
if (dataColumn != null)
{
for (int i = 0; i < dataColumn.Count; i++)
{
uint defaultStyle = IncOpenExcel.getDefaultStyle(dataColumn[i].DataType, defaultCellStyle, cateogry);
styles.Add((uint)i + startColumnNo, defaultStyle);
}
}
return(styles);
}
private static bool SetOrUpdateCellValue(SheetData sheetData, uint rowNumber, uint columnNumber, Type datatype, object value, DefaultCellStyle defaultCellStyle, uint customStyle = 0)
{
if (sheetData != null && value != null && datatype != null)
{
//创建cell.
//0.不存在row,建立row,插入cell.
//0.row存在1.删除cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在row后面.
string cellRefrence = GetCellReference(rowNumber, columnNumber);
IEnumerable <Row> equalOrbiggerRows = sheetData.Elements <Row>().Where(x => x.RowIndex >= rowNumber);
Row equalRow = null, biggerRow = null;
if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex == rowNumber)
{
equalRow = equalOrbiggerRows.First();
}
else if (equalOrbiggerRows != null && equalOrbiggerRows.Count() > 0 && equalOrbiggerRows.First().RowIndex > rowNumber)
{
biggerRow = equalOrbiggerRows.First();
}
if (equalRow != null)//存在row. 1.是否存在cell,存在跟新,不存在建立新cell.2.是否有比这个cell更大的cell,有插入大cell之前.否则直接附加在Row后面.
{
IEnumerable <Cell> equalOrbiggerCells = equalRow.Elements <Cell>().Where(x => x.CellReference >= new StringValue(cellRefrence));
Cell equalCell = null;
Cell biggerCell = null;
if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference == cellRefrence)
{
equalCell = equalOrbiggerCells.First();
}
else if (equalOrbiggerCells != null && equalOrbiggerCells.Count() > 0 && equalOrbiggerCells.First().CellReference > new StringValue(cellRefrence))
{
biggerCell = equalOrbiggerCells.First();
}
Cell newCell = createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle);
if (equalCell != null)
{
equalOrbiggerRows.First().ReplaceChild(newCell, equalCell);
}
else
{
if (biggerCell != null)
{
equalOrbiggerRows.First().InsertBefore(newCell, biggerCell);
}
else
{
equalOrbiggerRows.First().Append(newCell);
}
}
}
else//不存在.新建row and cell.
{
Row newrow = new Row();
newrow.RowIndex = rowNumber;
Cell theCell = IncOpenExcel.createCell(cellRefrence, datatype, value, defaultCellStyle, customStyle);
if (theCell != null)
{
newrow.Append(theCell);
}
if (biggerRow != null)
{
sheetData.InsertBefore(newrow, equalOrbiggerRows.First());//插入的行不是最大的,插到比它大的前面.
}
else
{
sheetData.Append(newrow);; //插入的行是最大的,直接附加到最后
}
}
}
return(true);
}