本文整理汇总了C#中NPOI.XSSF.UserModel.XSSFWorkbook.GetSheet方法的典型用法代码示例。如果您正苦于以下问题:C# XSSFWorkbook.GetSheet方法的具体用法?C# XSSFWorkbook.GetSheet怎么用?C# XSSFWorkbook.GetSheet使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类NPOI.XSSF.UserModel.XSSFWorkbook
的用法示例。
在下文中一共展示了XSSFWorkbook.GetSheet方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: XLSXPlugin
public XLSXPlugin(string contentFormat, string contentEncoding, string currentFile, string currentWorkingDirectory)
{
this.contentFormat = new ContentType(contentFormat, contentEncoding);
this.currentFile = currentFile;
fileInput = new FileStream(Path.Combine(currentWorkingDirectory, this.currentFile), FileMode.Open, FileAccess.Read);
workBook = new XSSFWorkbook(this.fileInput);
currentPageName = currentFile.Substring(this.currentFile.LastIndexOf("/") + 1, this.currentFile.LastIndexOf("."));
this.currentWorkingDirectory = currentWorkingDirectory;
sheet = workBook.GetSheet("Sheet1");
cmsBlockFactory = new CMSBlockFactory(currentPageName);
processDataRows();
}
示例2: TestNoColsWithoutWidthWhenGroupingAndCollapsing
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)5);
sheet.SetColumnGroupCollapsed(4, true);
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 4; i <= 5; i++)
{
Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
cols = sheet.GetCTWorksheet().GetColsArray(0);
foreach (CT_Col col in cols.GetColArray())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
示例3: ReadYinTaiStoreExcel
public static void ReadYinTaiStoreExcel()
{
Stream excel=new FileStream(@"E:\工作资料\门店帐号信息完整版.xlsx",FileMode.Open);
IWorkbook workbook = new XSSFWorkbook(excel);
ISheet sheet = workbook.GetSheet("门店信息");
var sb = new StringBuilder();
for (int i = sheet.FirstRowNum+1; i < sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
int j = row.FirstCellNum+3;
sb.AppendFormat(
"INSERT INTO [YintaiEvent].[dbo].[Store]([StoreId],[City],[StoreName],[CompanyFullName],[Address],[OfficeAddress],[Phone],[Bank1],[BankAccount1],[Bank2],[BankAccount2],[InUserId],[EditUserId],[InDate],[EditDate])VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',{13},{14})", i, row.GetCell(j-2),row.GetCell(j-1), row.GetCell(j), row.GetCell(j + 1), row.GetCell(j + 2), row.GetCell(j + 3), row.GetCell(j + 4), row.GetCell(j + 5), row.GetCell(j + 6), row.GetCell(j + 7), "0", "0", "GetDate()", "GetDate()");
sb.Append("\n");
}
var str = sb.ToString();
}
示例4: Start
// Use this for initialization
void Start ()
{
Debug.Log(Application.dataPath + "/Resources/Data/Test.xlsx");
if(File.Exists(Application.dataPath + "/Resources/Data/Test.xlsx"))
{
Debug.Log("OK");
}
else
{
Debug.Log("No file");
}
XSSFWorkbook workbook = new XSSFWorkbook(Application.dataPath+ "/Resources/Data/Test.xlsx");
ISheet testSheet = workbook.GetSheet("Test");
IEnumerator iter = testSheet.GetRowEnumerator();
while (iter.MoveNext())
{
IRow row = iter.Current as IRow;
string line = "";
foreach(ICell cell in row.Cells)
{
switch (cell.CellType)
{
case CellType.String:
line += cell.StringCellValue;
break;
case CellType.Numeric:
line += cell.NumericCellValue;
break;
case CellType.Boolean:
line += cell.BooleanCellValue;
break;
case CellType.Error:
line += cell.ErrorCellValue;
break;
}
line += "[" + cell.CellType + "]"+ (cell.CellComment!=null ? ("("+cell.CellComment.String.String+")"):"")+"\t";
}
Debug.Log(line);
}
}
示例5: abc
public void abc(String FilePath)
{
XSSFWorkbook hssfwb;
using (FileStream file = new FileStream(FilePath), FileMode.Open, FileAccess.Read))
{
hssfwb = new XSSFWorkbook(file);
}
ISheet sheet = hssfwb.GetSheet("Sheet1");
for (int row = 0; row <= sheet.LastRowNum+1; row++)
{
if (sheet.GetRow(row) != null)
{
示例6: TestNoColsWithoutWidthWhenGrouping
public void TestNoColsWithoutWidthWhenGrouping()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)7);
sheet.GroupColumn((short)9, (short)12);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGrouping");
sheet = (XSSFSheet)wb.GetSheet("test");
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186/cols:" + cols);
foreach (CT_Col col in cols.GetColArray())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
示例7: ExportHandleQuanExcel
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.HKHandleQuanDetail> hkHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号12 香港新马激励量化单(5.5日之后转案).xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("香港新马");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.GetRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚网申3");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(hkHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.HKHandleQuanDetail item in hkHandleQuanDetails.Values)
{
row = sheet.CreateRow(4 + i);
row.HeightInPoints = 25;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//翻译
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Translation);
//签证寄出
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Visa);
//寄出日期
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
//资深文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(10);
cell.CellStyle = style;
//.........这里部分代码省略.........
示例8: Import
public static bool Import(string TargetFolder)
{
bool Success = true;
string[] Files = Directory.GetFiles(TargetFolder);
List<string> ErrorList = new List<string>();
foreach (string CurrentFile in Files)
{
try
{
XSSFWorkbook MyWorkbook = null;
using (FileStream file = new FileStream(CurrentFile, FileMode.Open, FileAccess.Read))
{
MyWorkbook = new XSSFWorkbook(file);
// Read file values here
#region Biographical
//Notes: Did not make provisions for multiple Clinics, Schools or Grades - Client must confirm if it is necessary
string BioChowName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(0).StringCellValue;
string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(1).StringCellValue;
string BioDateOfScreen = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(2).StringCellValue;
string BioHeadOfHousehold = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(3).StringCellValue;
string BioName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(4).StringCellValue;
string BioSurname = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(5).StringCellValue;
string BioGPSLat = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(6).StringCellValue;
string BioGPSLong = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(7).StringCellValue;
string BioIDNum = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(8).StringCellValue;
string BioClinicUsed = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(9).StringCellValue;
string BioDateOfBirth = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(10).StringCellValue;
string BioMale = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(11).StringCellValue;
string BioFemale = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(12).StringCellValue;
string BioAttendingSchool = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(13).StringCellValue;
string BioSchoolName = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(14).StringCellValue;
string BioGrade = MyWorkbook.GetSheet("Biographical").GetRow(4).GetCell(15).StringCellValue;
#endregion
#region Environmental
//Notes: Did not make provisions for multiple Clinics, - Client must confirm if it is necessary
//Notes" Made provision for 5 huts in total
//Notes" Made provision for 2 water Supplies
string EnNoOfPeople = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(1).StringCellValue;
string EnNoLiveAway = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(2).StringCellValue;
string EnListWhere0 = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(3).StringCellValue;
string EnListWhere1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(3).StringCellValue;
string EnListWhere2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(3).StringCellValue;
string EnListWhere3 = MyWorkbook.GetSheet("Environmental").GetRow(7).GetCell(3).StringCellValue;
string EnListWhere4 = MyWorkbook.GetSheet("Environmental").GetRow(8).GetCell(3).StringCellValue;
string EnFamilyLastVisit0 = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(4).StringCellValue;
string EnFamilyLastVisit1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(4).StringCellValue;
string EnFamilyLastVisit2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(4).StringCellValue;
string EnFamilyLastVisit3 = MyWorkbook.GetSheet("Environmental").GetRow(7).GetCell(4).StringCellValue;
string EnFamilyLastVisit4 = MyWorkbook.GetSheet("Environmental").GetRow(8).GetCell(4).StringCellValue;
string EnWhichClinic = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(5).StringCellValue;
string EnMainHutStructure = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(6).StringCellValue;
string EnMainHutTypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(7).StringCellValue;
string EnMainHutVentilation = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(8).StringCellValue;
string EnMainHutTotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(9).StringCellValue;
string EnHut2Structure = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(6).StringCellValue;
string EnHut2TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(7).StringCellValue;
string EnHut2Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(8).StringCellValue;
string EnHut2TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(9).GetCell(9).StringCellValue;
string EnHut3Structure = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(6).StringCellValue;
string EnHut3TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(7).StringCellValue;
string EnHut3Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(8).StringCellValue;
string EnHut3TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(13).GetCell(9).StringCellValue;
string EnHut4Structure = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(6).StringCellValue;
string EnHut4TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(7).StringCellValue;
string EnHut4Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(8).StringCellValue;
string EnHut4TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(18).GetCell(9).StringCellValue;
string EnHut5Structure = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(6).StringCellValue;
string EnHut5TypeRoof = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(7).StringCellValue;
string EnHut5Ventilation = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(8).StringCellValue;
string EnHut5TotalRooms = MyWorkbook.GetSheet("Environmental").GetRow(22).GetCell(9).StringCellValue;
string EnNoSleepingInOneRoom = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(10).StringCellValue;
string EnNoOfStructures = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(11).StringCellValue;
string EnRainWaterCollection = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(12).StringCellValue;
string EnWaterSupply = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(13).StringCellValue;
string EnWaterSupply1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(13).StringCellValue;
string EnWalkingDistanceWater = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(14).StringCellValue;
string EnTreatWater = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(15).StringCellValue;
string EnHutElectricity = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(16).StringCellValue;
string EnFridge = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(17).StringCellValue;
string EnUseForCooking = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(18).StringCellValue;
string EnUseForCooking1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(18).StringCellValue;
string EnUseForCooking2 = MyWorkbook.GetSheet("Environmental").GetRow(6).GetCell(18).StringCellValue;
string EnTypeToilet = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(19).StringCellValue;
string EnDisposeWaste = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(20).StringCellValue;
string EnDisposeWaste1 = MyWorkbook.GetSheet("Environmental").GetRow(5).GetCell(20).StringCellValue;
string EnSourceIncomeHousehold = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(21).StringCellValue;
string EnFoodParcel = MyWorkbook.GetSheet("Environmental").GetRow(4).GetCell(22).StringCellValue;
#endregion
#region General
string GenWeight = MyWorkbook.GetSheet("General").GetRow(4).GetCell(1).StringCellValue;
string GenHeight = MyWorkbook.GetSheet("General").GetRow(4).GetCell(2).StringCellValue;
string GenBMI = MyWorkbook.GetSheet("General").GetRow(4).GetCell(3).StringCellValue;
string GenCurrentOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(5).StringCellValue;
//.........这里部分代码省略.........
示例9: SaveTransactionsFromFile
/// <summary>
///
/// </summary>
/// <param name="filename"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public bool SaveTransactionsFromFile(string filename, string sheetName)
{
try
{
//Using Npoi instead of the office components
ISheet sheet;
if (filename.Substring(filename.LastIndexOf('.')).ToLower() == EXCEL_2007_EXTENSION)
{
XSSFWorkbook workbook;
using (var file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);
}
sheet = workbook.GetSheet(sheetName);
}
else
{
HSSFWorkbook workbook;
using (var file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);
}
sheet = workbook.GetSheet(sheetName);
}
var transactionsList = new List<dynamic>();
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
var theRow = sheet.GetRow(row);
var transaction = new
{
campana = theRow.GetCell(0).StringCellValue,
factura = theRow.GetCell(1).StringCellValue,
fecha = theRow.GetCell(2).DateCellValue,
monto = theRow.GetCell(3).NumericCellValue,
puntos = theRow.GetCell(4).NumericCellValue,
comision = theRow.GetCell(5).NumericCellValue,
vendedor = theRow.GetCell(6).StringCellValue,
};
transactionsList.Add(transaction);
}
}
//string connectionString =
// string.Format(
// filename.Substring(filename.LastIndexOf('.')).ToLower() == EXCEL_2007_EXTENSION
// ? EXCEL_2007_CONNECTION_STRING
// : EXCEL_2005_CONNECTION_STRING, filename);
//var adapter = new OleDbDataAdapter(string.Format(SELECT_ALL_QUERY, sheetName), connectionString);
//var dataSet = new DataSet();
//adapter.Fill(dataSet, DATA_TABLE_NAME);
//var reportData = dataSet.Tables[DATA_TABLE_NAME].AsEnumerable();
//var transactionsList =
// reportData.Where(y => y.Field<string>("Campaña") != null)
// .Select(
// x => new
// {
// campana = x.Field<string>("Campaña"),
// factura = x.Field<string>("Factura"),
// fecha = x.Field<DateTime>("Fecha"),
// monto = x.Field<double>("Monto"),
// puntos = x.Field<double>("Puntos"),
// comision = x.Field<double>("Comision"),
// vendedor = x.Field<string>("Vendedor"),
// }).
// ToList(); //o por nombre de columna.
foreach (var individualTransaction in transactionsList)
{
int cedNumber = GetCedNumberFromString(individualTransaction.vendedor);
var customer = _usersRepository.GetUserByIdentificationNumber(cedNumber);
var company = _companiesRepository.GetCompany(individualTransaction.campana);
var transaction = new Transaction
{
Amount = individualTransaction.monto,
BillBarCode = individualTransaction.factura,
UserId = customer.UserId,
Points = (int) individualTransaction.puntos,
TransactionDate = Convert.ToDateTime(individualTransaction.fecha),
CompanyId = company.CompanyId,
CreatetedAt = DateTime.Now,
UpdatedAt = DateTime.Now,
Comision = individualTransaction.comision,
};
if (_transactionsRepository.SaveTransaction(transaction))
{
if (!DistributeTransactionCashback(transaction))
//.........这里部分代码省略.........
示例10: DownloadAsExcel
public ActionResult DownloadAsExcel(string id)
{
if (string.IsNullOrWhiteSpace(id))
{
TempData["Message"] = "Unable to download file. No file was selected. Please select a file and try again.";
return RedirectToAction("Index");
}
try
{
var file = GetNamedFile(id);
var created = file.TimeStamp;
var filePathAndFilename = file.FilePath;
var filename = file.FileNameLessExtension;
var streamReader = new StreamReader(filePathAndFilename);
var engine = new FileHelperEngine<FeederSystemFixedLengthRecord>();
var result = engine.ReadStream(streamReader);
var transactions = result.ToList();
// Opening the Excel template...
var templateFileStream = new FileStream(Server.MapPath(@"~\Files\RevisedScrubberWithoutData.xlsx"),
FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
var templateWorkbook = new XSSFWorkbook(templateFileStream);
// Getting the worksheet by its name...
var sheet = templateWorkbook.GetSheet("Sheet1");
// We need this so the date will be formatted correctly; otherwise, the date format gets all messed up.
var dateCellStyle = templateWorkbook.CreateCellStyle();
var format = templateWorkbook.CreateDataFormat();
dateCellStyle.DataFormat = format.GetFormat("[$-809]m/d/yyyy;@");
// Here's another to ensure we get a number with 2 decimal places:
var twoDecimalPlacesCellStyle = templateWorkbook.CreateCellStyle();
format = templateWorkbook.CreateDataFormat();
twoDecimalPlacesCellStyle.DataFormat = format.GetFormat("#0.00");
var boldFont = templateWorkbook.CreateFont();
boldFont.FontHeightInPoints = 11;
boldFont.FontName = "Calibri";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
var boldCellStyle = templateWorkbook.CreateCellStyle();
boldCellStyle.SetFont(boldFont);
var boldTotalAmountStyle = templateWorkbook.CreateCellStyle();
boldTotalAmountStyle.DataFormat = twoDecimalPlacesCellStyle.DataFormat;
boldTotalAmountStyle.SetFont(boldFont);
var grandTotal = 0.0;
var i = 0;
foreach (var transaction in transactions)
{
i++;
// Getting the row... 0 is the first row.
var dataRow = sheet.GetRow(i);
dataRow.CreateCell(0).SetCellValue(transaction.FiscalYear);
dataRow.CreateCell(1).SetCellValue(transaction.ChartNum);
dataRow.CreateCell(2).SetCellValue(transaction.Account);
dataRow.CreateCell(3).SetCellValue(transaction.SubAccount);
dataRow.CreateCell(4).SetCellValue(transaction.ObjectCode);
dataRow.CreateCell(5).SetCellValue(transaction.SubObjectCode);
dataRow.CreateCell(6).SetCellValue(transaction.BalanceType);
dataRow.CreateCell(7).SetCellValue(transaction.ObjectType.Trim());
dataRow.CreateCell(8).SetCellValue(transaction.FiscalPeriod);
dataRow.CreateCell(9).SetCellValue(transaction.DocumentType);
dataRow.CreateCell(10).SetCellValue(transaction.OriginCode);
dataRow.CreateCell(11).SetCellValue(transaction.DocumentNumber);
dataRow.CreateCell(12).SetCellValue(transaction.LineSequenceNumber);
dataRow.CreateCell(13).SetCellValue(transaction.TransactionDescription);
var transactionAmount = Convert.ToDouble(transaction.Amount.Trim());
grandTotal += transactionAmount;
var cell = dataRow.CreateCell(14);
cell.CellStyle = twoDecimalPlacesCellStyle;
cell.SetCellValue(transactionAmount);
dataRow.CreateCell(15).SetCellValue(transaction.DebitCreditCode.Trim());
cell = dataRow.CreateCell(16);
cell.CellStyle = dateCellStyle;
cell.SetCellValue(Convert.ToDateTime(transaction.TransactionDate));
dataRow.CreateCell(17).SetCellValue(transaction.OrganizationTrackingNumber);
dataRow.CreateCell(18).SetCellValue(transaction.ProjectCode);
dataRow.CreateCell(19).SetCellValue(transaction.OrganizationReferenceId.Trim());
dataRow.CreateCell(20).SetCellValue(transaction.ReferenceTypeCode.Trim());
dataRow.CreateCell(21).SetCellValue(transaction.ReferenceOriginCode.Trim());
dataRow.CreateCell(22).SetCellValue(transaction.ReferenceNumber.Trim());
dataRow.CreateCell(23).SetCellValue(transaction.ReversalDate.Trim());
dataRow.CreateCell(24).SetCellValue(transaction.TransactionEncumbranceUpdateCode.Trim());
}
if (transactions.Any())
//.........这里部分代码省略.........
示例11: Initialize
public override void Initialize(DateTime Start, DateTime End, IEnumerable<Catchment> Catchments)
{
base.Initialize(Start, End, Catchments);
Dictionary<XYPoint, List<double>> Data = new Dictionary<XYPoint,List<double>>();
XSSFWorkbook hssfwb;
using (FileStream file = new FileStream(ExcelFile.FileName, FileMode.Open, FileAccess.Read))
{
hssfwb = new XSSFWorkbook(file);
}
List<IRow> DataRows = new List<IRow>();
var sheet = hssfwb.GetSheet("Ndep_Tot");
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
DataRows.Add(sheet.GetRow(row));
}
}
using (ShapeReader sr = new ShapeReader(Shapefile.FileName))
{
FirstYear = (int)DataRows.First().Cells[0].NumericCellValue;
for (int i = 0; i < sr.Data.NoOfEntries; i++)
{
int icoor = sr.Data.ReadInt(i, "i");
int jcoor = sr.Data.ReadInt(i, "j");
XYPoint point = (XYPoint)sr.ReadNext(i);
//Create the timestampseries and set unit to kg/m2/s;
var data = DataRows.Where(v => (int)v.Cells[3].NumericCellValue == icoor & (int)v.Cells[4].NumericCellValue == jcoor).OrderBy(v => (int)v.Cells[0].NumericCellValue).Select(v => v.Cells[6].NumericCellValue / (365.0 * 86400.0 * 1.0e6)).ToList();
if(data.Count()>0)
Data.Add(point, data);
}
}
foreach (var c in Catchments)
{
XYPolygon poly = null;
if (c.Geometry is XYPolygon)
poly = c.Geometry as XYPolygon;
else if (c.Geometry is MultiPartPolygon)
poly = ((MultiPartPolygon)c.Geometry).Polygons.First(); //Just use the first polygon
double LakeArea = c.Lakes.Sum(l => l.Geometry.GetArea()); //Get the area of the lakes
if (c.BigLake != null) //Add the big lake
LakeArea += c.BigLake.Geometry.GetArea();
if (poly != null)
{
var point = new XYPoint(poly.PlotPoints.First().Longitude, poly.PlotPoints.First().Latitude); //Take one point in the polygon
var closestpoint = Data.Keys.Select(p => new Tuple<XYPoint, double>(p, p.GetDistance(point))).OrderBy(s => s.Item2).First().Item1;
deposition.Add(c.ID, new List<double>(Data[closestpoint].Select(v=>v*LakeArea)));
}
}
NewMessage("Initialized");
}
示例12: Import
public static bool Import(string TargetFolder)
{
bool Success = true;
string[] Files = Directory.GetFiles(TargetFolder);
List<string> ErrorList = new List<string>();
foreach (string CurrentFile in Files)
{
try
{
XSSFWorkbook MyWorkbook = null;
using (FileStream file = new FileStream(CurrentFile, FileMode.Open, FileAccess.Read))
{
MyWorkbook = new XSSFWorkbook(file);
// Read file values here
#region Biographical
string BioChowName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(0).StringCellValue;
string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(1).StringCellValue;
string BioFirstName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(2).StringCellValue;
string BioSecondName = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(3).StringCellValue;
string BioIDNumber = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(4).StringCellValue;
string BioGPSLatitude = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(5).StringCellValue;
string BioGPSLongitude = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(6).StringCellValue;
string BioArea = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(7).StringCellValue;
string BioClinic = MyWorkbook.GetSheet("Biographical").GetRow(2).GetCell(8).StringCellValue;
#endregion
#region VisitDetails
// VD prefix used to identify variables associated with Visit Details tab
string VDVisitNum = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(0).StringCellValue;
string VDVisitDate = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(1).StringCellValue;
string VDNextVisitDate = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(2).StringCellValue;
string VDOutcome = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(4).StringCellValue;
string VDHPT = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(5).StringCellValue;
string VDDiabetes = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(6).StringCellValue;
string VDEpilepsy = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(7).StringCellValue;
string VDHIV = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(8).StringCellValue;
string VDTB = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(9).StringCellValue;
string VDMatHealth = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(10).StringCellValue;
string VDChildHealth = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(11).StringCellValue;
string VDOther = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(12).StringCellValue;
string VDODoorToDoor = MyWorkbook.GetSheet("Visit details").GetRow(2).GetCell(13).StringCellValue;
#endregion
#region HyperTension
//Hyper prefix used to identify variables associated with Hypertension tab.
//HiEHRef = HiEH Referral
//ClinicRef = Clinic Referral
//AOT = Already On Treatment
string HyperDateOfVisit = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(0).StringCellValue;
string Hyper_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(1).StringCellValue;
string Hyper_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(2).StringCellValue;
string Hyper_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(3).StringCellValue;
string Hyper_HiEHRef_OnMeds = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(4).StringCellValue;
string Hyper_HiEHRef_StartDate = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(5).StringCellValue;
string Hyper_HiEHRef_BPReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(6).StringCellValue;
string Hyper_HiEHRef_TodayReadingTop = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(7).StringCellValue;
string Hyper_HiEHRef_TodayReadingBottom = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(8).StringCellValue;
string Hyper_HiEHRef_ReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(9).StringCellValue;
string Hyper_HiEHRef_RefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(10).StringCellValue;
string Hyper_ClinicRef_ReReferToClinic = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(12).StringCellValue;
string Hyper_ClinicRef_ReRefNum = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(13).StringCellValue;
string Hyper_AOT_FollowUpReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(15).StringCellValue;
string Hyper_DoorToDoorReading_CheckReading = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(17).StringCellValue;
string Hyper_Medication = MyWorkbook.GetSheet("Hypertension").GetRow(5).GetCell(18).StringCellValue;
#endregion
#region Diabetes
//HiEHRef = HiEH Referral
//ClinicRef = Clinic Referral
//AOT = Already On Treatment
string DiabetesDateOfVisit = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(0).StringCellValue;
string Diabetes_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(1).StringCellValue;
string Diabetes_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(2).StringCellValue;
string Diabetes_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(3).StringCellValue;
string Diabetes_HiEHRef_OnMeds = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(4).StringCellValue;
string Diabetes_HiEHRef_StartDate = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(5).StringCellValue;
string Diabetes_HiEHRef_FollowUpTestReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(6).StringCellValue;
string Diabetes_HiEHRef_ReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(7).StringCellValue;
string Diabetes_HiEHRef_RefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(8).StringCellValue;
string Diabetes_ClinicRef_ReReferToClinic = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(10).StringCellValue;
string Diabetes_ClinicRef_ReRefNum = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(11).StringCellValue;
string Diabetes_AOT_FollowUpReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(13).StringCellValue;
string Diabetes_DoorToDoorReading_CheckReading = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(15).StringCellValue;
//.........这里部分代码省略.........
示例13: ExportExcel
public string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleNumDTO> hnDTOList, string fileName)
{
if (hnDTOList == null)
{
return string.Empty;
}
string url = "http://blessing.wang/ExcelModel/numModel.xlsx";
WebRequest request = WebRequest.Create(url);
WebResponse response = request.GetResponse();
Stream stream = response.GetResponseStream();
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("寄出个数");
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(1);
cell.SetCellValue(hnDTOList.Count + "个");
row = sheet.GetRow(2);
cell = row.GetCell(7);
cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
//设置单元格格式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int i = 0; i < hnDTOList.Count; i++)
{
row = sheet.CreateRow(5 + i);
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].ContractNo);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Student);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Application);
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Visa);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Author);
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].FirstFour);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Doctor);
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Cooperation);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Note);
}
style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(5 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("编制人签名:");
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue("经理审核签名:");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("总经理/分管总裁批准签字:");
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue("保持:人力资源中心");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1.《文案工作进度(量化)日报单》汇总动态记录PM-16《文案工作量化日报表》;此单必须机打,手写量化将直接作废;");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(7 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2.本报单请于16:00之前申报, □确认√确认填报项。请勿集中中延期申报");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
//.........这里部分代码省略.........
示例14: button2_Click
private void button2_Click(object sender, EventArgs e)
{
string ruta = this.buscarDoc();
ConexionPostgres conn = new ConexionPostgres();
FileStream fs;
try
{
fs = new FileStream(ruta, FileMode.Open, FileAccess.Read);
}
catch
{
MessageBox.Show("El archivo está siendo usado por otro programa. Asegurese de haber seleccionado el archivo correcto.");
return;
}
XSSFWorkbook wb = new XSSFWorkbook(fs);
string nombreHoja = "";
for (int i = 0; i < wb.Count; i++)
{
nombreHoja = wb.GetSheetAt(i).SheetName;
}
XSSFSheet sheet = (XSSFSheet)wb.GetSheet(nombreHoja);
try
{
for (int row = 1; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
var fila = sheet.GetRow(row);
var nit = fila.GetCell(0).ToString();
var numero_unidad = fila.GetCell(1).ToString();
var nombre_completo = fila.GetCell(2).ToString();
var coeficiente = fila.GetCell(3).ToString().Replace(",", ".");
var documento = fila.GetCell(4).ToString();
var cadenaSql = string.Format(
@"INSERT INTO modelo.unidad_residencial
(
nit,
numero_unidad,
nombre_completo,
coeficiente,
documento
)
VALUES
(
'{0}',
'{1}',
'{2}',
'{3}',
'{4}'
);",
nit,
numero_unidad,
nombre_completo,
coeficiente,
documento
);
var resultado = conn.registrar(cadenaSql);
if (!resultado)//Falló la consulta
{
MessageBox.Show("El registro NIT:" + nit
+ ", Número Unidad:" + numero_unidad
+ ", Nombre Completo:" + nombre_completo
+ ", Coeficiente:" + coeficiente
+ ", Número Documento:" + documento
+ " ,no pudo ser completado de manera exitosa, revise los datos (están mal o ya existen o se encontro elemento en blanco).");
MessageBox.Show(cadenaSql);
return;
}
}
else
{
MessageBox.Show("realizado");
}
}
MessageBox.Show("Datos cargados correctamente.");
}
catch
{
MessageBox.Show("Algo pasó con la carga de archivos, asegúrese de que ha cargado el archivo correcto con datos válidos.");
}
//Se cierra el archivo
fs.Close();
}
示例15: SetUp
public void SetUp()
{
if (workbook == null)
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(SS.FILENAME);
OPCPackage pkg = OPCPackage.Open(is1);
workbook = new XSSFWorkbook(pkg);
sheet = workbook.GetSheet(SS.TEST_SHEET_NAME);
}
_functionFailureCount = 0;
_functionSuccessCount = 0;
_EvaluationFailureCount = 0;
_EvaluationSuccessCount = 0;
}