public void TestReMoveCell()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet();
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
Assert.AreEqual(-1, row.LastCellNum);
Assert.AreEqual(-1, row.FirstCellNum);
row.CreateCell(1);
Assert.AreEqual(2, row.LastCellNum);
Assert.AreEqual(1, row.FirstCellNum);
row.CreateCell(3);
Assert.AreEqual(4, row.LastCellNum);
Assert.AreEqual(1, row.FirstCellNum);
row.RemoveCell(row.GetCell(3));
Assert.AreEqual(2, row.LastCellNum);
Assert.AreEqual(1, row.FirstCellNum);
row.RemoveCell(row.GetCell(1));
Assert.AreEqual(-1, row.LastCellNum);
Assert.AreEqual(-1, row.FirstCellNum);
// all cells on this row have been Removed
// so Check the row record actually Writes it out as 0's
byte[] data = new byte[100];
row.RowRecord.Serialize(0, data);
Assert.AreEqual(0, data[6]);
Assert.AreEqual(0, data[8]);
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
Assert.AreEqual(-1, sheet.GetRow(0).LastCellNum);
Assert.AreEqual(-1, sheet.GetRow(0).FirstCellNum);
}
public void TestLastAndFirstColumns_bug46654()
{
int ROW_IX = 10;
int COL_IX = 3;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Sheet1");
RowRecord rowRec = new RowRecord(ROW_IX);
rowRec.FirstCol = ((short)2);
rowRec.LastCol = ((short)5);
BlankRecord br = new BlankRecord();
br.Row = (ROW_IX);
br.Column = ((short)COL_IX);
sheet.Sheet.AddValueRecord(ROW_IX, br);
HSSFRow row = new HSSFRow(workbook, sheet, rowRec);
ICell cell = row.CreateCellFromRecord(br);
if (row.FirstCellNum == 2 && row.LastCellNum == 5)
{
throw new AssertionException("Identified bug 46654a");
}
Assert.AreEqual(COL_IX, row.FirstCellNum);
Assert.AreEqual(COL_IX + 1, row.LastCellNum);
row.RemoveCell(cell);
Assert.AreEqual(-1, row.FirstCellNum);
Assert.AreEqual(-1, row.LastCellNum);
}
private void execute()
{
try
{
HSSFWorkbook inputBook = new HSSFWorkbook(new FileStream(INPUT_URL, FileMode.Open)); //input不可外流，放在GitHub外層
HSSFSheet inputSheet = (HSSFSheet)inputBook.GetSheetAt(0); //目前只取第一分頁sheet0
//提早取得名稱
//取得資料
int iNumRow = inputSheet.LastRowNum;
for (int i = 0; i < iNumRow; ++i)
{
//保留標題列略過
if (i < this.keepRow)
{
continue;
}
HSSFWorkbook outputBook = new HSSFWorkbook();
HSSFSheet outputSheet = (HSSFSheet)outputBook.CreateSheet("sheet1"); // 在 Excel 工作簿中建立工作表，名稱為 Sheet1
HSSFRow iCurRow = (HSSFRow)inputSheet.GetRow(i); //取得第N列資料
//標題列數(注意:最後一條保留列為資料標題)
int titleCellCount = 0;
int ci = 0;
//先寫入保留標題列---------------------------------------------------------------------------------------
for (int ki = 0; ki < this.keepRow; ++ki)
{
//查詢保留列
HSSFRow iTitleRow = (HSSFRow)inputSheet.GetRow(ki);
//保留列有多少欄?
titleCellCount = iTitleRow.LastCellNum;
//建立保留列
HSSFRow oTitleRow = (HSSFRow)outputSheet.CreateRow(ki);
//設定同等列高
oTitleRow.Height = iTitleRow.Height;
for (ci = 0; ci < titleCellCount; ++ci)
{
//input.J copy to output.J
ICell inTItleCell = iTitleRow.GetCell(ci);
if (inTItleCell != null)
{
ICell oTitleCell = oTitleRow.CreateCell(ci);
oTitleCell.SetCellValue(inTItleCell.StringCellValue);
//複製欄格式
HSSFCellStyle newTitleCellStyle = (HSSFCellStyle)outputBook.CreateCellStyle();
newTitleCellStyle.CloneStyleFrom(inTItleCell.CellStyle);
oTitleCell.CellStyle = newTitleCellStyle;
}
}
}
//寫入資料---------------------------------------------------------------------------------------
//建立員工資料列
string name = "temp";
string apartment = "temp";
HSSFRow oDataRow = (HSSFRow)outputSheet.CreateRow(this.keepRow);
oDataRow.Height = iCurRow.Height;
for (ci = 0; ci < titleCellCount; ++ci)
{
ICell inDataCell = iCurRow.GetCell(ci);
if (inDataCell == null)
{
continue;
}
//取得名字
if (ci == this.nameCell)
{
name = inDataCell.StringCellValue;
}
//取得部門
if (ci == this.nameCell + 1)
{
apartment = inDataCell.StringCellValue;
}
ICell oDataCell = oDataRow.CreateCell(ci);
if (this.config.needToSkipCol(name, ci + 1))
{
outputSheet.SetColumnHidden(ci, true);
}
//到職日特別處理
if (ci == 0)
{
var value = inDataCell.DateCellValue;
oDataCell.SetCellValue(value.ToShortDateString());//轉換為2014/12/19
}
//數字
else if (inDataCell.CellType == NPOI.SS.UserModel.CellType.Numeric || inDataCell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
var value = inDataCell.NumericCellValue;
oDataCell.SetCellValue(value);
}
else
{
oDataCell.SetCellValue(inDataCell.StringCellValue);
}
HSSFCellStyle newCellStyle = (HSSFCellStyle)outputBook.CreateCellStyle();
newCellStyle.CloneStyleFrom(inDataCell.CellStyle);
oDataCell.CellStyle = newCellStyle;
outputSheet.AutoSizeColumn(ci);
//oDataCell.CellStyle.WrapText = true;
}
//刪除-------------------------------------------------------------------
for (int ri = 0; ri <= outputSheet.LastRowNum; ++ri)
{
HSSFRow eachRow = (HSSFRow)outputSheet.GetRow(ri);//取得第N列資料
titleCellCount = eachRow.LastCellNum;
for (ci = titleCellCount - 1; ci > -1; --ci)
{
if (this.config.needToSkipCol(name, ci + 1))
{
ICell eachCell = eachRow.GetCell(ci);
if (eachCell == null)
{
eachCell = eachRow.CreateCell(ci);
}
eachRow.RemoveCell(eachCell);
}
}
}
//output不可外流，放在GitHub外層
string fileName = OUTPUT_URL;
fileName = fileName.Replace("#1", apartment);
fileName = fileName.Replace("#2", name);
Directory.CreateDirectory(Path.GetDirectoryName(fileName));
FileStream outputStream = new FileStream(fileName, FileMode.Create);
outputBook.Write(outputStream);
outputBook.WriteProtectWorkbook("123", "456");
outputStream.Close();
}
MessageBox.Show("薪資拆分成功!");
System.Windows.Forms.Application.Exit();//自動關閉APP
}
catch (Exception error)
{
MessageBox.Show("異常錯誤 " + error.Message);
}
}