public void TestSelectedSheet_bug44523()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet("Sheet1");
NPOI.SS.UserModel.Sheet sheet2 = wb.CreateSheet("Sheet2");
NPOI.SS.UserModel.Sheet sheet3 = wb.CreateSheet("Sheet3");
NPOI.SS.UserModel.Sheet sheet4 = wb.CreateSheet("Sheet4");
ConfirmActiveSelected(sheet1, true);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetSelectedTab(1);
// Demonstrate bug 44525:
// Well... not quite, since isActive + isSelected were also Added in the same bug fix
if (sheet1.IsSelected)
{
throw new AssertFailedException("Identified bug 44523 a");
}
wb.ActiveSheetIndex = (1);
if (sheet1.IsActive)
{
throw new AssertFailedException("Identified bug 44523 b");
}
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, true);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
}
public void TestReadWriteDuplicatedRichText1()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("duprich1.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(1);
Assert.AreEqual("01/05 (Wed)", sheet.GetRow(0).GetCell((short)8).StringCellValue);
Assert.AreEqual("01/05 (Wed)", sheet.GetRow(1).GetCell((short)8).StringCellValue);
MemoryStream baos = new MemoryStream();
wb.Write(baos);
// test the second file.
wb = HSSFTestDataSamples.OpenSampleWorkbook("duprich2.xls");
sheet = wb.GetSheetAt(0);
int row = 0;
Assert.AreEqual("Testing", sheet.GetRow(row++).GetCell((short)0).StringCellValue);
Assert.AreEqual("rich", sheet.GetRow(row++).GetCell((short)0).StringCellValue);
Assert.AreEqual("text", sheet.GetRow(row++).GetCell((short)0).StringCellValue);
Assert.AreEqual("strings", sheet.GetRow(row++).GetCell((short)0).StringCellValue);
Assert.AreEqual("Testing", sheet.GetRow(row++).GetCell((short)0).StringCellValue);
Assert.AreEqual("Testing", sheet.GetRow(row++).GetCell((short)0).StringCellValue);
wb.Write(baos);
}
/// <summary>
/// Calculates the height of a client anchor in points.
/// </summary>
/// <param name="sheet">the sheet the anchor will be attached to</param>
/// <returns>the shape height.</returns>
public float GetAnchorHeightInPoints(NPOI.SS.UserModel.Sheet sheet)
{
int y1 = Dy1;
int y2 = Dy2;
int row1 = Math.Min(Row1, Row2);
int row2 = Math.Max(Row1, Row2);
float points = 0;
if (row1 == row2)
{
points = ((y2 - y1) / 256.0f) * GetRowHeightInPoints(sheet, row2);
}
else
{
points += ((256.0f - y1) / 256.0f) * GetRowHeightInPoints(sheet, row1);
for (int i = row1 + 1; i < row2; i++)
{
points += GetRowHeightInPoints(sheet, i);
}
points += (y2 / 256.0f) * GetRowHeightInPoints(sheet, row2);
}
return(points);
}
/**
* @param streamOffset start position for serialization. This affects values in some
* records such as INDEX, but most callers will be OK to pass zero.
* @return the {@link Record}s (in order) which will be output when the
* specified sheet is serialized
*/
public static Record[] GetRecords(NPOI.SS.UserModel.Sheet hSheet, int streamOffset)
{
RecordCollector rc = new RecordCollector();
((HSSFSheet)hSheet).Sheet.VisitContainedRecords(rc, streamOffset);
return(rc.Records);
}
public void TestSpuriousSharedFormulaFlag()
{
long actualCRC = GetFileCRC(HSSFTestDataSamples.OpenSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
long expectedCRC = 2277445406L;
if (actualCRC != expectedCRC)
{
Console.Error.WriteLine("Expected crc " + expectedCRC + " but got " + actualCRC);
throw failUnexpectedTestFileChange();
}
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0); // Sheet1
String cellFormula;
cellFormula = GetFormulaFromFirstCell(s, 0); // row "1"
// the problem is1 not observable in the first row of the shared formula
if (!cellFormula.Equals("\"first formula\""))
{
throw new Exception("Something else wrong with this Test case");
}
// but the problem is1 observable in rows 2,3,4
cellFormula = GetFormulaFromFirstCell(s, 1); // row "2"
if (cellFormula.Equals("\"second formula\""))
{
throw new AssertFailedException("found bug 44449 (Wrong SharedFormulaRecord was used).");
}
if (!cellFormula.Equals("\"first formula\""))
{
throw new Exception("Something else wrong with this Test case");
}
}
private void SaveExcel(List <FileProperty> flist)
{
saveFileDialog1.OverwritePrompt = true;
saveFileDialog1.AddExtension = true;
saveFileDialog1.DefaultExt = ".xls";
saveFileDialog1.AutoUpgradeEnabled = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
toolStripStatusLabel1.Text = "正在生成Excel文件";
string path = saveFileDialog1.FileName;
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workBook.CreateSheet("Sheet1");
NPOI.SS.UserModel.Row row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("Title");
row1.CreateCell(1).SetCellValue("FilePath");
row1.CreateCell(2).SetCellValue("Description");
row1.CreateCell(3).SetCellValue("Tags");
row1.CreateCell(4).SetCellValue("Price");
row1.CreateCell(5).SetCellValue("CateId");
row1.CreateCell(6).SetCellValue("Test");
for (int i = 0; i < flist.Count; i++)
{
NPOI.SS.UserModel.Row r = sheet.CreateRow(i + 1);
r.CreateCell(0).SetCellValue(flist[i].Name);
r.CreateCell(1).SetCellValue(flist[i].Path);
}
using (FileStream fs = new FileStream(path, FileMode.Create))
{
workBook.Write(fs);
}
toolStripStatusLabel1.Text = "生成Excel成功";
}
}
public FileResult AgreementDetailListExportExcel(long accountId, int enumOrderTypeId, DateTime?startDate, DateTime?endDate)
{
var queryModel = new AccountQuery()
{
StartDate = startDate,
EndDate = endDate.HasValue ? endDate.Value.AddDays(1) : endDate,
AccountId = accountId,
PageSize = int.MaxValue,
PageNo = 1
};
ObsoletePageModel <AccountMetaModel> pageModelMetaInfo = _iAccountService.GetAccountMeta(queryModel);
var mode = pageModelMetaInfo.Models.ToList().Select(e => new AccountMetaModel
{
AccountId = e.Id,
Id = e.Id,
EndDate = e.EndDate,
StartDate = e.StartDate,
MetaKey = e.MetaKey,
MetaValue = e.MetaValue,
DateRange = e.StartDate.ToString("yyyy-MM-dd") + " 至 " + e.EndDate.ToString("yyyy-MM-dd")
}).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.Sheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.Row row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("类型");
row1.CreateCell(1).SetCellValue("营销类型");
row1.CreateCell(2).SetCellValue("费用");
row1.CreateCell(3).SetCellValue("服务周期");
sheet1.SetColumnWidth(0, 550 * 5);
sheet1.SetColumnWidth(1, 550 * 20);
sheet1.SetColumnWidth(2, 550 * 8);
sheet1.SetColumnWidth(3, 550 * 15);
//将数据逐步写入sheet1各个行
for (int i = 0; i < mode.Count(); i++)
{
NPOI.SS.UserModel.Row rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue("营销服务费");
rowtemp.CreateCell(1).SetCellValue(mode[i].MetaKey);
rowtemp.CreateCell(2).SetCellValue(mode[i].MetaValue);
rowtemp.CreateCell(3).SetCellValue(mode[i].DateRange);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "结算详情-营销服务费列表.xls"));
}
private static NPOI.SS.UserModel.Sheet GetSheet()
{
if (_sheet == null)
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(SAMPLE_SPREADSHEET_FILE_NAME);
_sheet = wb.GetSheetAt(0);
}
return(_sheet);
}
public void TestCloneSheetBasic()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = b.CreateSheet("Test");
s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1));
NPOI.SS.UserModel.Sheet clonedSheet = b.CloneSheet(0);
Assert.AreEqual(1, clonedSheet.NumMergedRegions, "One merged area");
}
private void ActivateSheet(NPOI.SS.UserModel.Sheet sheet)
{
DataTable dt = new DataTable(sheet.SheetName);
int maxCx = 0;
int cy = sheet.PhysicalNumberOfRows;
for (int y = 0; y < cy; y++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(y);
if (row != null)
{
int cx = row.PhysicalNumberOfCells;
maxCx = Math.Max(maxCx, row.FirstCellNum + cx);
}
}
int maxCy = sheet.FirstRowNum + cy;
for (int x = 0; x < maxCx; x++)
{
DataColumn col = dt.Columns.Add("C" + (1 + x), typeof(String));
}
for (int vy = 0; vy < maxCy; vy++)
{
DataRow dr = dt.NewRow();
if (vy >= sheet.FirstRowNum)
{
int y = vy - sheet.FirstRowNum;
NPOI.SS.UserModel.Row row = sheet.GetRow(y);
for (int vx = 0; vx < maxCx; vx++)
{
dr[vx] = "";
if (row != null)
{
if (vx >= row.FirstCellNum)
{
int x = vx - row.FirstCellNum;
NPOI.SS.UserModel.Cell cell = row.GetCell(x);
dr[vx] = (cell != null) ? cell.ToString() : "";
}
}
}
}
dt.Rows.Add(dr);
}
gv.DataSource = dt;
foreach (DataGridViewColumn col in gv.Columns)
{
col.ReadOnly = true;
}
gv.AutoResizeColumns();
gv.AutoResizeRows();
}
public void TestReadFormulaContainingExternalFunction()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("externalFunctionExample.xls");
String expectedFormula = "YEARFRAC(B1,C1)";
NPOI.SS.UserModel.Sheet sht = wb.GetSheetAt(0);
String cellFormula = sht.GetRow(0).GetCell(0).CellFormula;
Assert.AreEqual(expectedFormula, cellFormula);
}
private NPOI.SS.UserModel.Row GetRow(NPOI.SS.UserModel.Sheet sheet, int line)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(line);
if (row == null)
{
row = sheet.CreateRow(line);
}
return(row);
}
/// <summary>
/// Gets the row height in points.
/// </summary>
/// <param name="sheet">The sheet.</param>
/// <param name="rowNum">The row num.</param>
/// <returns></returns>
private float GetRowHeightInPoints(NPOI.SS.UserModel.Sheet sheet, int rowNum)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(rowNum);
if (row == null)
{
return(sheet.DefaultRowHeightInPoints);
}
else
{
return(row.HeightInPoints);
}
}
public void TestReading()
{
HSSFWorkbook workbook = LoadWorkbook("PercentPtg.xls");
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
Assert.AreEqual(53000.0,
sheet.GetRow(0).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(5300.0,
sheet.GetRow(1).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for percent formula result");
Assert.AreEqual("A1*10%",
sheet.GetRow(1).GetCell((short)0).CellFormula, "Wrong formula string for percent formula");
}
private NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Sheet sheet, int line, int col)
{
NPOI.SS.UserModel.Row row = GetRow(sheet, line);
NPOI.SS.UserModel.Cell cell = row.GetCell(col);
if (cell == null)
{
cell = row.CreateCell(col);
}
return(cell);
}
private void CreateUser(NPOI.SS.UserModel.Workbook wb, string user, Business.Boloes.Support.Bolao bolao)
{
NPOI.SS.UserModel.Sheet newSheet = wb.CloneSheet(0);
Business.Boloes.Support.JogoUsuario jogo = new Business.Boloes.Support.JogoUsuario(_currentLogin);
jogo.Bolao = bolao;
IList <Framework.DataServices.Model.EntityBaseData> jogos =
jogo.SelectAllByPeriod(bolao, user, 0, DateTime.MinValue, DateTime.MinValue, null, null, null, null);
foreach (Model.Boloes.JogoUsuario jogoUser in jogos)
{
}
}
public void TestPageBreakClones()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = b.CreateSheet("Test");
s.SetRowBreak(3);
s.SetColumnBreak((short)6);
NPOI.SS.UserModel.Sheet clone = b.CloneSheet(0);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken");
Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken");
s.RemoveRowBreak(3);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken");
}
public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("new sheet");
NPOI.SS.UserModel.Row row1 = sheet.CreateRow(0);
this.cell11 = row1.CreateCell(0);
this.cell12 = row1.CreateCell(1);
this.cell13 = row1.CreateCell(2);
this.cell14 = row1.CreateCell(3);
this.cell15 = row1.CreateCell(4);
this.cell16 = row1.CreateCell(5);
this.evaluator = new HSSFFormulaEvaluator(sheet, wb);
//this.evaluator.SetCurrentRow(row1);
}
public void TestReading()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("ReferencePtg.xls");
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
// First row
Assert.AreEqual(55.0,
sheet.GetRow(0).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(55.0,
sheet.GetRow(0).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A1",
sheet.GetRow(0).GetCell((short)1).CellFormula, "Wrong formula string for reference");
// Now moving over the 2**15 boundary
// (Remember that excel row (n) is1 poi row (n-1)
Assert.AreEqual(32767.0,
sheet.GetRow(32766).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32767.0,
sheet.GetRow(32766).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32767",
sheet.GetRow(32766).GetCell((short)1).CellFormula, "Wrong formula string for reference");
Assert.AreEqual(32768.0,
sheet.GetRow(32767).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32768.0,
sheet.GetRow(32767).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32768",
sheet.GetRow(32767).GetCell((short)1).CellFormula, "Wrong formula string for reference");
Assert.AreEqual(32769.0,
sheet.GetRow(32768).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32769.0,
sheet.GetRow(32768).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32769",
sheet.GetRow(32768).GetCell((short)1).CellFormula, "Wrong formula string for reference");
Assert.AreEqual(32770.0,
sheet.GetRow(32769).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32770.0,
sheet.GetRow(32769).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32770",
sheet.GetRow(32769).GetCell((short)1).CellFormula, "Wrong formula string for reference");
}
public void TestAnchorHeightInPoints()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet();
HSSFClientAnchor[] anchor =
{
new HSSFClientAnchor(0, 0, 0, 0, (short)0, 1, (short)1, 3),
new HSSFClientAnchor(0, 254, 0, 126, (short)0, 1, (short)1, 3),
new HSSFClientAnchor(0, 128, 0, 128, (short)0, 1, (short)1, 3),
new HSSFClientAnchor(0, 0, 0, 128, (short)0, 1, (short)1, 3),
};
float[] ref1 = { 24.0f, 18.0f, 24.0f, 30.0f };
for (int i = 0; i < anchor.Length; i++)
{
float height = anchor[i].GetAnchorHeightInPoints(sheet);
Assert.AreEqual(ref1[i], height, 0);
}
}
public void TestCaseInsensitiveNames()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet originalSheet = b.CreateSheet("Sheet1");
NPOI.SS.UserModel.Sheet fetchedSheet = b.GetSheet("sheet1");
if (fetchedSheet == null)
{
throw new AssertFailedException("Identified bug 44892");
}
Assert.AreEqual(originalSheet, fetchedSheet);
try
{
b.CreateSheet("sHeeT1");
Assert.Fail("should have thrown exceptiuon due to duplicate sheet name");
}
catch (ArgumentException e)
{
// expected during successful Test
Assert.AreEqual("The workbook already contains a sheet of this name", e.Message);
}
}
private static void ConfirmActiveSelected(NPOI.SS.UserModel.Sheet sheet, bool expected)
{
ConfirmActiveSelected(sheet, expected, expected);
}
public FileResult ExportExcel(int status, string shopName)
{
var queryModel = new AccountQuery()
{
Status = (Model.AccountInfo.AccountStatus?)status,
ShopName = shopName,
PageSize = int.MaxValue,
PageNo = 1
};
ObsoletePageModel <AccountInfo> accounts = _iAccountService.GetAccounts(queryModel);
IList <AccountModel> models = new List <AccountModel>();
foreach (var item in accounts.Models.ToArray())
{
AccountModel model = new AccountModel();
model.Id = item.Id;
model.ShopId = item.ShopId;
model.ShopName = item.ShopName;
model.AccountDate = item.AccountDate.ToString();
model.StartDate = item.StartDate;
model.EndDate = item.EndDate;
model.Status = (int)item.Status;
model.ProductActualPaidAmount = item.ProductActualPaidAmount;
model.FreightAmount = item.FreightAmount;
model.CommissionAmount = item.CommissionAmount;
model.RefundAmount = item.RefundAmount;
model.RefundCommissionAmount = item.RefundCommissionAmount;
model.BrokerageAmount = item.Brokerage;
model.ReturnBrokerageAmount = item.ReturnBrokerage;
model.AdvancePaymentAmount = item.AdvancePaymentAmount;
model.PeriodSettlement = item.PeriodSettlement;
model.Remark = item.Remark;
model.TimeSlot = string.Format("{0} 至 {1}", model.StartDate.Date.ToString("yyyy-MM-dd"), model.EndDate.Date.ToString("yyyy-MM-dd"));
models.Add(model);
}
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.Sheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.Row row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("诊所名称");
row1.CreateCell(1).SetCellValue("时间段");
row1.CreateCell(2).SetCellValue("诊疗项目实付总额");
row1.CreateCell(3).SetCellValue("运费");
row1.CreateCell(4).SetCellValue("佣金");
row1.CreateCell(5).SetCellValue("退款金额");
row1.CreateCell(6).SetCellValue("退还佣金");
row1.CreateCell(7).SetCellValue("分佣佣金");
row1.CreateCell(8).SetCellValue("退还分佣佣金");
row1.CreateCell(9).SetCellValue("营销费用总额");
row1.CreateCell(10).SetCellValue("本期应结");
row1.CreateCell(11).SetCellValue("出账日期");
//将数据逐步写入sheet1各个行
for (int i = 0; i < models.Count; i++)
{
NPOI.SS.UserModel.Row rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(models[i].ShopName);
rowtemp.CreateCell(1).SetCellValue(models[i].TimeSlot);
rowtemp.CreateCell(2).SetCellValue(models[i].ProductActualPaidAmount.ToString());
rowtemp.CreateCell(3).SetCellValue(models[i].FreightAmount.ToString());
rowtemp.CreateCell(4).SetCellValue(models[i].CommissionAmount.ToString());
rowtemp.CreateCell(5).SetCellValue(models[i].RefundAmount.ToString());
rowtemp.CreateCell(6).SetCellValue(models[i].RefundCommissionAmount.ToString());
rowtemp.CreateCell(7).SetCellValue(models[i].BrokerageAmount.ToString());
rowtemp.CreateCell(8).SetCellValue(models[i].ReturnBrokerageAmount.ToString());
rowtemp.CreateCell(9).SetCellValue(models[i].AdvancePaymentAmount.ToString());
rowtemp.CreateCell(10).SetCellValue(models[i].PeriodSettlement.ToString());
rowtemp.CreateCell(11).SetCellValue(models[i].AccountDate.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "结算管理.xls"));
}
public void CreateFile(Model.Campeonatos.Campeonato campeonato, Model.Boloes.Bolao bolao)
{
if (System.IO.File.Exists(_fileName))
{
System.IO.File.Delete(_fileName);
}
//FileStream stream = new FileStream(_fileName, FileMode.Create, FileAccess.Write);
//NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
NPOI.SS.UserModel.Workbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheetJogos = wb.CreateSheet("Jogos-Grupo");
Business.Campeonatos.Support.Campeonato camp = new Business.Campeonatos.Support.Campeonato(_currentLogin, campeonato);
IList <Framework.DataServices.Model.EntityBaseData> jogos = camp.LoadJogos(0, null, null, DateTime.MinValue, DateTime.MinValue, null);
IList <Framework.DataServices.Model.EntityBaseData> grupos = camp.LoadGrupos();
int count = 0;
int line = 0;
foreach (Model.Campeonatos.Grupo grupo in grupos)
{
if (!string.IsNullOrEmpty(grupo.Nome.Trim()))
{
NPOI.SS.UserModel.Row row = sheetJogos.CreateRow(count++);
NPOI.SS.UserModel.Cell grupoCell = row.CreateCell(0);
grupoCell.SetCellValue("Grupo " + grupo.Nome);
line = 0;
row = sheetJogos.CreateRow(count++);
row.CreateCell(line++).SetCellValue("Jogo");
row.CreateCell(line++).SetCellValue("Data/Hora");
row.CreateCell(line++).SetCellValue("Local");
row.CreateCell(line++).SetCellValue("Time");
row.CreateCell(line++).SetCellValue("Gols");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("x");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("Gols");
row.CreateCell(line++).SetCellValue("Time");
row.CreateCell(line++).SetCellValue("");
foreach (Model.Campeonatos.Jogo jogo in jogos)
{
if (string.Compare(jogo.Grupo.Nome, grupo.Nome, true) == 0)
{
line = 0;
row = sheetJogos.CreateRow(count++);
row.CreateCell(line++).SetCellValue(jogo.JogoLabel);
row.CreateCell(line++).SetCellValue(jogo.DataJogo.ToString("dd/MM/yy HH:mm"));
row.CreateCell(line++).SetCellValue(jogo.Estadio.ToString());
row.CreateCell(line++).SetCellValue(jogo.Time1.Nome);
if (jogo.PartidaValida)
{
row.CreateCell(line++).SetCellValue(jogo.GolsTime1);
}
else
{
row.CreateCell(line++).SetCellValue("");
}
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("x");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
if (jogo.PartidaValida)
{
row.CreateCell(line++).SetCellValue(jogo.GolsTime2);
}
else
{
row.CreateCell(line++).SetCellValue("");
}
row.CreateCell(line++).SetCellValue(jogo.Time2.Nome);
row.CreateCell(line++).SetCellValue("");
}
}
sheetJogos.CreateRow(count++);
}
}
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Oitavas de Final", ref count, jogos);
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Quartas de Final", ref count, jogos);
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Semi Finais", ref count, jogos);
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Final", ref count, jogos);
Business.Boloes.Support.Bolao bolaoBO = new Business.Boloes.Support.Bolao(_currentLogin, bolao.Nome);
IList <Framework.DataServices.Model.EntityBaseData> users = bolaoBO.LoadMembros();
foreach (Framework.Security.Model.UserData user in users)
{
CreateUser(wb, user.UserName, bolaoBO);
}
wb.Write(new FileStream(_fileName, FileMode.Create));
}
public FileResult DetailListExportExcel(long accountId, int enumOrderTypeId, DateTime?startDate, DateTime?endDate)
{
var queryModel = new AccountQuery()
{
StartDate = startDate,
EndDate = endDate.HasValue ? endDate.Value.AddDays(1) : endDate,
AccountId = accountId,
PageSize = int.MaxValue,
EnumOrderType = (AccountDetailInfo.EnumOrderType)enumOrderTypeId,
PageNo = 1
};
var accountDetails = _iAccountService.GetAccountDetails(queryModel);
var accountDetailsModel = (from p in accountDetails.Models.ToList()
select new
{
p.Id,
p.OrderType,
OrderTypeDescription = p.OrderType.ToDescription(),
p.OrderId,
p.ProductActualPaidAmount,
p.FreightAmount,
p.CommissionAmount,
p.RefundCommisAmount,
p.RefundTotalAmount,
Date = p.Date.ToString(),
OrderDate = p.OrderDate.ToString(),
p.OrderRefundsDates
}).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.Sheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
string titleFlag = string.Empty;
NPOI.SS.UserModel.Row row1 = sheet1.CreateRow(0);
if (enumOrderTypeId == 1)
{
titleFlag = "预约单列表";
row1.CreateCell(0).SetCellValue("类型");
row1.CreateCell(1).SetCellValue("预约单编号");
row1.CreateCell(2).SetCellValue("诊疗项目实付金额");
row1.CreateCell(3).SetCellValue("运费");
row1.CreateCell(4).SetCellValue("佣金");
row1.CreateCell(5).SetCellValue("下单日期");
row1.CreateCell(6).SetCellValue("成交日期");
sheet1.SetColumnWidth(0, 550 * 5);
sheet1.SetColumnWidth(1, 550 * 20);
sheet1.SetColumnWidth(2, 550 * 8);
sheet1.SetColumnWidth(3, 550 * 5);
sheet1.SetColumnWidth(4, 550 * 5);
sheet1.SetColumnWidth(5, 550 * 15);
sheet1.SetColumnWidth(6, 550 * 15);
}
else if (enumOrderTypeId == 0)
{
titleFlag = "退单列表";
row1.CreateCell(0).SetCellValue("类型");
row1.CreateCell(1).SetCellValue("预约单编号");
row1.CreateCell(2).SetCellValue("诊疗项目实付金额");
row1.CreateCell(3).SetCellValue("运费");
row1.CreateCell(4).SetCellValue("退款金额");
row1.CreateCell(5).SetCellValue("退还佣金");
row1.CreateCell(6).SetCellValue("退单日期");
sheet1.SetColumnWidth(0, 550 * 5);
sheet1.SetColumnWidth(1, 550 * 20);
sheet1.SetColumnWidth(2, 550 * 8);
sheet1.SetColumnWidth(3, 550 * 5);
sheet1.SetColumnWidth(4, 550 * 8);
sheet1.SetColumnWidth(5, 550 * 5);
sheet1.SetColumnWidth(6, 550 * 15);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < accountDetailsModel.Count(); i++)
{
NPOI.SS.UserModel.Row rowtemp = sheet1.CreateRow(i + 1);
if (enumOrderTypeId == 1)
{
rowtemp.CreateCell(0).SetCellValue(titleFlag);
rowtemp.CreateCell(1).SetCellValue(accountDetailsModel[i].OrderId.ToString());
rowtemp.CreateCell(2).SetCellValue(accountDetailsModel[i].ProductActualPaidAmount.ToString());
rowtemp.CreateCell(3).SetCellValue(accountDetailsModel[i].FreightAmount.ToString());
rowtemp.CreateCell(4).SetCellValue(accountDetailsModel[i].CommissionAmount.ToString());
rowtemp.CreateCell(5).SetCellValue(accountDetailsModel[i].OrderDate.ToString());
rowtemp.CreateCell(6).SetCellValue(accountDetailsModel[i].Date.ToString());
}
else if (enumOrderTypeId == 0)
{
rowtemp.CreateCell(0).SetCellValue(titleFlag);
rowtemp.CreateCell(1).SetCellValue(accountDetailsModel[i].OrderId.ToString());
rowtemp.CreateCell(2).SetCellValue(accountDetailsModel[i].ProductActualPaidAmount.ToString());
rowtemp.CreateCell(3).SetCellValue(accountDetailsModel[i].FreightAmount.ToString());
rowtemp.CreateCell(4).SetCellValue(accountDetailsModel[i].RefundTotalAmount.ToString());
rowtemp.CreateCell(5).SetCellValue(accountDetailsModel[i].RefundCommisAmount.ToString());
rowtemp.CreateCell(6).SetCellValue(accountDetailsModel[i].OrderRefundsDates.ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", string.Format("结算详情-{0}.xls", titleFlag)));
}
private void SetValue(NPOI.SS.UserModel.Sheet sheet, int line, int col, int value)
{
NPOI.SS.UserModel.Cell cell = GetCell(sheet, line, col);
cell.SetCellValue(value);
}
public void TestBackupRecord()
{
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < 10; i++)
{
HSSFSheet s = (HSSFSheet)wb.CreateSheet("Sheet " + i);
Sheet sheet = s.Sheet;
}
// Check the initial order
Assert.AreEqual(0, wb.GetSheetIndex("Sheet 0"));
Assert.AreEqual(1, wb.GetSheetIndex("Sheet 1"));
Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wb.GetSheetIndex("Sheet 3"));
Assert.AreEqual(4, wb.GetSheetIndex("Sheet 4"));
Assert.AreEqual(5, wb.GetSheetIndex("Sheet 5"));
Assert.AreEqual(6, wb.GetSheetIndex("Sheet 6"));
Assert.AreEqual(7, wb.GetSheetIndex("Sheet 7"));
Assert.AreEqual(8, wb.GetSheetIndex("Sheet 8"));
Assert.AreEqual(9, wb.GetSheetIndex("Sheet 9"));
// Change
wb.Workbook.SetSheetOrder("Sheet 6", 0);
wb.Workbook.SetSheetOrder("Sheet 3", 7);
wb.Workbook.SetSheetOrder("Sheet 1", 9);
// Check they're currently right
Assert.AreEqual(0, wb.GetSheetIndex("Sheet 6"));
Assert.AreEqual(1, wb.GetSheetIndex("Sheet 0"));
Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wb.GetSheetIndex("Sheet 4"));
Assert.AreEqual(4, wb.GetSheetIndex("Sheet 5"));
Assert.AreEqual(5, wb.GetSheetIndex("Sheet 7"));
Assert.AreEqual(6, wb.GetSheetIndex("Sheet 3"));
Assert.AreEqual(7, wb.GetSheetIndex("Sheet 8"));
Assert.AreEqual(8, wb.GetSheetIndex("Sheet 9"));
Assert.AreEqual(9, wb.GetSheetIndex("Sheet 1"));
// Read it in and see if it is correct.
MemoryStream baos = new MemoryStream();
wb.Write(baos);
MemoryStream bais = new MemoryStream(baos.ToArray());
HSSFWorkbook wbr = new HSSFWorkbook(bais);
Assert.AreEqual(0, wbr.GetSheetIndex("Sheet 6"));
Assert.AreEqual(1, wbr.GetSheetIndex("Sheet 0"));
Assert.AreEqual(2, wbr.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wbr.GetSheetIndex("Sheet 4"));
Assert.AreEqual(4, wbr.GetSheetIndex("Sheet 5"));
Assert.AreEqual(5, wbr.GetSheetIndex("Sheet 7"));
Assert.AreEqual(6, wbr.GetSheetIndex("Sheet 3"));
Assert.AreEqual(7, wbr.GetSheetIndex("Sheet 8"));
Assert.AreEqual(8, wbr.GetSheetIndex("Sheet 9"));
Assert.AreEqual(9, wbr.GetSheetIndex("Sheet 1"));
// Now get the index by the sheet, not the name
for (int i = 0; i < 10; i++)
{
NPOI.SS.UserModel.Sheet s = wbr.GetSheetAt(i);
Assert.AreEqual(i, wbr.GetSheetIndex(s));
}
}
private static String GetFormulaFromFirstCell(NPOI.SS.UserModel.Sheet s, int rowIx)
{
return(s.GetRow(rowIx).GetCell((short)0).CellFormula);
}
public void TestActiveSheetAfterDelete_bug40414()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet0 = wb.CreateSheet("Sheet0");
NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet("Sheet1");
NPOI.SS.UserModel.Sheet sheet2 = wb.CreateSheet("Sheet2");
NPOI.SS.UserModel.Sheet sheet3 = wb.CreateSheet("Sheet3");
NPOI.SS.UserModel.Sheet sheet4 = wb.CreateSheet("Sheet4");
// Confirm default activation/selection
ConfirmActiveSelected(sheet0, true);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.ActiveSheetIndex = (3);
wb.SetSelectedTab(3);
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, true);
ConfirmActiveSelected(sheet4, false);
wb.RemoveSheetAt(3);
// after removing the only active/selected sheet, another should be active/selected in its place
if (!sheet4.IsSelected)
{
throw new AssertFailedException("identified bug 40414 a");
}
if (!sheet4.IsActive)
{
throw new AssertFailedException("identified bug 40414 b");
}
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet4, true);
sheet3 = sheet4; // re-align local vars in this Test case
// Some more cases of removing sheets
// Starting with a multiple selection, and different active sheet
wb.SetSelectedTabs(new int[] { 1, 3, });
wb.ActiveSheetIndex = (2);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
ConfirmActiveSelected(sheet3, false, true);
// removing a sheet that is not active, and not the only selected sheet
wb.RemoveSheetAt(3);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
// removing the only selected sheet
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet2, true, true);
// The last remaining sheet should always be active+selected
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, true, true);
}
private static void ConfirmActiveSelected(NPOI.SS.UserModel.Sheet sheet,
bool expectedActive, bool expectedSelected)
{
Assert.AreEqual(expectedActive, sheet.IsActive, "active");
Assert.AreEqual(expectedSelected, sheet.IsSelected, "selected");
}
