public string Read(string inFile)
{
var stream = File.OpenRead(inFile);
var book = new XSSFWorkbook(stream);
stream.Close();
var sb = new StringBuilder();
var sheet = book.GetSheetAt(0);
int lastRowNum = sheet.LastRowNum;
for (int r = 0; r <= lastRowNum; r++)
{
var datarow = sheet.GetRow(r);
{
foreach (var cell in datarow.Cells)
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append(cell.NumericCellValue.ToString() + "\t");
break;
case CellType.String:
sb.Append(cell.StringCellValue.Replace("\n", "") + "\t");
break;
default:
throw new Exception("?");
}
}
sb.Append("\r\n");
}
}
return sb.ToString();
}
/// <summary>
/// Gets the field names from a workbook and stores them in a list.
/// </summary>
/// <param name="fieldNames">list where field names will be stored</param>
/// <param name="workbook">workbook from which field names will be extracted</param>
public static void GetFieldNames(ImportData data, XSSFWorkbook workbook)
{
ISheet sheet = workbook.GetSheetAt(0);
bool reading = true;
int rindex = sheet.FirstRowNum;
while (reading) {
IRow row = sheet.GetRow(rindex);
if (row != null) {
ICell cell = row.GetCell(0);
if (cell != null) {
string s = CellValueAsString(cell);
if (s != "" && s[0] == '[') {
for (int i = 0; i < row.LastCellNum; i++) {
s = CellValueAsString(row.GetCell(i)).TrimEnd(']').TrimStart('['); ;
data.fieldNames.Add(s);
}
// don't read more than one row of field names
reading = false;
}
}
}
rindex++;
if (rindex > sheet.LastRowNum) {
reading = false;
}
}
}
public static bool UpdateExcelData(string excelFilePath, string excelName)
{
XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open));
var sheet = excelBook.GetSheetAt(0);
var headerRow = sheet.GetRow(0);
//total columns
int cellCount = headerRow.LastCellNum;
//total rows
int rowCount = sheet.LastRowNum;
switch (excelName)
{
case Constant.Water:
UpdateWaterData(sheet, cellCount, rowCount);
break;
case Constant.Air:
UpdateAirData(sheet, cellCount, rowCount);
break;
}
try
{
FileStream writefile = new FileStream("d:\\" + excelName + ".xlsx", FileMode.Create, FileAccess.Write);
excelBook.Write(writefile);
writefile.Close();
return true;
}
catch
{
return false;
}
}
public ActionResult ExportExcel()
{
var data = this.rptRepository.All();
string path = Server.MapPath("~/Templates/客戶資訊.xlsx");
FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);
IWorkbook wb = new XSSFWorkbook(stream);
stream.Close();
ISheet sheet = wb.GetSheetAt(0);
ICellStyle cs = sheet.GetRow(1).Cells[0].CellStyle;
int Index = 1;
foreach (var item in data)
{
IRow row = sheet.CreateRow(Index);
CreateCell(item.客戶名稱, row, 0, cs);
CreateCell(item.聯絡人數量.ToString(), row, 1, cs);
CreateCell(item.客戶帳戶數量.ToString(), row, 2, cs);
Index++;
}
string serverPath = @"D:/repot.xlsx";
using (FileStream file = new FileStream(serverPath, FileMode.Create))
{
wb.Write(file);
file.Close();
}
return File(serverPath, "application/excel","Report.xlsx");
}
/// <summary>
/// use me for 2003 and 2007
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public DataTable ReadExcelAsTable(string fileName)
{
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
//chk xls
SS.UserModel.ISheet sheet;
if (fileName.EndsWith("xlsx"))
{
XSSF.UserModel.XSSFWorkbook wb = new XSSF.UserModel.XSSFWorkbook(fs); //for 2007
sheet = wb.GetSheetAt(0); //read first sheet
}
else
{
SS.UserModel.IWorkbook wb = new HSSF.UserModel.HSSFWorkbook(fs); //for 2003
sheet = wb.GetSheetAt(0); //read first sheet
}
return(ReadSheet(sheet));
}
}
catch (Exception ex)
{
throw ex;
}
}
public Dictionary<DateTime, List<SaleParsed>> ImportFromSheet(string path, DateTime dateTime, IEnumerable<int> sheetNumbers, IEnumerable<GoodType> goodTypes, string producer)
{
this.goodTypes = goodTypes;
this.producer = producer;
var result = new Dictionary<DateTime, List<SaleParsed>>();
using (var fs = File.OpenRead(path))
{
var workbook = new XSSFWorkbook(fs);
foreach (var sNum in sheetNumbers)
{
ISheet sheet = workbook.GetSheetAt(sNum);
var sales = ParseSales(sheet, dateTime);
foreach (var s in sales)
{
if (result.ContainsKey(s.Key))
{
result[s.Key].AddRange(s.Value);
}
else
{
result.Add(s.Key, s.Value);
}
}
}
}
return result;
}
public void TestBug48936()
{
IWorkbook w = new XSSFWorkbook();
ISheet s = w.CreateSheet();
int i = 0;
List<String> lst = ReadStrings("48936-strings.txt");
foreach (String str in lst)
{
s.CreateRow(i++).CreateCell(0).SetCellValue(str);
}
try
{
w = XSSFTestDataSamples.WriteOutAndReadBack(w);
}
catch (POIXMLException)
{
Assert.Fail("Detected Bug #48936");
}
s = w.GetSheetAt(0);
i = 0;
foreach (String str in lst)
{
String val = s.GetRow(i++).GetCell(0).StringCellValue;
Assert.AreEqual(str, val);
}
}
public static int[][][] ReadExcelPattern(string filepath)
{
using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(fs);
int sheetnumber = workbook.NumberOfSheets;
int [][][] sheetelements = new int[sheetnumber][][];
for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
ISheet _isheet = workbook.GetSheetAt(sheetindex);
int lastrownum = _isheet.LastRowNum;
int [][] rowelements = new int[lastrownum+1][];
for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
IRow row = _isheet.GetRow(rowindex);
if(row == null)continue;
int lastcellnum = row.LastCellNum;
int[] cellelements = new int[lastcellnum+1];
for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
ICell cell = row.GetCell(cellindex);
if(cell != null){
cellelements[cellindex] = Convert.ToInt32(cell.ToString());
Debug.Log(cellelements[cellindex]);
}
}
rowelements[rowindex] = cellelements;
}
sheetelements[sheetindex] = rowelements;
}
return sheetelements;
}
}
public void TestLoadStyles()
{
XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("styles.xlsx"));
ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell((short)0);
ICellStyle style = cell.CellStyle;
// assertNotNull(style);
}
/// <summary>
/// Get the parsable rows from a workbook
/// </summary>
/// <param name="rows">a reference to an object that will contain parsable rows</param>
/// <param name="workbook">the workbook to extract rows from</param>
public static void GetParsableRows(ref List<ParsableRow> rows, XSSFWorkbook workbook) {
ISheet sheet = workbook.GetSheetAt(0);
bool reading = true;
int rindex = sheet.FirstRowNum;
while (reading) {
IRow row = sheet.GetRow(rindex);
// if there is a row, and it's not empty
if (row != null && row.Cells.Count > 0) {
// check to see if the row is parsable by looking at the first value
ICell firstCell = row.GetCell(0);
if (firstCell != null) {
string firstCellVal = CellValueAsString(firstCell);
// treat the row as parsable if it is:
// - not a title
// - not metadata
// - not a variable
if (firstCellVal[0] != '[' && firstCellVal[0] != '#' && firstCellVal[0] != '$') {
// cells are stored in parsable row objects
ParsableRow pr = new ParsableRow();
// copy the line number to the parsable row object
pr.linenumber = rindex;
// make an array which is as long as the last cell number - note that NPOI will show cells **up to** the last cell that
// contains data, so if a row in the sheet only has a single value in the fourth column, that row will return null for all
// indices up to 2, then a value for index 3.
pr.cells = new string[row.LastCellNum];
// for each cell in the row, convert it to a string and copy it to the parsable row object
for (int i = 0; i < row.LastCellNum; i++) {
pr.cells[i] = CellValueAsString(row.GetCell(i));
}
// add the parsable row object we just set up to the list of rows that was passed to the method
rows.Add(pr);
}
}
}
rindex++;
// check to make sure we're not out of bounds
if (rindex > sheet.LastRowNum) {
reading = false;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
// LoadTemplate();
//FileStream file = new FileStream(@"template/Astro2_Sys WHCK Status - 0712-SA2.xlsx", FileMode.Open, FileAccess.ReadWrite);
FileStream file = new FileStream(@"template/Astro2_Sys WHCK Status - 0712-SA2.xlsx", FileMode.Open, FileAccess.ReadWrite);
IWorkbook wb = new XSSFWorkbook(file);
MessageBox.Show(wb.NumberOfSheets+"", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
if (wb.NumberOfSheets >= 2)
{
ISheet sheet1 = wb.GetSheetAt(1);
sheet1.GetRow(7).GetCell(6).SetCellValue("Failed");
//Write the stream data of workbook to the root directory
//FileStream sw = new FileStream(@"test.xls", FileMode.Create);
FileStream sw = new FileStream(@"test.xls", FileMode.OpenOrCreate);
wb.Write(file);
sw.Close();
}
//create cell on rows, since rows do already exist,it's not necessary to create rows again.
// sheet1.GetRow(8).GetCell(7).SetCellValue("Failed");
/*
sheet1.GetRow(2).GetCell(1).SetCellValue(300);
sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
sheet1.GetRow(5).GetCell(1).SetCellValue(110);
sheet1.GetRow(6).GetCell(1).SetCellValue(100);
sheet1.GetRow(7).GetCell(1).SetCellValue(200);
sheet1.GetRow(8).GetCell(1).SetCellValue(210);
sheet1.GetRow(9).GetCell(1).SetCellValue(2300);
sheet1.GetRow(10).GetCell(1).SetCellValue(240);
sheet1.GetRow(11).GetCell(1).SetCellValue(180123);
sheet1.GetRow(12).GetCell(1).SetCellValue(150);
* */
//Force excel to recalculate all the formula while open
//sheet1.ForceFormulaRecalculation = true;
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
//Response.BinaryWrite(ms.ToArray());
// WriteToFile();
// wb = null;
//ms.Close();
//ms.Dispose();
}
public static void CreateLoadScriptableObject()
{
string filepath = AssetDatabase.GetAssetPath(Selection.activeObject);
string createpath = AssetDatabase.GenerateUniqueAssetPath("Assets/PartA/Resources/Pattern/" + Selection.activeObject.name +".asset");
ExcelData exceldata = ScriptableObject.CreateInstance<ExcelData>();
//List<_Row> _sheet_list = new List<_Row>();
using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(fs);
int sheetnumber = workbook.NumberOfSheets;
int [][][] sheetelements = new int[sheetnumber][][];
exceldata.data = new int[sheetnumber][][];
for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
ISheet _isheet = workbook.GetSheetAt(sheetindex);
int lastrownum = _isheet.LastRowNum;
int [][] rowelements = new int[lastrownum + 1][];
_Row localrow = new _Row();
for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
IRow row = _isheet.GetRow(rowindex);
if(row == null)continue;
int lastcellnum = row.LastCellNum;
int[] cellelements = new int[lastcellnum + 1];
_Cell localcell = new _Cell();
for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
ICell cell = row.GetCell(cellindex);
if(cell != null){
//cellelements[cellindex] = Convert.ToInt32(cell.ToString());
localcell.cell[cellindex] = Convert.ToInt32(cell.ToString());
}
}
localrow.row.Add(localcell);
rowelements[rowindex] = cellelements;
}
exceldata.sheet.Add(localrow);
sheetelements[sheetindex] = rowelements;
}
//exceldata.data.CopyTo(sheetelements,0);
//exceldata.data = (int[][][])sheetelements.Clone();
foreach(_Row cells in exceldata.sheet){
foreach(_Cell test in cells.row){
for(int i = 0;i < 5;i++)
Debug.Log(test.cell[i]);
}
}
//exceldata.data = sheetelements;
exceldata.check = "Exist";
exceldata.hideFlags = HideFlags.NotEditable;
EditorUtility.SetDirty(exceldata);
AssetDatabase.CreateAsset(exceldata,createpath);
AssetDatabase.SaveAssets();
EditorUtility.FocusProjectWindow();
}
}
public void TestLoadSample()
{
XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("sample.xlsx"));
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.AreEqual("Sheet1", workbook.GetSheetName(0));
ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell((short)1);
Assert.IsNotNull(cell);
Assert.AreEqual(111.0, cell.NumericCellValue, 0.0);
cell = row.GetCell((short)0);
Assert.AreEqual("Lorem", cell.RichStringCellValue.String);
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls = {
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address=(s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink=(link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri&&rel.TargetUri.IsFile)
Assert.AreEqual(urls[i].Replace("file:///","").Replace("/","\\"),rel.TargetUri.LocalPath);
else
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
else
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
public void SetUp()
{
if (workbook == null)
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(SS.FILENAME);
OPCPackage pkg = OPCPackage.Open(is1);
workbook = new XSSFWorkbook(pkg);
sheet = workbook.GetSheetAt(0);
}
_functionFailureCount = 0;
_functionSuccessCount = 0;
_EvaluationFailureCount = 0;
_EvaluationSuccessCount = 0;
}
private void verifyBug54084Unicode(XSSFWorkbook wb)
{
// expected data is stored in UTF-8 in a text-file
byte[] data = HSSFTestDataSamples.GetTestDataFileContent("54084 - Greek - beyond BMP.txt");
String testData = Encoding.UTF8.GetString(data).Trim();
ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell(0);
String value = cell.StringCellValue;
//Console.WriteLine(value);
Assert.AreEqual(testData, value, "The data in the text-file should exactly match the data that we read from the workbook");
}
protected void btnImport_Click(object sender, EventArgs e)
{
if (upExcel.HasFile)
{
string fileName = upExcel.FileName;
string fileExt = Path.GetExtension(fileName);
if (fileExt.Equals(".xls") || fileExt.Equals(".xlsx"))
{
string path = Server.MapPath("/File/" + fileName);
upExcel.SaveAs(path);
using (FileStream file = File.OpenRead(path))
{
try
{
// 根据文件名后缀加载IWorkbook
IWorkbook workbook = fileExt == ".xls" ? new HSSFWorkbook(file)
: workbook = new XSSFWorkbook(file);
ISheet sheet = workbook.GetSheetAt(0);
List<AttendanceInfoModel> attcList = new List<AttendanceInfoModel>();
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
AttendanceInfoModel attc = new AttendanceInfoModel();
attc.UserID = row.Cells[0].ToString();
attc.FaceTime = row.Cells[1].DateCellValue;
attcList.Add(attc);
}
AttendanceInfoBLL bll = new AttendanceInfoBLL();
bll.AppendAttendanceInfo(attcList);
ClientScript.RegisterStartupScript(this.GetType(), "import", "alert('保存成功！');parent.dialog.close();", true);
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "import", "alert('错误信息：\\nExcel的格式不正确，格式：用户ID + 打卡时间。');", true);
}
finally
{
if (File.Exists(path)) File.Delete(path);
}
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "import", "alert('错误信息：\\n不支持的文件格式，仅支持Excel2003和2007。');", true);
}
}
}
public override IEnumerable<IFreeDocument> ReadFile(Action<int> alreadyGetSize = null)
{
XSSFWorkbook hssfworkbook;
using (var file = new FileStream(FileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
List<string> titles = null;
try
{
titles= sheet.GetRow(0).Cells.Select(d => d.StringCellValue).ToList();
}
catch (Exception ex)
{
throw new Exception("请填写Excel的表头信息");
}
for (int i = 1; i < sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
var data = PluginProvider.GetObjectInstance(DataType) as IFreeDocument;
var dict = new Dictionary<string, object>();
for (int index = 0; index < titles.Count; index++)
{
string title = titles[index];
dict.Add(title, row.GetCell(index).ToString());
}
var freeDocument = data as IFreeDocument;
if (freeDocument != null)
{
if (i == 1)
{
PropertyNames = titles.ToDictionary(d => d, d => d);
}
}
data.DictDeserialize(dict);
yield return data;
if(i%1000==0)
XLogSys.Print.Info($"已经导入数量{i}，总共{sheet.LastRowNum}");
}
}
public Dictionary<int, string> GetSheets(string path)
{
var result = new Dictionary<int, string>();
using (var fs = File.OpenRead(path))
{
var workbook = new XSSFWorkbook(fs);
for (int sheetIdx = 0; sheetIdx < workbook.NumberOfSheets; sheetIdx++)
{
ISheet sheet = workbook.GetSheetAt(sheetIdx);
result.Add(sheetIdx, sheet.SheetName);
}
}
return result;
}
public static DataTable GetDataFromExcel(string excelFilePath)
{
//get exist excel file
XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open));
var sheet = excelBook.GetSheetAt(0);
var headerRow = sheet.GetRow(0);
var table = new DataTable();
//total columns
int cellCount = headerRow.LastCellNum;
//get column name
for (var i = headerRow.FirstCellNum; i < cellCount; i++)
{
var columnName = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(columnName);
}
//total rows
int rowCount = sheet.LastRowNum;
for (var i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
var dataRow = table.NewRow();
for (var j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(excelBook);//解析带有公式的Excel
var cell = e.EvaluateInCell(row.GetCell(j));
dataRow[j] = cell.ToString();
}
}
table.Rows.Add(dataRow);
}
return table;
}
public override DataTransactionResponse Run()
{
var dataTransactionResponse = new DataTransactionResponse();
XSSFWorkbook xssfWorkbook;
using (var file = new FileStream(Url, FileMode.Open, FileAccess.Read))
{
xssfWorkbook = new XSSFWorkbook(file);
}
var sheet = xssfWorkbook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
rows.MoveNext();
while (rows.MoveNext())
{
IRow row = (XSSFRow) rows.Current;
var response = CheckAndInsertData(row);
dataTransactionResponse.Messages.Add(response);
}
xssfWorkbook = null;
sheet = null;
return dataTransactionResponse;
}
public static Dictionary<string, string> ReadConfiguration(string configFile)
{
//READ CONFIG FILE
var dict = new Dictionary<string, string>();
XSSFWorkbook xssfwb;
using (FileStream file = new FileStream(configFile, FileMode.Open, FileAccess.Read))
{
xssfwb = new XSSFWorkbook(file);
}
ISheet sheet = xssfwb.GetSheetAt(0); //get the first sheet
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
var name = sheet.GetRow(row).GetCell(0).ToString();
var value = sheet.GetRow(row).GetCell(1).ToString();
dict.Add(name, value);
}
}
return dict;
}
public static void ReadConfiguration(Dictionary<string, ObjectInfo> library, string libraryFile)
{
XSSFWorkbook xssfwb;
using (FileStream file = new FileStream(libraryFile, FileMode.Open, FileAccess.Read))
{
xssfwb = new XSSFWorkbook(file);
}
ISheet sheet = xssfwb.GetSheetAt(0); //get the first sheet
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
var name = sheet.GetRow(row).GetCell(0).ToString();
var value = new ObjectInfo()
{
Name = name,
Type = sheet.GetRow(row).GetCell(1).ToString(),
Value = sheet.GetRow(row).GetCell(2).ToString()
};
library.Add(name, value);
}
}
}
public void TestReadAnchors()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4);
XSSFShape shape1 = Drawing.CreateTextbox(anchor1) as XSSFShape;
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5);
XSSFShape shape2 = Drawing.CreateTextbox(anchor2) as XSSFShape;
int pictureIndex = wb.AddPicture(new byte[] { }, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6);
XSSFShape shape3 = Drawing.CreatePicture(anchor3, pictureIndex) as XSSFShape;
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List<XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(3, shapes.Count);
Assert.AreEqual(shapes[0].GetAnchor(), anchor1);
Assert.AreEqual(shapes[1].GetAnchor(), anchor2);
Assert.AreEqual(shapes[2].GetAnchor(), anchor3);
}
public void TestSaveLoadNew()
{
XSSFWorkbook workbook = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
workbook.CreateSheet("sheet3");
IRichTextString rts = workbook.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.Name);
workbook.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
// Load back the XSSFWorkbook
workbook = new XSSFWorkbook(pkg);
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.IsNotNull(workbook.GetSheetAt(0));
Assert.IsNotNull(workbook.GetSheetAt(1));
Assert.IsNotNull(workbook.GetSheetAt(2));
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
sheet1 = workbook.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
}
public void TestIncrementSheetId()
{
XSSFWorkbook wb = new XSSFWorkbook();
int sheetId = (int)((XSSFSheet)wb.CreateSheet()).sheet.sheetId;
Assert.AreEqual(1, sheetId);
sheetId = (int)((XSSFSheet)wb.CreateSheet()).sheet.sheetId;
Assert.AreEqual(2, sheetId);
//test file with gaps in the sheetId sequence
wb = XSSFTestDataSamples.OpenSampleWorkbook("47089.xlsm");
int lastSheetId = (int)((XSSFSheet)wb.GetSheetAt(wb.NumberOfSheets - 1)).sheet.sheetId;
sheetId = (int)((XSSFSheet)wb.CreateSheet()).sheet.sheetId;
Assert.AreEqual(lastSheetId + 1, sheetId);
}
public void TestAddNewTextParagraphWithRTS()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String");
XSSFFont font = wb1.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromArgb(0, 255, 255)));
font.FontName = ("Arial");
rt.ApplyFont(font);
XSSFFont midfont = wb1.CreateFont() as XSSFFont;
midfont.SetColor(new XSSFColor(Color.FromArgb(0, 255, 0)));
rt.ApplyFont(5, 14, midfont); // Set the text "Rich Text" to be green and the default font
XSSFTextParagraph para = shape.AddNewTextParagraph(rt);
// Save and re-load it
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
// Check
Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = sshape.TextParagraphs;
Assert.AreEqual(2, paras.Count); // this should be 2 as XSSFSimpleShape Creates a default paragraph (no text), and then we add a string to that.
List <XSSFTextRun> runs = para.TextRuns;
Assert.AreEqual(3, runs.Count);
// first run properties
Assert.AreEqual("Test ", runs[0].Text);
Assert.AreEqual("Arial", runs[0].FontFamily);
Color clr = runs[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
// second run properties
Assert.AreEqual("Rich Text", runs[1].Text);
Assert.AreEqual(XSSFFont.DEFAULT_FONT_NAME, runs[1].FontFamily);
clr = runs[1].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 0 },
new int[] { clr.R, clr.G, clr.B }));
// third run properties
Assert.AreEqual(" String", runs[2].Text);
Assert.AreEqual("Arial", runs[2].FontFamily);
clr = runs[2].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb2);
wb2.Close();
}
/// <summary>
/// Get the parsable rows from a workbook
/// </summary>
/// <param name="rows">a reference to an object that will contain parsable rows</param>
/// <param name="workbook">the workbook to extract rows from</param>
private void GetRows(ImportData data, XSSFWorkbook workbook)
{
ISheet sheet = workbook.GetSheetAt(0);
bool reading = true;
int rowIndex = sheet.FirstRowNum;
while (reading) {
IRow row = sheet.GetRow(rowIndex);
if (RowIsParsable(row)) {
var validatorRow = new Row();
validatorRow.lineNumber = rowIndex;
for (int i = 0; i < data.fieldNames.Count; ++i) {
var fieldName = data.fieldNames[i];
var field = new Field() { value = CellValueAsString(row.GetCell(i)) };
validatorRow.fields.Add(fieldName, field);
}
data.rows.Add(validatorRow);
}
rowIndex++;
if (rowIndex > sheet.LastRowNum) {
reading = false;
}
}
}
public void TestReadTextBoxParagraphs()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
//the sheet has one relationship and it is XSSFDrawing
List <RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = textbox.TextParagraphs;
Assert.AreEqual(3, paras.Count);
Assert.AreEqual("Line 2", paras[1].Text); // check content of second paragraph
Assert.AreEqual("Line 1\nLine 2\nLine 3", textbox.Text); // check content of entire textbox
// check attributes of paragraphs
Assert.AreEqual(TextAlign.LEFT, paras[0].TextAlign);
Assert.AreEqual(TextAlign.CENTER, paras[1].TextAlign);
Assert.AreEqual(TextAlign.RIGHT, paras[2].TextAlign);
Color clr = paras[0].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 255, 0, 0 },
new int[] { clr.R, clr.G, clr.B }));
clr = paras[1].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 0 },
new int[] { clr.R, clr.G, clr.B }));
clr = paras[2].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 0, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb);
wb.Close();
}
public void TestAddBulletParagraphs()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet;
XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 10, 20));
String paraString1 = "A normal paragraph";
String paraString2 = "First bullet";
String paraString3 = "Second bullet (level 1)";
String paraString4 = "Third bullet";
String paraString5 = "Another normal paragraph";
String paraString6 = "First numbered bullet";
String paraString7 = "Second bullet (level 1)";
String paraString8 = "Third bullet (level 1)";
String paraString9 = "Fourth bullet (level 1)";
String paraString10 = "Fifth Bullet";
XSSFTextParagraph para = shape.AddNewTextParagraph(paraString1);
para = shape.AddNewTextParagraph(paraString2);
para.SetBullet(true);
para = shape.AddNewTextParagraph(paraString3);
para.SetBullet(true);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString4);
para.SetBullet(true);
para = shape.AddNewTextParagraph(paraString5);
para = shape.AddNewTextParagraph(paraString6);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD);
para = shape.AddNewTextParagraph(paraString7);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString8);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph("");
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString9);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString10);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD);
// Save and re-load it
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
// Check
drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = sshape.TextParagraphs;
Assert.AreEqual(12, paras.Count); // this should be 12 as XSSFSimpleShape Creates a default paragraph (no text), and then we Added to that
StringBuilder builder = new StringBuilder();
builder.Append(paraString1);
builder.Append("\n");
builder.Append("\u2022 ");
builder.Append(paraString2);
builder.Append("\n");
builder.Append("\t\u2022 ");
builder.Append(paraString3);
builder.Append("\n");
builder.Append("\u2022 ");
builder.Append(paraString4);
builder.Append("\n");
builder.Append(paraString5);
builder.Append("\n");
builder.Append("1. ");
builder.Append(paraString6);
builder.Append("\n");
builder.Append("\t3. ");
builder.Append(paraString7);
builder.Append("\n");
builder.Append("\t4. ");
builder.Append(paraString8);
builder.Append("\n");
builder.Append("\t"); // should be empty
builder.Append("\n");
builder.Append("\t5. ");
builder.Append(paraString9);
builder.Append("\n");
builder.Append("2. ");
builder.Append(paraString10);
Assert.AreEqual(builder.ToString(), sshape.Text);
checkRewrite(wb2);
wb2.Close();
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List<POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
//XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
c1.LineWidth = 2.5;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());
List<XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFConnector);
Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);
// Save and re-load it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
// Check
dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
CT_Drawing ctDrawing = dr1.GetCTDrawing();
// Connector, shapes and text boxes are all two cell anchors
Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());
shapes = dr1.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFConnector);
Assert.IsTrue(shapes[1] is XSSFShapeGroup);
Assert.IsTrue(shapes[2] is XSSFSimpleShape);
Assert.IsTrue(shapes[3] is XSSFSimpleShape); //
// Ensure it got the right namespaces
//String xml = ctDrawing.ToString();
//Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
//Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
}
public void TestLoadSave()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx");
ICreationHelper CreateHelper = workbook.GetCreationHelper();
Assert.AreEqual(3, workbook.NumberOfSheets);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
// Check hyperlinks
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Write out, and check
// Load up again, check all links still there
XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
sheet = (XSSFSheet)wb2.GetSheetAt(0);
// Check hyperlinks again
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Add one more, and re-check
IRow r17 = sheet.CreateRow(17);
ICell r17c = r17.CreateCell(2);
IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url);
hyperlink.Address = ("http://poi.apache.org/spreadsheet/");
hyperlink.Label = "POI SS Link";
r17c.Hyperlink = (hyperlink);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
// Save and re-load once more
XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2);
Assert.AreEqual(3, wb3.NumberOfSheets);
Assert.IsNotNull(wb3.GetSheetAt(0));
Assert.IsNotNull(wb3.GetSheetAt(1));
Assert.IsNotNull(wb3.GetSheetAt(2));
sheet = (XSSFSheet)wb3.GetSheetAt(0);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
}
public void TestGroupingTest()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("GroupTest.xlsx");
Assert.AreEqual(31, wb.GetSheetAt(0).LastRowNum);
// NOTE: This is currently based on current behavior of POI, somehow
// what POI returns in the calls to collapsed/hidden is not fully matching
// the examples in the spec or I did not fully understand how POI stores the data internally...
CheckWorkbookGrouping(wb,
new bool?[] {
// 0-4
false, false, false, false, false, null, null,
// 7-11
false, false, true, true, true, null, null,
// 14-18
false, false, true, false, false, null,
// 20-24
false, false, true, true, false, null, null,
// 27-31
false, false, false, true, false
},
new bool[] {
// 0-4
false, false, false, false, false, false, false,
// 7-11
true, true, true, true, false, false, false,
// 14-18
true, true, false, false, false, false,
// 20-24
true, true, true, false, false, false, false,
// 27-31
true, true, true, true, false
},
// outlineLevel
new int[] {
// 0-4
3, 3, 2, 1, 0, 0, 0,
// 7-11
3, 3, 2, 1, 0, 0, 0,
// 14-18
3, 3, 2, 1, 0, 0,
// 20-24
3, 3, 2, 1, 0, 0, 0,
// 27-31
3, 3, 2, 1, 0,
}
);
/*
* Row: 0: Level: 3 Collapsed: false Hidden: false
* Row: 1: Level: 3 Collapsed: false Hidden: false
* Row: 2: Level: 2 Collapsed: false Hidden: false
* Row: 3: Level: 1 Collapsed: false Hidden: false
* Row: 4: Level: 0 Collapsed: false Hidden: false
* Row: 7: Level: 3 Collapsed: false Hidden: true
* Row: 8: Level: 3 Collapsed: false Hidden: true
* Row: 9: Level: 2 Collapsed: true Hidden: true
* Row: 10: Level: 1 Collapsed: true Hidden: true
* Row: 11: Level: 0 Collapsed: true Hidden: false
* Row: 14: Level: 3 Collapsed: false Hidden: true
* Row: 15: Level: 3 Collapsed: false Hidden: true
* Row: 16: Level: 2 Collapsed: true Hidden: false
* Row: 17: Level: 1 Collapsed: false Hidden: false
* Row: 18: Level: 0 Collapsed: false Hidden: false
* Row: 20: Level: 3 Collapsed: false Hidden: true
* Row: 21: Level: 3 Collapsed: false Hidden: true
* Row: 22: Level: 2 Collapsed: true Hidden: true
* Row: 23: Level: 1 Collapsed: true Hidden: false
* Row: 24: Level: 0 Collapsed: false Hidden: false
* Row: 27: Level: 3 Collapsed: false Hidden: true
* Row: 28: Level: 3 Collapsed: false Hidden: true
* Row: 29: Level: 2 Collapsed: false Hidden: true
* Row: 30: Level: 1 Collapsed: true Hidden: true
* Row: 31: Level: 0 Collapsed: true Hidden: false
*/
}
public void TestAddValidations()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx");
ISheet sheet = workbook.GetSheetAt(0);
List <XSSFDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations();
/**
* For each validation type, there are two cells with the same validation. This Tests
* application of a single validation defInition to multiple cells.
*
* For list ( 3 validations for explicit and 3 for formula )
* - one validation that allows blank.
* - one that does not allow blank.
* - one that does not show the drop down arrow.
* = 2
*
* For number validations ( integer/decimal and text length ) with 8 different types of operators.
* = 50
*
* = 52 ( Total )
*/
Assert.AreEqual(52, dataValidations.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };
int[] SingleOperandOperatorTypes = new int[] {
OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL,
OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL,
OperatorType.EQUAL, OperatorType.NOT_EQUAL
};
int[] doubleOperandOperatorTypes = new int[] {
OperatorType.BETWEEN, OperatorType.NOT_BETWEEN
};
decimal value = (decimal)10, value2 = (decimal)20;
double dvalue = (double)10.001, dvalue2 = (double)19.999;
int lastRow = sheet.LastRowNum;
int offset = lastRow + 3;
int lastKnownNumValidations = dataValidations.Count;
IRow row = sheet.CreateRow(offset++);
ICell cell = row.CreateCell(0);
IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
row = sheet.CreateRow(offset++);
cell = row.CreateCell(0);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
ICell firstCell = row.CreateCell(1); firstCell.SetCellValue("UT");
ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN");
ICell thirdCell = row.CreateCell(3); thirdCell.SetCellValue("IL");
int rowNum = row.RowNum + 1;
String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString();
IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula);
dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
offset++;
offset++;
for (int i = 0; i < validationTypes.Length; i++)
{
int validationType = validationTypes[i];
offset = offset + 2;
IRow row0 = sheet.CreateRow(offset++);
ICell cell_10 = row0.CreateCell(0);
cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length");
offset++;
for (int j = 0; j < SingleOperandOperatorTypes.Length; j++)
{
int operatorType = SingleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
//For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null;
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null;
ICell cell_13 = row1.CreateCell(3);
cell_13.SetCellType(CellType.NUMERIC);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null);
if (i == 0 && j == 0)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else if (i == 0 && j == 1)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
for (int j = 0; j < doubleOperandOperatorTypes.Length; j++)
{
int operatorType = doubleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_13 = row1.CreateCell(3);
ICell cell_14 = row1.CreateCell(4);
String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString();
cell_13.SetCellType(CellType.NUMERIC);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value);
String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString();
cell_14.SetCellType(CellType.NUMERIC);
cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
ISheet sheetAt = workbook.GetSheetAt(0);
Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count);
}
protected bool OpenWB(string sExcelFileName)
{
Sheets.Clear();
if (Path.GetExtension(sExcelFileName).Equals(".xlsx"))
{
StatusChange("Importing from a newer Excel file format");
XSSFWorkbook wb;
using (FileStream file = new FileStream(sExcelFileName, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(file);
}
for (int i = 0; i < wb.Count; i++) {
ISheet sheet = wb.GetSheetAt(i);
Sheets.Add(sheet.SheetName, sheet);
}
}
else if (Path.GetExtension(sExcelFileName).Equals(".xls"))
{
StatusChange("This is an an older Excel file format");
HSSFWorkbook wb;
using (FileStream file = new FileStream(sExcelFileName, FileMode.Open, FileAccess.Read))
{
wb = new HSSFWorkbook(file);
}
for (int i = 0; i < wb.Count; i++) {
ISheet sheet = wb.GetSheetAt(i);
Sheets.Add(sheet.SheetName, sheet);
}
}
else
{
StatusChange("I am not sure what file extention this is.. exiting import");
throw new Exception("I am not sure what file extention this is");
}
return true;
}
public void TestReferencesToOtherWorkbooks()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("ref2-56737.xlsx");
XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet;
// References to a .xlsx file
IRow rXSLX = s.GetRow(2);
ICell cXSLX_cell = rXSLX.GetCell(4);
ICell cXSLX_sNR = rXSLX.GetCell(6);
ICell cXSLX_gNR = rXSLX.GetCell(8);
Assert.AreEqual("[1]Uses!$A$1", cXSLX_cell.CellFormula);
Assert.AreEqual("[1]Defines!NR_To_A1", cXSLX_sNR.CellFormula);
Assert.AreEqual("[1]!NR_Global_B2", cXSLX_gNR.CellFormula);
Assert.AreEqual("Hello!", cXSLX_cell.StringCellValue);
Assert.AreEqual("Test A1", cXSLX_sNR.StringCellValue);
Assert.AreEqual(142.0, cXSLX_gNR.NumericCellValue);
// References to a .xls file
IRow rXSL = s.GetRow(4);
ICell cXSL_cell = rXSL.GetCell(4);
ICell cXSL_sNR = rXSL.GetCell(6);
ICell cXSL_gNR = rXSL.GetCell(8);
Assert.AreEqual("[2]Uses!$C$1", cXSL_cell.CellFormula);
Assert.AreEqual("[2]Defines!NR_To_A1", cXSL_sNR.CellFormula);
Assert.AreEqual("[2]!NR_Global_B2", cXSL_gNR.CellFormula);
Assert.AreEqual("Hello!", cXSL_cell.StringCellValue);
Assert.AreEqual("Test A1", cXSL_sNR.StringCellValue);
Assert.AreEqual(142.0, cXSL_gNR.NumericCellValue);
// Try to Evaluate without references, won't work
// (At least, not unit we fix bug #56752 that is1)
try
{
evaluator.Evaluate(cXSL_cell);
Assert.Fail("Without a fix for #56752, shouldn't be able to Evaluate a " +
"reference to a non-provided linked workbook");
}
catch (Exception e) { }
// Setup the environment
Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>();
evaluators.Add("ref2-56737.xlsx", evaluator);
evaluators.Add("56737.xlsx",
_testDataProvider.OpenSampleWorkbook("56737.xlsx").GetCreationHelper().CreateFormulaEvaluator());
evaluators.Add("56737.xls",
HSSFTestDataSamples.OpenSampleWorkbook("56737.xls").GetCreationHelper().CreateFormulaEvaluator());
evaluator.SetupReferencedWorkbooks(evaluators);
// Try Evaluating all of them, ensure we don't blow up
foreach (IRow r in s)
{
foreach (ICell c in r)
{
// TODO Fix and enable
evaluator.Evaluate(c);
}
}
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSLX_cell).FormatAsString());
Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSLX_sNR).FormatAsString());
//Assert.AreEqual("142.0", evaluator.Evaluate(cXSLX_gNR).FormatAsString());
Assert.AreEqual("142", evaluator.Evaluate(cXSLX_gNR).FormatAsString());
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell).FormatAsString());
Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSL_sNR).FormatAsString());
//Assert.AreEqual("142.0", evaluator.Evaluate(cXSL_gNR).FormatAsString());
Assert.AreEqual("142", evaluator.Evaluate(cXSL_gNR).FormatAsString());
// Add another formula referencing these workbooks
ICell cXSL_cell2 = rXSL.CreateCell(40);
cXSL_cell2.CellFormula = (/*setter*/ "[56737.xls]Uses!$C$1");
// TODO Shouldn't it become [2] like the others?
Assert.AreEqual("[56737.xls]Uses!$C$1", cXSL_cell2.CellFormula);
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell2).FormatAsString());
// Now add a formula that refers to yet another (different) workbook
// Won't work without the workbook being linked
ICell cXSLX_nw_cell = rXSLX.CreateCell(42);
try {
cXSLX_nw_cell.CellFormula = (/*setter*/ "[alt.xlsx]Sheet1!$A$1");
Assert.Fail("New workbook not linked, shouldn't be able to Add");
} catch (Exception e) {}
// Link and re-try
IWorkbook alt = new XSSFWorkbook();
alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
// TODO Implement the rest of this, see bug #57184
/*
* wb.LinkExternalWorkbook("alt.xlsx", alt);
*
* cXSLX_nw_cell.SetCellFormula"[alt.xlsx]Sheet1!$A$1");
* // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ?
* Assert.AreEqual("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.CellFormula);
*
* // Evaluate it, without a link to that workbook
* try {
* Evaluator.Evaluate(cXSLX_nw_cell);
* Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate");
* } catch(Exception e) {}
*
* // Add a link, check it does
* Evaluators.Put("alt.xlsx", alt.GetCreationHelper().CreateFormulaEvaluator());
* Evaluator.SetupReferencedWorkbooks(evaluators);
*
* Evaluator.Evaluate(cXSLX_nw_cell);
* Assert.AreEqual("In another workbook", cXSLX_nw_cell.StringCellValue);
*/
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
//XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
c1.LineWidth = 2.5;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
Assert.IsNotNull(c2);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFConnector);
Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);
// Save and re-load it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
// Check
dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
CT_Drawing ctDrawing = dr1.GetCTDrawing();
// Connector, shapes and text boxes are all two cell anchors
Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());
shapes = dr1.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFConnector);
Assert.IsTrue(shapes[1] is XSSFShapeGroup);
Assert.IsTrue(shapes[2] is XSSFSimpleShape);
Assert.IsTrue(shapes[3] is XSSFSimpleShape); //
// Ensure it got the right namespaces
//String xml = ctDrawing.ToString();
//Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
//Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestSaveLoadNew()
{
XSSFWorkbook workbook = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
workbook.CreateSheet("sheet3");
IRichTextString rts = workbook.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.Name);
workbook.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
// Load back the XSSFWorkbook
workbook = new XSSFWorkbook(pkg);
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.IsNotNull(workbook.GetSheetAt(0));
Assert.IsNotNull(workbook.GetSheetAt(1));
Assert.IsNotNull(workbook.GetSheetAt(2));
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
sheet1 = workbook.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
}
public IEvaluationSheet GetSheet(int sheetIndex)
{
return(new XSSFEvaluationSheet(_uBook.GetSheetAt(sheetIndex)));
}
/// <summary>
/// 利用NPOI导入Excel文件数据
/// </summary>
/// <param name="XlsFilesPathName">获取选定文件的路径名</param>
/// <returns></returns>
public static DataTable NPOIImportExcelFile(string XlsFilesPathName)
{
try
{
DataTable ExcelTable = new DataTable();//创建填充数据表
if (XlsFilesPathName == string.Empty)
{
MessageBoxEx.Show("请您选择要导入的配件信息文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else if (Path.GetExtension(XlsFilesPathName) != ".xlsx" && Path.GetExtension(XlsFilesPathName) != ".xls")
{
MessageBoxEx.Show("请您选择Excel格式的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
IWorkbook WkBk = null;//创建Excel工作簿
ISheet sht = null;//获取第一个sheet表
IRow HeaderRow = null;//获取Excel标题行
FileStream fsxls = new FileStream(XlsFilesPathName, FileMode.Open, FileAccess.Read);//读取Excel文件流
if (Path.GetExtension(XlsFilesPathName) == ".xlsx")
{
WkBk = new XSSFWorkbook(fsxls);//生成支持Office2007以上版本的Excel表格工作簿
sht = (XSSFSheet)WkBk.GetSheetAt(0);//获取第一个sheet表数据
HeaderRow = (XSSFRow)sht.GetRow(0);//获取Excel标题行
}
else if (Path.GetExtension(XlsFilesPathName) == ".xls")
{
WkBk = new HSSFWorkbook(fsxls);//生成支持Office2003及以下版本的Excel表格工作簿
sht = (HSSFSheet)WkBk.GetSheetAt(0);//获取第一个sheet表数据
HeaderRow = (HSSFRow)sht.GetRow(0);//获取Excel标题行
}
fsxls.Close();
fsxls.Dispose();
int FirstCellIndx = HeaderRow.FirstCellNum;//获取Excel标题行首列索引
int LastCellIndx = HeaderRow.LastCellNum;//获取Excel标题行尾列索引
//创建表列
for (int i = FirstCellIndx; i < LastCellIndx; i++)
{
DataColumn DataCol = new DataColumn();
DataCol.ColumnName = HeaderRow.GetCell(i).StringCellValue;//标题列名称
ExcelTable.Columns.Add(DataCol);//添加表列名
}
int FirstRowIndx = sht.FirstRowNum;//获取Excel首行索引
int LastRowIndx = sht.LastRowNum;//获取Excel首行索引
//创建表行
for (int j = FirstRowIndx + 1; j < LastRowIndx; j++)
{
IRow DatasRow = sht.GetRow(j);
DataRow TableRow = ExcelTable.NewRow();//创建表行
for (int k = FirstCellIndx; k < LastCellIndx; k++)
{
//获取Excel表行数据值
string XlsCellValue = DatasRow.GetCell(k).ToString() == string.Empty ? "" : DatasRow.GetCell(k).ToString();
TableRow[k] = XlsCellValue;
}
ExcelTable.Rows.Add(TableRow);//添加表行
}
WkBk = null;
sht = null;
}
return ExcelTable;//返回数据表
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
return null;
}
}
public void TestCommentShowsBox()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet();
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFCell cell = (XSSFCell)sheet.CreateRow(0).CreateCell(0);
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFCreationHelper factory = (XSSFCreationHelper)wb.GetCreationHelper();
XSSFClientAnchor anchor = (XSSFClientAnchor)factory.CreateClientAnchor();
anchor.Col1 = cell.ColumnIndex;
anchor.Col2 = cell.ColumnIndex + 3;
anchor.Row1 = cell.RowIndex;
anchor.Row2 = cell.RowIndex + 5;
XSSFComment comment = (XSSFComment)drawing.CreateCellComment(anchor);
XSSFRichTextString str = (XSSFRichTextString)factory.CreateRichTextString("this is a comment");
comment.String = str;
cell.CellComment = comment;
XSSFVMLDrawing vml = sheet.GetVMLDrawing(false);
CT_Shapetype shapetype = null;
ArrayList items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
using (MemoryStream ws = new MemoryStream())
{
wb.Write(ws);
using (MemoryStream rs = new MemoryStream(ws.GetBuffer()))
{
wb = new XSSFWorkbook(rs);
sheet = (XSSFSheet)wb.GetSheetAt(0);
vml = sheet.GetVMLDrawing(false);
shapetype = null;
items = vml.GetItems();
foreach (object o in items)
{
if (o is CT_Shapetype)
{
shapetype = (CT_Shapetype)o;
}
}
//wb.Write(new FileStream("comments.xlsx", FileMode.Create));
//using (MemoryStream ws2 = new MemoryStream())
//{
// vml.Write(ws2);
// throw new System.Exception(System.Text.Encoding.UTF8.GetString(ws2.GetBuffer()));
//}
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.stroked);
Assert.AreEqual(NPOI.OpenXmlFormats.Vml.ST_TrueFalse.t, shapetype.filled);
}
}
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls =
{
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address = (s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink = (link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
}
private LoadPatternList()
{
using (FileStream fs = new FileStream(path,FileMode.Open,FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(fs);
int sheetnumber = workbook.NumberOfSheets;
int [][][] sheetelements = new int[sheetnumber][][];
for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
ISheet _isheet = workbook.GetSheetAt(sheetindex);
int lastrownum = _isheet.LastRowNum;
int [][] rowelements = new int[lastrownum+1][];
for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
IRow row = _isheet.GetRow(rowindex);
if(row == null)continue;
int lastcellnum = row.LastCellNum;
int[] cellelements = new int[lastcellnum+1];
for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
ICell cell = row.GetCell(cellindex);
if(cell != null){
cellelements[cellindex] = Convert.ToInt32(cell.ToString());
}
}
rowelements[rowindex] = cellelements;
}
sheetelements[sheetindex] = rowelements;
}
data = sheetelements;
}
if(data.Length > 0){
checksign = "No";
}
}
