protected void Button1_Click1(object sender, EventArgs e)
{
using (FileStream file = new FileStream(@"E:/Technovil/Managers.xlsx", FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(file);
}
sh = (XSSFSheet)wb.GetSheet("Sheet1");
dataGridView1.DataSource = sh.GetTables();
dataGridView1.DataBind();
}
public void Test56170()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56170.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
IWorkbook wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
ICell cell;
// add some contents to table so that the table will need expansion
IRow row = sheet.GetRow(0);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(0);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo1");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(1);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo2");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(2);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo3");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
row = sheet.GetRow(1);
cell = row.CreateCell(0);
cell.SetCellValue("demo1");
cell = row.CreateCell(1);
cell.SetCellValue("demo2");
cell = row.CreateCell(2);
cell.SetCellValue("demo3");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
// expand table
XSSFTable table = sheet.GetTables()[0];
CellReference startRef = table.GetStartCellReference();
CellReference endRef = table.GetEndCellReference();
table.GetCTTable().@ref = (new CellRangeAddress(startRef.Row, 1, startRef.Col, endRef.Col).FormatAsString());
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbRead);
/*FileOutputStream stream = new FileOutputStream("c:\\temp\\output.xlsx");
* workbook.Write(stream);
* stream.Close();*/
}
/**
* Find the 2D base data area for the pivot table, either from its direct reference or named table/range.
* @return AreaReference representing the current area defined by the pivot table
* @ if the ref1 attribute is not contiguous or the name attribute is not found.
*/
public AreaReference GetPivotArea(IWorkbook wb)
{
CT_WorksheetSource wsSource = ctPivotCacheDefinition.cacheSource.worksheetSource;
String ref1 = wsSource.@ref;
String name = wsSource.name;
if (ref1 == null && name == null)
{
throw new ArgumentException("Pivot cache must reference an area, named range, or table.");
}
// this is the XML format, so tell the reference that.
if (ref1 != null)
{
return(new AreaReference(ref1, SpreadsheetVersion.EXCEL2007));
}
if (name != null)
{
// named range or table?
IName range = wb.GetName(name);
if (range != null)
{
return(new AreaReference(range.RefersToFormula, SpreadsheetVersion.EXCEL2007));
}
// not a named range, check for a table.
// do this second, as tables are sheet-specific, but named ranges are not, and may not have a sheet name given.
XSSFSheet sheet = (XSSFSheet)wb.GetSheet(wsSource.sheet);
foreach (XSSFTable table in sheet.GetTables())
{
if (table.Name.Equals(name))
{ //case-sensitive?
return(new AreaReference(table.StartCellReference, table.EndCellReference));
}
}
}
throw new ArgumentException("Name '" + name + "' was not found.");
}
public static void WriteDMToTable(this ISheet sheet, List <DefineSelectItem> lst, string tableName, int numCol)
{
if (lst == null || lst.Count == 0)
{
return;
}
XSSFSheet s = (XSSFSheet)sheet;
XSSFTable tbl = s.GetTables().Where(a => a.Name == tableName).FirstOrDefault();
if (tbl == null)
{
return;
}
CT_Table ctTBl = tbl.GetCTTable();
if (numCol < 2 || ctTBl.tableColumns.count < numCol)
{
return;
}
CellReference cellRefStart = tbl.GetStartCellReference();
int rowStart = cellRefStart.Row + 1;
short colStart = cellRefStart.Col;
CellReference cellRefEnd = tbl.GetEndCellReference();
AreaReference reference = new AreaReference(cellRefStart, new CellReference(cellRefStart.Row + lst.Count, cellRefEnd.Col));
ctTBl.insertRow = true;
ctTBl.insertRowShift = true;
ctTBl.@ref = reference.FormatAsString();
IRow row = GetCreateRow(sheet, rowStart);
switch (numCol)
{
case 3:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Value2);
row.GetCreateCell(colStart + 2).SetCellValue(item.Text);
}
break;
case 4:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Value2);
row.GetCreateCell(colStart + 2).SetCellValue(item.Value3);
row.GetCreateCell(colStart + 3).SetCellValue(item.Text);
}
break;
case 2:
default:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Text);
}
break;
}
}