public void TestBug50298()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
ISheet sheet = wb.CloneSheet(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)");
wb.SetSheetName(wb.GetSheetIndex(sheet), "copy");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy");
wb.SetSheetOrder("copy", 0);
assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received");
wb.RemoveSheetAt(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
// check that the overall workbook serializes with its correct size
int expected = wb.Workbook.Size;
int written = wb.Workbook.Serialize(0, new byte[expected * 2]);
Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected);
HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb);
assertSheetOrder(read, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
}
public void SetSheetName(string sheetName, string newSheetName, ref int sheetIndex)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
_currentSheet = _workbook.GetSheetAt(i);
_workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
_workbook.SetSheetOrder(newSheetName, _workbook.NumberOfSheets);
_defaultExcelCellStyle = new ExcelCellStyle();
}
public void createManagerTab(List <ClientTradeSummary> tradeStatistics_, List <string> tradingDays_, HSSFWorkbook wb_, string tabName_)
{
ISheet tb = wb_.CreateSheet(tabName_);
wb_.SetSheetOrder(tabName_, 0);
sortAlgos();
generateDateLine(tradingDays_, tb, wb_);
addStrategyLine(tb, wb_);
addColumeNameLine(tb, wb_);
addStatistics(tradeStatistics_, tb, wb_);
computeAggregateStatistics(tradeStatistics_);
addSummaryLine(tb, wb_);
setAutoSizeColumn(tb, columeNb);
mergeCells(tb);
currentLine = 0;
}
public void TestSetSheetOrderHSSF()
{
IWorkbook wb = new HSSFWorkbook();
ISheet s1 = wb.CreateSheet("first sheet");
ISheet s2 = wb.CreateSheet("other sheet");
IName name1 = wb.CreateName();
name1.NameName = (/*setter*/ "name1");
name1.RefersToFormula = (/*setter*/ "'first sheet'!D1");
IName name2 = wb.CreateName();
name2.NameName = (/*setter*/ "name2");
name2.RefersToFormula = (/*setter*/ "'other sheet'!C1");
IRow s1r1 = s1.CreateRow(2);
ICell c1 = s1r1.CreateCell(3);
c1.SetCellValue(30);
ICell c2 = s1r1.CreateCell(2);
c2.CellFormula = (/*setter*/ "SUM('other sheet'!C1,'first sheet'!C1)");
IRow s2r1 = s2.CreateRow(0);
ICell c3 = s2r1.CreateCell(1);
c3.CellFormula = (/*setter*/ "'first sheet'!D3");
ICell c4 = s2r1.CreateCell(2);
c4.CellFormula = (/*setter*/ "'other sheet'!D3");
// conditional formatting
ISheetConditionalFormatting sheetCF = s1.SheetConditionalFormatting;
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(
ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1");
IConditionalFormattingRule[] cfRules = { rule1 };
CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5
};
sheetCF.AddConditionalFormatting(regions, cfRules);
wb.SetSheetOrder("other sheet", 0);
// names
Assert.AreEqual("'first sheet'!D1", wb.GetName("name1").RefersToFormula);
Assert.AreEqual("'other sheet'!C1", wb.GetName("name2").RefersToFormula);
// cells
Assert.AreEqual("SUM('other sheet'!C1,'first sheet'!C1)", c2.CellFormula);
Assert.AreEqual("'first sheet'!D3", c3.CellFormula);
Assert.AreEqual("'other sheet'!D3", c4.CellFormula);
// conditional formatting
IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual("'first sheet'!D1", cf.GetRule(0).Formula1);
Assert.AreEqual("'other sheet'!D1", cf.GetRule(0).Formula2);
}
/// <summary>
/// DataTable 数据添加到Excel模板中.
/// </summary>
/// <param name="newFilePath"></param>
/// <param name="dt">输入DataTable</param>
/// <param name="startRow">NPOI开始的行号,从0开始.(对应的Excel列从0开始行).</param>
/// <param name="rowHeight">行高.(Excel中设置的值,方法中已进行x20计算)</param>
/// <param name="styleCell_row">样式单元的行号,默认0行.</param>
/// <param name="styleCell_col">样式单元的列号,默认0列.</param>
/// <returns></returns>
public static string CreateExcelTemplate(string newFilePath, DataSet ds, string sheetName = "sheet1")
{
try
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
//单元格的四周边框设置为细边框.
ICellStyle style = workbook.CreateCellStyle();//.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//cell.CellStyle = style;
int startRowNum = 0;
foreach (DataTable dt in ds.Tables)
{
//if (dt.Rows[0][1] + "" == dt.Rows[0][2] + "")
//{
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, startRow + dt.Rows.Count - 1, 1, 2));
//}
//else
//{
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, startRow + dt.Rows.Count - 1, 1, 1));
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, startRow + dt.Rows.Count - 1, 2, 2));
//}
//DataRow preRow = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow curRow = dt.Rows[i];
//preRow = i == 0 ? dt.Rows[0] : dt.Rows[i - 1];
//if(curRow[1]+""==preRow[1]+""&& curRow[2] + ""==preRow[2] + "" && curRow[1] + ""==curRow[1] + "")
//{
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i-1<0?0:i, i, 1, 2));
//}else
//{
// if (curRow[0] + ""==preRow[0] + "")
// {
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 1, 1));
// }
// if (curRow[1] + ""==preRow[1] + "")
// {
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 2, 2));
// }
//}
//DataRow dr = dt.Rows[i];
IRow excelRow = sheet.CreateRow(startRowNum + i);
//插入行.第"startRow"到第"sheet.LastRowNum"行移动"dt.Rows.Count"行,bool copyRowHeight, bool resetOriginalRowHeight
//sheet.ShiftRows(i, i + 1, dt.Rows.Count, true, true);
for (int j = 0; j < dt.Columns.Count; j++)
{
var excelCell = excelRow.CreateCell(j, CellType.String);
#region 单元格样式
excelCell.CellStyle = style;// sheet.GetRow(styleCell_row).Cells[styleCell_col].CellStyle;
#endregion
excelCell.SetCellValue(curRow[j].ToString());
}
}
startRowNum += dt.Rows.Count;
}
Dictionary <string, List <int> > dic = new Dictionary <string, List <int> >();
List <int[]> List = new List <int[]>();
int startRowNo = 0, endRowNo = 0, startColNo, endColNo;
ICell preCel, curCel;
//合并相同的单元格
for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
IRow row = sheet.GetRow(rowIndex);
string cel1Val = row.GetCell(1).StringCellValue;
string cel2Val = row.GetCell(2).StringCellValue;
string precel1Val = sheet.GetRow(rowIndex - 1).GetCell(1).StringCellValue;
string precel2Val = sheet.GetRow(rowIndex - 1).GetCell(2).StringCellValue;
if (!dic.ContainsKey(cel1Val))
{
dic.Add(cel1Val, new List <int>());
}
if (!dic.ContainsKey(cel2Val))
{
dic.Add(cel2Val, new List <int>());
}
if (precel1Val == cel1Val && precel1Val == cel2Val)
{
if (dic[precel1Val].Count > 0)
{
dic[precel1Val][1] = rowIndex;
}
endRowNo = rowIndex;
}
else
{
List.Add(new int[] { startRowNo, endRowNo });
}
if (cel1Val == cel2Val)
{
startColNo = 1;
endColNo = 2;
}
//if (cellVal + "" == cel2 + "" && curRow[2] + "" == preRow[2] + "" && curRow[1] + "" == curRow[1] + "")
//{
// //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1 < 0 ? 0 : i, i, 1, 2));
//}
//else
//{
// //if (curRow[0] + "" == preRow[0] + "")
// //{
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 1, 1));
// //}
// //if (curRow[1] + "" == preRow[1] + "")
// //{
// // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i - 1, i, 2, 2));
// //}
//}
//if (cell == null)
//{
// continue;
//}
//int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度
//columnWidth = columnWidth < contextLength ? contextLength : columnWidth;
}
//自适应列宽
AutoColumnWidth(sheet, 8);
//保存Excel
workbook.SetSheetOrder(sheet.SheetName, 0);//设置表格位置
workbook.SetActiveSheet(0);
using (FileStream fs = new FileStream(newFilePath, FileMode.Create))
{
workbook.Write(fs);
}
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
//刷新表注释
private void RefreshNote(string fileFullName)
{
bool change = false;
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
change = true; break;
}
}
if (!change)
{
return;
}
IWorkbook workbook = new HSSFWorkbook(new FileStream(fileFullName, FileMode.Open, FileAccess.ReadWrite));
ISheet sheet = workbook.GetSheetAt(0);
string sheetName = sheet.SheetName;
ISheet sheetBack = workbook.CreateSheet("__sheetBackup");
sheetBack.CreateFreezePane(2, START_ROW);
int columnNum = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.LastCellNum > columnNum)
{
columnNum = row.LastCellNum;
}
IRow rowBack = sheetBack.CreateRow(i);
for (int j = 0; j < (mMaxColumn == -1 ? row.LastCellNum : mMaxColumn); ++j)
{
string val = Util.GetCellString(row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK));
var cell = rowBack.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
Util.SetCellString(cell, val);
}
}
for (int i = 0; i < columnNum; ++i)
{
sheetBack.SetColumnWidth(i, sheet.GetColumnWidth(i));
}
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
CellRangeAddressList cellRange = new CellRangeAddressList(START_ROW, 65535, i, i);
IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(GetEnumList(filed.Type));
IDataValidation dataValidation = helper.CreateValidation(constraint, cellRange);
dataValidation.SuppressDropDownArrow = false;
dataValidation.CreatePromptBox(filed.Type, GetEnumComment(filed.Type));
sheetBack.AddValidationData(dataValidation);
}
}
workbook.RemoveSheetAt(0);
workbook.SetSheetOrder(sheetBack.SheetName, 0);
workbook.SetSheetName(0, sheetName);
using (FileStream stream = new FileStream(fileFullName, FileMode.Create)) {
workbook.Write(stream);
}
}
