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(); }
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()); } }
public void TestBug56644CreateBlank() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56644.xlsx"); try { wb.MissingCellPolicy = (MissingCellPolicy.CREATE_NULL_AS_BLANK); ISheet sheet = wb.GetSheet("samplelist"); IRow row = sheet.GetRow(20); row.CreateCell(2); } finally { wb.Close(); } }
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(); }
// 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); } }
public void TestMergingOverlappingCols_OVERLAPS_2_MINOR() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test"); CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0); CT_Col col = cols.AddNewCol(); col.min = (2 + 1); col.max = (4 + 1); col.width = (20); col.customWidth = (true); sheet.GroupColumn((short)1, (short)3); cols = sheet.GetCTWorksheet().GetColsArray(0); //logger.log(POILogger.DEBUG, "testMergingOverlappingCols_OVERLAPS_2_MINOR/cols:" + cols); Assert.AreEqual(1, cols.GetColArray(0).outlineLevel); Assert.AreEqual(2, cols.GetColArray(0).min); // 1 based Assert.AreEqual(2, cols.GetColArray(0).max); // 1 based Assert.AreEqual(false, cols.GetColArray(0).customWidth); Assert.AreEqual(1, cols.GetColArray(1).outlineLevel); Assert.AreEqual(3, cols.GetColArray(1).min); // 1 based Assert.AreEqual(4, cols.GetColArray(1).max); // 1 based Assert.AreEqual(true, cols.GetColArray(1).customWidth); Assert.AreEqual(0, cols.GetColArray(2).outlineLevel); Assert.AreEqual(5, cols.GetColArray(2).min); // 1 based Assert.AreEqual(5, cols.GetColArray(2).max); // 1 based Assert.AreEqual(true, cols.GetColArray(2).customWidth); Assert.AreEqual(3, cols.sizeOfColArray()); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testMergingOverlappingCols_OVERLAPS_2_MINOR"); sheet = (XSSFSheet)wb.GetSheet("test"); for (int i = 2; i <= 4; i++) { Assert.AreEqual(20 * 256, sheet.GetColumnWidth(i), "Unexpected width of column " + i); } Assert.AreEqual(sheet.DefaultColumnWidth * 256, sheet.GetColumnWidth(1), "Unexpected width of column " + 1); }
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) {
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()); } }
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.GetColList()) { Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString()); } }
public static void ExportHandleNumExcel(Dictionary<string, Model.DTO.HKHandleNumDetail> hkHandleNumDetails, DateTime dt, ref string fileName) { FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号6 香港新马寄出个数量化单(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(hkHandleNumDetails.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.HKHandleNumDetail item in hkHandleNumDetails.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.Education); //学校申请 cell = row.CreateCell(4); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.Application); //新加坡公立硕士 cell = row.CreateCell(5); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.HK.SingaporeMaster); //香港博士 cell = row.CreateCell(6); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.HK.Doctor); //签证申请 cell = row.CreateCell(7); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.Visa); //资深文案 cell = row.CreateCell(8); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Senior); //制作文案 cell = row.CreateCell(9); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Author); //寄出日期 cell = row.CreateCell(10); cell.CellStyle = style; cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd")); //备注 cell = row.CreateCell(11); cell.CellStyle = style; cell.SetCellValue(item.Note); i++; } style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 11; style.SetFont(font); row = sheet.CreateRow(4 + hkHandleNumDetails.Count); row.HeightInPoints = 25; cell = row.CreateCell(0); cell.SetCellValue("编制人:"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; cell = row.CreateCell(3); cell.SetCellValue("经理审核:"); cell.CellStyle = style; cell = row.CreateCell(7); cell.SetCellValue("总监审核:"); cell.CellStyle = style; row = sheet.CreateRow(5 + hkHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("1、新马申请学历:幼稚园、小学、O Level、A Level、中学、预科、专科、本科、硕士、博士、语言"); cell.CellStyle = style; row = sheet.CreateRow(6 + hkHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("2、香港申请学历:本科、硕士和博士"); cell.CellStyle = style; row = sheet.CreateRow(7 + hkHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("3、量化手写无效;需要机打。"); cell.CellStyle = style; row = sheet.CreateRow(8 + hkHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("4、寄出后2个工作日内填报量化。"); cell.CellStyle = style; row = sheet.CreateRow(9 + hkHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("5、新加坡公立硕士以及香港博士申请一个合同号算2个寄出个数。"); cell.CellStyle = style; row = sheet.CreateRow(10 + hkHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("6、未精益,只填报制作文案即可。"); cell.CellStyle = style; fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1); FileStream file = File.Create(fileName); workbook.Write(file); file.Close(); stream.Close(); }
/// <summary> /// 功能:由Excel(2007)导入DataTable /// </summary> /// <param name="excelFilePath">Excel文件路径,为物理路径。</param> /// <param name="sheetName">Excel工作表名称(默认为NULL)</param> /// <param name="headerRowIndex">Excel表头行索引(默认为0)</param> /// <param name="error">出错信息</param> /// <returns>DataTable</returns> public static DataTable ExcelToDataTable(string excelFilePath, string sheetName, int? headerRowIndex, ref string error) { if (System.IO.File.Exists(excelFilePath) == true) { using (FileStream stream = System.IO.File.OpenRead(excelFilePath)) { DataTable dataTable = new DataTable(); try { XSSFWorkbook workbook = new XSSFWorkbook(stream); XSSFSheet sheet; XSSFRow headerRow; if (string.IsNullOrEmpty(sheetName) == true) { sheet = (XSSFSheet)workbook.GetSheetAt(0); } else { sheet = (XSSFSheet)workbook.GetSheet(sheetName); } if (headerRowIndex == null) { headerRow = (XSSFRow)sheet.GetRow(0); } else { headerRow = (XSSFRow)sheet.GetRow((int)headerRowIndex); } int cellCount = headerRow.LastCellNum; //添加列名 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); dataTable.Columns.Add(column); } //添加数据 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); DataRow dataRow = dataTable.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) == null) { dataRow[j] = null; } else { dataRow[j] = row.GetCell(j).ToString(); } } dataTable.Rows.Add(dataRow); } workbook = null; sheet = null; return dataTable; } catch (Exception ex) { error = "由Excel导入DataTable(ex)" + ex.ToString(); throw; } } } else { return null; } }
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()) { var totalsRow = sheet.GetRow(i + 1); var totalsCell = totalsRow.CreateCell(13); totalsCell.CellStyle = boldCellStyle; totalsCell.SetCellValue(" Grand Total"); totalsCell = totalsRow.CreateCell(14); totalsCell.CellStyle = boldTotalAmountStyle; totalsCell.SetCellValue(grandTotal); } // Forcing formula recalculation... sheet.ForceFormulaRecalculation = true; var ms = new MemoryStream(); // Writing the workbook content to the FileStream... templateWorkbook.Write(ms); TempData["Message"] = "Excel report created successfully!"; // Sending the server processed data back to the user computer... return File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename + ".xlsx"); } catch (Exception ex) { TempData["Message"] = String.Format("Opps! Something went wrong: {0}", ex.Message); return RedirectToAction("Index"); } }
/// <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)) { _transactionsRepository.RejectChanges(); } } else { //send error _usersRepository.RejectChanges(); _transactionsRepository.RejectChanges(); return false; } } _transactionsRepository.SaveChangesMade(); _usersRepository.SaveChangesMade(); return true; } catch (Exception ex) { //TODO: enviar algun error _usersRepository.RejectChanges(); _transactionsRepository.RejectChanges(); Console.WriteLine(ex.Message); return false; } }
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)) { int Row = 0; MyWorkbook = new XSSFWorkbook(file); // Read file values here #region Biographical try { string BioDataCapturer = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(0).StringCellValue; string BioUniqueID = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(1).NumericCellValue.ToString(); string BioName = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(2).StringCellValue; string BioSurname = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(3).StringCellValue; string BioLatitude = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(4).StringCellValue; string BioLongitude = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(5).StringCellValue; string BioIDNumber = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(6).StringCellValue; string BioClinic = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(7).StringCellValue; string BioDateOfBirth = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(8).DateCellValue.ToString(); string BioMale = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(9).StringCellValue; string BioFemale = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(10).StringCellValue; string BioContactNo = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(11).NumericCellValue.ToString(); string BioFileNo = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(12).StringCellValue; string BioNextOfKinRelationship = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(13).StringCellValue; string BioNextOfKinName = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(14).StringCellValue; string BioNextOfKinTelephone = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(15).NumericCellValue.ToString(); string BioHPT = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(17).StringCellValue; string BioDiabetes = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(18).StringCellValue; string BioEpilepsy = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(19).StringCellValue; string BioAsthma = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(20).StringCellValue; string BioHIV = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(21).StringCellValue; string BioTB = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(22).StringCellValue; string BioMaternalHealth = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(23).StringCellValue; string BioChildHealth = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(24).StringCellValue; string BioEpilepsy2 = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(25).StringCellValue; string BioOther = MyWorkbook.GetSheet("Biographical").GetRow(5).GetCell(26).StringCellValue; } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Biographical: " + ex.Message); } # endregion #region Visit Data List<ArrayList> VisitDataEntries = new List<ArrayList>(); Row = 2; try { while (MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990,1,1)) { ArrayList VisitDataEntriesCurrent = new ArrayList(); // Index // 0 - Date of Visit // 1 - Weight // 2 - Height // 3 - BMI // 4 - Next Visit Date // 5 - HPT // 6 - Diabetes // 7 - Epilepsy // 8 - Asthma // 9 - HIV // 10 - TB // 11 - Mat Hlth // 12 - Child Hlth // 13 - Epilepsy // 14 - Other VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(0).DateCellValue.ToString()); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(1).NumericCellValue.ToString()); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(2).NumericCellValue.ToString()); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(3).NumericCellValue.ToString()); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(5).DateCellValue.ToString()); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(7).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(8).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(9).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(10).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(11).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(12).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(13).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(14).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(15).StringCellValue); VisitDataEntriesCurrent.Add(MyWorkbook.GetSheet("Visit data").GetRow(Row).GetCell(16).StringCellValue); VisitDataEntries.Add(VisitDataEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Visit data: " + ex.Message); } #endregion #region Hypertension List<ArrayList> HypertensionEntries = new List<ArrayList>(); Row = 5; try { while (MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList HypertensionEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // 1 - DWF Referral // 2 - Diagnosed and given treatment systolic // 3 - Diagnosed and given treatment diastolic // 4 - Not put on meds systolic // 5 - Not put on meds diastolic // 6 - Next review date // 7 - On meds systolic // 8 - On meds diastolic // 9 - Blood sugar level // 10 - Results Creatinine // 11 - Results Cholesterol // 12 - Treatment HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(0).DateCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(1).StringCellValue); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(2).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(3).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(4).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(5).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(6).DateCellValue); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(7).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(8).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(10).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(12).NumericCellValue.ToString()); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(13).StringCellValue); HypertensionEntriesCurrent.Add(MyWorkbook.GetSheet("Hypertension").GetRow(Row).GetCell(14).StringCellValue); HypertensionEntries.Add(HypertensionEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Hypertension: " + ex.Message); } #endregion #region Diabetes List<ArrayList> DiabetesEntries = new List<ArrayList>(); Row = 4; try { while (MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList DiabetesEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // Blood sugar levels // 1 - DWF referral // 2 - Diagnosed & given treatment // 3 - Not on meds Blood Sugar level // 4 - Next review date // 5 - On meds Blood Sugar level // BP Reading // 6 - Systolic // 7 - Diastolic // Results // 8 - HbA1C // 9 - Creatinine // 10 - Cholesterol // 11 - Foot exam // 12 - Eye test // // 13 - Refer to clinic // 14 - Referral No. // // 15 - Treatment DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(0).DateCellValue.ToString()); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(1).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(2).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(3).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(4).DateCellValue.ToString()); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(5).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(7).DateCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(8).NumericCellValue.ToString()); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(10).NumericCellValue.ToString()); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(11).NumericCellValue.ToString()); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(12).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(13).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(14).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(15).StringCellValue); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(16).NumericCellValue.ToString()); DiabetesEntriesCurrent.Add(MyWorkbook.GetSheet("Diabetes").GetRow(Row).GetCell(17).StringCellValue); DiabetesEntries.Add(DiabetesEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Diabetes: " + ex.Message); } #endregion #region Epilepsy List<ArrayList> EpilepsyEntries = new List<ArrayList>(); Row = 5; try { while (MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList EpilepsyEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // Checks // 1 - DWF referral // 2 - No. of fits in last month // 3 - Drug side-effects if any // BP Reading // 4 - Systolic // 5 - Diastolic // // 6 - Treatment EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(0).DateCellValue.ToString()); EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(1).StringCellValue); EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(2).NumericCellValue.ToString()); EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(3).StringCellValue); EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(5).NumericCellValue.ToString()); EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(6).NumericCellValue.ToString()); EpilepsyEntriesCurrent.Add(MyWorkbook.GetSheet("Epilepsy").GetRow(Row).GetCell(8).StringCellValue); EpilepsyEntries.Add(EpilepsyEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Epilepsy: " + ex.Message); } #endregion #region Asthma List<ArrayList> AsthmaEntries = new List<ArrayList>(); Row = 5; try { while (MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList AsthmaEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // Results // 1 - DWF referral // 2 - Peak Expiratory Flow Rate // BP Reading // 3 - Systolic // 4 - Diastolic // // 5 - Treatment AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(0).DateCellValue.ToString()); AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(1).StringCellValue); AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(2).StringCellValue); AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(4).NumericCellValue.ToString()); AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(5).NumericCellValue.ToString()); AsthmaEntriesCurrent.Add(MyWorkbook.GetSheet("Asthma").GetRow(Row).GetCell(6).StringCellValue); AsthmaEntries.Add(AsthmaEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Asthma: " + ex.Message); } #endregion #region HIV List<ArrayList> HIVEntries = new List<ArrayList>(); Row = 3; try { while (MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList HIVEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // Results // 1 - DWF referral // 2 - CD4 // 3 - Viral Load // // 4 - Treatment // BP Reading // 5 - Systolic // 6 - Diastolic HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(0).DateCellValue.ToString()); HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(1).StringCellValue); HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(2).NumericCellValue.ToString()); HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(3).NumericCellValue.ToString()); HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(4).StringCellValue); HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(6).NumericCellValue.ToString()); HIVEntriesCurrent.Add(MyWorkbook.GetSheet("HIV").GetRow(Row).GetCell(7).NumericCellValue.ToString()); HIVEntries.Add(HIVEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - HIV: " + ex.Message); } #endregion #region TB List<ArrayList> TBEntries = new List<ArrayList>(); Row = 3; try { while (MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList TBEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // 1 - DWF referral // Check // 2 - Sputum Taken // Test Results Review // 3 - Date // Results // 4 - Genexpert // 5 - AFB // 6 - Treatment TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(0).DateCellValue.ToString()); TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(1).StringCellValue); TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(2).StringCellValue); TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(3).DateCellValue.ToString()); TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(5).StringCellValue); TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(6).StringCellValue); TBEntriesCurrent.Add(MyWorkbook.GetSheet("TB").GetRow(Row).GetCell(7).StringCellValue); TBEntries.Add(TBEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - TB: " + ex.Message); } #endregion #region Mat Health List<ArrayList> MatHealthEntries = new List<ArrayList>(); Row = 3; try { while (MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList MatHealthEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // 1 - DWF referral // 2 - Mom Connect Registered // Monthly ANC visit // 3 - ANC visit no // PNC Visits // 4 - PNC 1-week // 5 - PCR done // 6 - PNC 6-weeks MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(0).DateCellValue.ToString()); MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(1).StringCellValue); MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(2).StringCellValue); MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(3).NumericCellValue.ToString()); MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(4).StringCellValue); MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(5).StringCellValue); MatHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Mat Health").GetRow(Row).GetCell(6).StringCellValue); MatHealthEntries.Add(MatHealthEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Mat Health: " + ex.Message); } #endregion #region Child Health List<ArrayList> ChildHealthEntries = new List<ArrayList>(); Row = 2; try { while (MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList ChildHealthEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // 1 - DWF referral // 2 - PCR done // 3 - Current RTHC? // 4 - Vaccinations up to date ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(0).DateCellValue.ToString()); ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(1).StringCellValue); ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(2).StringCellValue); ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(3).StringCellValue); ChildHealthEntriesCurrent.Add(MyWorkbook.GetSheet("Child Health").GetRow(Row).GetCell(4).StringCellValue); ChildHealthEntries.Add(ChildHealthEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Child Health: " + ex.Message); } #endregion #region Other List<ArrayList> OtherEntries = new List<ArrayList>(); Row = 5; try { while (MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(0).DateCellValue > new DateTime(1990, 1, 1)) { ArrayList OtherEntriesCurrent = new ArrayList(); // Index // 0 - Visit date // 1 - DWF referral // Other Condition // 2 - Condition that required referral // 3 - Outcome // BP Reading // 4 - Systolic // 5 - Diastolic OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(0).DateCellValue.ToString()); OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(1).StringCellValue); OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(3).StringCellValue); OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(4).StringCellValue); OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(6).NumericCellValue.ToString()); OtherEntriesCurrent.Add(MyWorkbook.GetSheet("Other").GetRow(Row).GetCell(7).NumericCellValue.ToString()); OtherEntries.Add(OtherEntriesCurrent); Row++; } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + " - Other: " + ex.Message); } #endregion // Queries here } } catch (Exception ex) { Success = false; ErrorList.Add(CurrentFile + ": " + ex.Message); } } if (Success == false) { System.Windows.MessageBox.Show("Some errors occurred. Please check the log file for a list of errors.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information); File.WriteAllLines(Directory.GetCurrentDirectory() + "ImportErrorLog.txt", ErrorList); System.Diagnostics.Process.Start(Directory.GetCurrentDirectory() + "ImportErrorLog.txt"); } else System.Windows.MessageBox.Show(Files.Length.ToString() + " file(s) have been successfully imported.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information); return Success; }
public static void ExportUKExcel(Dictionary<string, Model.DTO.UKHandleNumDetail> ukHandleNumDetails, Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName) { FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单(2015年1月1日后转案).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 = 10; style.SetFont(font); IRow row = sheet.GetRow(3); ICell cell = row.CreateCell(1); cell.SetCellValue("英亚院校申请3部"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue(dt.ToString("yyyy/MM/dd")); cell.CellStyle = style; cell = row.CreateCell(12); cell.SetCellValue(ukHandleNumDetails.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 = 10; 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 = 11; trueOrFalseStyle.SetFont(font); int i = 0; //for (int i = 0; i < ukHandleNumDetails.Count; i++) //{ foreach (Model.DTO.UKHandleNumDetail item in ukHandleNumDetails.Values) { row = sheet.CreateRow(6 + i); row.HeightInPoints = 16.5F; //序号 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.Education); //前四 cell = row.CreateCell(4); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.UK.FirstFour); //博士 cell = row.CreateCell(5); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.UK.Doctor); //国内合作 cell = row.CreateCell(6); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.UK.Cooperation); //申请个数 cell = row.CreateCell(7); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.QuanType.ApplyNum); //网申个数 cell = row.CreateCell(8); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.QuanType.OnlineNum); //文书个数 cell = row.CreateCell(9); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.QuanType.PSNum); //资深文案 cell = row.CreateCell(10); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Senior); //制作文案 cell = row.CreateCell(11); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Author); //高级文书文案 cell = row.CreateCell(12); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.OldSenior); //文书文案 cell = row.CreateCell(13); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.OldAuthor); //转案区间 cell = row.CreateCell(14); cell.CellStyle = style; cell.SetCellValue(item.GetPeriod); //备注 cell = row.CreateCell(15); cell.CellStyle = style; cell.SetCellValue(item.Note); i++; } style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 10; style.SetFont(font); row = sheet.CreateRow(6 + ukHandleNumDetails.Count); row.HeightInPoints = 12; cell = row.CreateCell(0); cell.SetCellValue("编制人:"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; cell = row.CreateCell(3); cell.SetCellValue("经理审核:"); cell.CellStyle = style; cell = row.CreateCell(10); cell.SetCellValue("总监审核:"); cell.CellStyle = style; row = sheet.CreateRow(7+ ukHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("1、申请学历填写标准的申请类别"); cell.CellStyle = style; row = sheet.CreateRow(8 + ukHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("2、量化手写无效;需要机打。"); cell.CellStyle = style; row = sheet.CreateRow(9 + ukHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("3、寄出后2个工作日内填报量化。"); cell.CellStyle = style; row = sheet.CreateRow(10 + ukHandleNumDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("4、博士申请及英国前四所本硕申请一个合同号算2个寄出个数,国内合作一个合同号算0.5个寄出个数。"); cell.CellStyle = style; //下面开始导出量化 sheet = workbook.GetSheet("量化单"); style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 10; style.SetFont(font); row = sheet.GetRow(3); 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(11); cell.SetCellValue(ukHandleQuanDetails.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 = 10; style.SetFont(font); //方框样式 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 = 11; trueOrFalseStyle.SetFont(font); i = 0; //for (int i = 0; i < ukHandleNumDetails.Count; i++) //{ foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values) { row = sheet.CreateRow(6 + i); row.HeightInPoints = 16.7F; //序号 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.Magnetic); //文书 cell = row.CreateCell(7); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.SendQuan.PS); //录取 cell = row.CreateCell(8); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.Admission); //资深文案 cell = row.CreateCell(9); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Senior); //制作文案 cell = row.CreateCell(10); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Author); //文书文案 cell = row.CreateCell(11); cell.CellStyle = style; cell.SetCellValue(item.PS.Author); //文书部门 cell = row.CreateCell(12); cell.CellStyle = style; cell.SetCellValue(item.PS.Department); //转案区间 cell = row.CreateCell(13); cell.CellStyle = style; cell.SetCellValue(item.GetPeriod); //备注 cell = row.CreateCell(14); cell.CellStyle = style; cell.SetCellValue(item.Note); i++; } style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 10; style.SetFont(font); row = sheet.CreateRow(6 + ukHandleQuanDetails.Count); row.HeightInPoints = 16.7F; cell = row.CreateCell(0); cell.SetCellValue("编制人:"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; cell = row.CreateCell(3); cell.SetCellValue("经理审核:"); cell.CellStyle = style; cell = row.CreateCell(10); cell.SetCellValue("总监审核:"); cell.CellStyle = style; //设置左对齐单元格格式 style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Left; style.VerticalAlignment = VerticalAlignment.Center; font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 10; style.SetFont(font); row = sheet.CreateRow(7 + ukHandleQuanDetails.Count); row.HeightInPoints = 16.7F; cell = row.CreateCell(0); cell.SetCellValue("1、量化手写无效;需要机打。"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; row = sheet.CreateRow(8 + ukHandleQuanDetails.Count); row.HeightInPoints = 16.7F; cell = row.CreateCell(0); cell.CellStyle = style; cell.SetCellValue("2、寄出后2个工作日内填报量化。"); row = sheet.CreateRow(9 + ukHandleQuanDetails.Count); row.HeightInPoints = 16.7F; cell = row.CreateCell(0); cell.SetCellValue("3、申请学历填写标准的申请类别"); cell.CellStyle = style; //下面开始保存 fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1); FileStream file = File.Create(fileName); workbook.Write(file); file.Close(); stream.Close(); }
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName) { FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单(2015年1月1日后转案).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 = 10; style.SetFont(font); IRow row = sheet.GetRow(3); 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(11); cell.SetCellValue(ukHandleQuanDetails.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 = 10; 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 = 11; trueOrFalseStyle.SetFont(font); int i = 0; //for (int i = 0; i < ukHandleNumDetails.Count; i++) //{ foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values) { row = sheet.CreateRow(5 + i); row.HeightInPoints = 16.7F; //序号 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.Magnetic); //文书 cell = row.CreateCell(6); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.SendQuan.PS); //录取 cell = row.CreateCell(7); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.Admission); //资深文案 cell = row.CreateCell(8); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Senior); //制作文案 cell = row.CreateCell(9); cell.CellStyle = style; cell.SetCellValue(item.CopyWriting.Author); //文书文案 cell = row.CreateCell(10); cell.CellStyle = style; cell.SetCellValue(item.PS.Author); //文书部门 cell = row.CreateCell(11); cell.CellStyle = style; cell.SetCellValue(item.PS.Department); //转案区间 cell = row.CreateCell(12); cell.CellStyle = style; cell.SetCellValue(item.GetPeriod); //备注 cell = row.CreateCell(13); cell.CellStyle = style; cell.SetCellValue(item.Note); i++; } style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 10; style.SetFont(font); row = sheet.CreateRow(5 + ukHandleQuanDetails.Count); row.HeightInPoints = 16.7F; cell = row.CreateCell(0); cell.SetCellValue("编制人:"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; cell = row.CreateCell(3); cell.SetCellValue("经理审核:"); cell.CellStyle = style; cell = row.CreateCell(10); cell.SetCellValue("总监审核:"); cell.CellStyle = style; //设置左对齐单元格格式 style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Left; 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 = 10; style.SetFont(font); //设置合并单元格区域 CellRangeAddress cellRangeAddress = new CellRangeAddress(7 + ukHandleQuanDetails.Count, 7 + ukHandleQuanDetails.Count, 0, 12); sheet.AddMergedRegion(cellRangeAddress); row = sheet.CreateRow(7 + ukHandleQuanDetails.Count); row.HeightInPoints = 16.7F; cell = row.CreateCell(0); cell.SetCellValue("1、量化手写无效;需要机打。"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++) { ICell singleCell = HSSFCellUtil.GetCell(row, (short)j); singleCell.CellStyle = style; } //设置合并单元格区域 cellRangeAddress = new CellRangeAddress(8 + ukHandleQuanDetails.Count, 8 + ukHandleQuanDetails.Count, 0, 12); sheet.AddMergedRegion(cellRangeAddress); row = sheet.CreateRow(8 + ukHandleQuanDetails.Count); row.HeightInPoints = 25; cell = row.CreateCell(0); cell.CellStyle = style; cell.SetCellValue("2、寄出后2个工作日内填报量化。"); cell.CellStyle.Alignment = HorizontalAlignment.Left; for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++) { ICell singleCell = HSSFCellUtil.GetCell(row, (short)j); singleCell.CellStyle = style; } //设置合并单元格区域 cellRangeAddress = new CellRangeAddress(9 + ukHandleQuanDetails.Count, 9 + ukHandleQuanDetails.Count, 0, 1); sheet.AddMergedRegion(cellRangeAddress); row = sheet.CreateRow(9 + ukHandleQuanDetails.Count); row.HeightInPoints = 37.5F; cell = row.CreateCell(0); cell.SetCellValue("3、申请学历填写标准的申请类别"); cell.CellStyle = style; for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++) { ICell singleCell = HSSFCellUtil.GetCell(row, (short)j); singleCell.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); //cell = row.CreateCell(2); //cell.SetCellValue("高中及以下(无文书)"); //style.WrapText = true; //cell.CellStyle = style; //cell = row.CreateCell(3); //cell.SetCellValue("高端本科"); //cell.CellStyle = style; //cell = row.CreateCell(4); //cell.SetCellValue("本科预科/硕士预科(无文书)"); //cell.CellStyle = style; //cell = row.CreateCell(5); //cell.SetCellValue("非高端本科院校"); //cell.CellStyle = style; //cell = row.CreateCell(6); //cell.SetCellValue("高端硕士院校"); //cell.CellStyle = style; //cell = row.CreateCell(7); //cell.SetCellValue("非高端研究生"); //cell.CellStyle = style; //cell = row.CreateCell(8); //cell.SetCellValue("博士"); //cell.CellStyle = style; //cell = row.CreateCell(9); //cell.SetCellValue("只申请语言学校"); //cell.CellStyle = style; //cell = row.CreateCell(10); //cell.SetCellValue("高中及以下(有文书)"); //cell.CellStyle = style; //cell = row.CreateCell(11); //cell.SetCellValue("本科预科/硕士预科(有文书)"); //cell.CellStyle = style; //cell = row.CreateCell(12); //cell.CellStyle = style; fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1); FileStream file = File.Create(fileName); workbook.Write(file); file.Close(); stream.Close(); }
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)); row = sheet.CreateRow(8 + hnDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("3.高端合同(英国前四所大学、博士)需在“英国前四所大学”、“博士”项点黑"); cell.CellStyle = style; sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10)); row = sheet.CreateRow(9 + hnDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("4.国内合作合同需在“国内合作”项点黑"); cell.CellStyle = style; sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10)); string path = HttpContext.Current.Server.MapPath("/ExportTemp/"); DirectoryInfo info = new DirectoryInfo(path); FileInfo[] files = info.GetFiles(); if (files.Length > 0) { for (int i = files.Length - 1; i >= 0; i--) { files[i].Delete(); } } fileName += ".xlsx"; FileStream file = File.Create(path + fileName); workbook.Write(file); file.Close(); stream.Close(); return "http://blessing.wang/ExportTemp/" + fileName; }
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(); }
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; }
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; cell.SetCellValue(item.CopyWriting.Author); //新马文案 cell = row.CreateCell(11); cell.CellStyle = style; //录取 cell = row.CreateCell(12); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.Admission); //获签 cell = row.CreateCell(13); cell.CellStyle = trueOrFalseStyle; cell.SetCellValue(item.Sign); //文书文案 cell = row.CreateCell(14); cell.CellStyle = style; cell.SetCellValue(item.PS.Author); //文书部门 cell = row.CreateCell(15); cell.CellStyle = style; cell.SetCellValue(item.PS.Department); //备注 cell = row.CreateCell(16); cell.CellStyle = style; cell.SetCellValue(item.Note); i++; } style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; font = workbook.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 11; style.SetFont(font); row = sheet.CreateRow(4 + hkHandleQuanDetails.Count); row.HeightInPoints = 25; cell = row.CreateCell(0); cell.SetCellValue("编制人:"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; cell = row.CreateCell(3); cell.SetCellValue("经理审核:"); cell.CellStyle = style; cell = row.CreateCell(9); cell.SetCellValue("总监审核:"); cell.CellStyle = style; //设置左对齐单元格格式 style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Left; 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); ////设置合并单元格区域 //CellRangeAddress cellRangeAddress = new CellRangeAddress(5 + hkHandleQuanDetails.Count, 5 + hkHandleQuanDetails.Count, 0, 15); //sheet.AddMergedRegion(cellRangeAddress); row = sheet.CreateRow(5 + hkHandleQuanDetails.Count); row.HeightInPoints = 25; cell = row.CreateCell(0); cell.SetCellValue("1、量化手写无效;需要机打。"); cell.CellStyle = style; cell.CellStyle.Alignment = HorizontalAlignment.Left; //for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++) //{ // ICell singleCell = HSSFCellUtil.GetCell(row, (short)j); // singleCell.CellStyle = style; //} ////设置合并单元格区域 //cellRangeAddress = new CellRangeAddress(6 + hkHandleQuanDetails.Count, 6 + hkHandleQuanDetails.Count, 0, 15); //sheet.AddMergedRegion(cellRangeAddress); row = sheet.CreateRow(6 + hkHandleQuanDetails.Count); row.HeightInPoints = 25; cell = row.CreateCell(0); cell.CellStyle = style; cell.SetCellValue("2、寄出后2个工作日内填报量化。"); cell.CellStyle.Alignment = HorizontalAlignment.Left; //for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++) //{ // ICell singleCell = HSSFCellUtil.GetCell(row, (short)j); // singleCell.CellStyle = style; //} //设置合并单元格区域 //cellRangeAddress = new CellRangeAddress(7 + hkHandleQuanDetails.Count, 7 + hkHandleQuanDetails.Count, 0, 1); //sheet.AddMergedRegion(cellRangeAddress); row = sheet.CreateRow(7 + hkHandleQuanDetails.Count); //row.HeightInPoints = 37.5F; cell = row.CreateCell(0); cell.SetCellValue("3、新加坡量化类别:"); cell.CellStyle = style; //for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++) //{ // ICell singleCell = HSSFCellUtil.GetCell(row, (short)j); // singleCell.CellStyle = style; //} row = sheet.CreateRow(8 + hkHandleQuanDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("4、香港量化类别:"); cell.CellStyle = style; row = sheet.CreateRow(9 + hkHandleQuanDetails.Count); cell = row.CreateCell(0); cell.SetCellValue("5、马来西亚量化类别:"); 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); row = sheet.GetRow(7 + hkHandleQuanDetails.Count); cell = row.CreateCell(2); cell.SetCellValue("公立硕士"); style.WrapText = true; cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("幼儿园,小学,公立大学,大专、本科、语言/预科/私立大专,本科、研究生"); cell.CellStyle = style; cell = row.CreateCell(4); cell.SetCellValue("NEXUS/BC幼儿园"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("visa only"); cell.CellStyle = style; cell = row.CreateCell(6); cell.CellStyle = style; row = sheet.GetRow(8 + hkHandleQuanDetails.Count); cell = row.CreateCell(2); cell.SetCellValue("博士/研究生"); style.WrapText = true; cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("PS(1封)+推荐信(2封)+CV(1封)"); cell.CellStyle = style; cell = row.CreateCell(4); cell.SetCellValue("个人陈述PS(1封)"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("推荐信(2封)"); cell.CellStyle = style; cell = row.CreateCell(6); cell.SetCellValue("CV(1封)"); cell.CellStyle = style; row = sheet.GetRow(9 + hkHandleQuanDetails.Count); cell = row.CreateCell(2); cell.SetCellValue("公立本科"); style.WrapText = true; cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("公立硕士"); cell.CellStyle = style; cell = row.CreateCell(4); cell.SetCellValue("语言/预科/私立本科、研究生"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("家长旅游签"); cell.CellStyle = style; cell = row.CreateCell(6); cell.CellStyle = style; sheet.AutoSizeColumn(4); fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1); FileStream file = File.Create(fileName); workbook.Write(file); file.Close(); stream.Close(); }
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; string Diabetes_Medication = MyWorkbook.GetSheet("Diabetes").GetRow(5).GetCell(16).StringCellValue; #endregion #region Epilepsy //HiEHRef = HiEH Referral //CurrentRef = Current Referral //OT = On Treatment string EpilepsyDateOfVisit = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(0).StringCellValue; string Epilepsy_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(1).StringCellValue; string Epilepsy_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(2).StringCellValue; string Epilepsy_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(3).StringCellValue; string Epilepsy_CurrentRef_FitInLastMonth = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(5).StringCellValue; string Epilepsy_CurrentRef_RefToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(6).StringCellValue; string Epilepsy_CurrentRef_RefNum = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(7).StringCellValue; string Epilepsy_OT_CurrentlyOnMeds = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(9).StringCellValue; string Epilepsy_OT_StartDate = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(10).StringCellValue; string Epilepsy_OT_MoreThanThreeFitsInLastMonth = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(11).StringCellValue; string Epilepsy_OT_ReRefToClinic = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(12).StringCellValue; string Epilepsy_OT_ReRefNum = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(13).StringCellValue; string Epilepsy_Medication = MyWorkbook.GetSheet("Epilepsy").GetRow(5).GetCell(14).StringCellValue; #endregion #region Asthma //HiEHRef = HiEH Referral //CurrentRef = Current Referral //OT = On Treatment string AsthmaDateOfVisit = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(0).StringCellValue; string Asthma_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(1).StringCellValue; string Asthma_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(2).StringCellValue; string Asthma_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(3).StringCellValue; string Asthma_CurrentRef_DifficultyBreathing = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(5).StringCellValue; string Asthma_CurrentRef_RefToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(6).StringCellValue; string Asthma_CurrentRef_RefNum = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(7).StringCellValue; string Asthma_OT_CurrentlyOnMeds = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(9).StringCellValue; string Asthma_OT_StartDate = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(10).StringCellValue; string Asthma_OT_IncreaseInAttacks = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(11).StringCellValue; string Asthma_OT_ReRefToClinic = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(12).StringCellValue; string Asthma_OT_ReRefNum = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(13).StringCellValue; string Asthma_Medication = MyWorkbook.GetSheet("Asthma").GetRow(4).GetCell(14).StringCellValue; #endregion #region HIV //HiEHRef = HiEH Referral //ClinictRef = Clinic Referral //IP = If Positive //IN = If Negative //IU = If Unknown string HIVDateOfVisit = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(0).StringCellValue; string HIV_HiEHRef_WentToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(1).StringCellValue; string HIV_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(2).StringCellValue; string HIV_HiEHRef_ReRefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(3).StringCellValue; string HIV_ClinicRef_RefToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(5).StringCellValue; string HIV_ClinicRef_RefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(6).StringCellValue; string HIV_HIVStatus = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(8).StringCellValue; string HIV_IP_OnARVs = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(10).StringCellValue; string HIV_IP_StartDate = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(11).StringCellValue; string HIV_IP_AdherenceOK = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(12).StringCellValue; string HIV_IP_Concerns = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(13).StringCellValue; string HIV_IP_RefToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(14).StringCellValue; string HIV_IP_RefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(15).StringCellValue; string HIV_IP_ARVsConcern = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(16).StringCellValue; string HIV_IP_ReRefToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(17).StringCellValue; string HIV_IP_ReRefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(18).StringCellValue; string HIV_IN_CounselingDone = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(20).StringCellValue; string HIV_IU_HIVTestDone = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(22).StringCellValue; string HIV_HIVTest_HIVTestResults = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(24).StringCellValue; string HIV_HIVTest_ReferToClinic = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(25).StringCellValue; string HIV_HIVTest_RefNum = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(26).StringCellValue; string HIV_Medication = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(27).StringCellValue; #endregion #region TB //HiEHRef = HiEH Referral string TBDateOfVisit = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(0).StringCellValue; string TB_HiEHRef_WentToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(1).StringCellValue; string TB_HiEHRef_ReReferToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(2).StringCellValue; string TB_HiEHRef_ReRefNum = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(3).StringCellValue; string TB_SymptonsRefer_WeigthLoss = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(5).StringCellValue; string TB_SymptonsRefer_SweatingAtNight = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(6).StringCellValue; string TB_SymptonsRefer_FeverOverTwoWeeks = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(7).StringCellValue; string TB_SymptonsRefer_CoughMoreTwoWeeks = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(8).StringCellValue; string TB_SymptonsRefer_LossOfApetite = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(9).StringCellValue; string TB_SymptonsRefer_RefToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(10).StringCellValue; string TB_SymptonsRefer_RefNum = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(11).StringCellValue; string TB_SymptonsRefer_Results = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(12).StringCellValue; string TB_Treatment_NewlyDiagnosed = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(14).StringCellValue; string TB_Treatment_StartDate = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(15).StringCellValue; string TB_Treatment_RefContactsToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(16).StringCellValue; string TB_Treatment_PrevOnMeds = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(17).StringCellValue; string TB_Treatment_FinishDate = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(18).StringCellValue; string TB_Treatment_Concerns = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(19).StringCellValue; string TB_Treatment_RefToClinic = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(20).StringCellValue; string TB_Treatment_RefNum = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(21).StringCellValue; string TB_Medication = MyWorkbook.GetSheet("TB").GetRow(5).GetCell(22).StringCellValue; #endregion #region MaternalHealth //HiEHRef = HiEH Referral //CP = Currently Pregnant //PP = Possible Pregnancy string MatHealthDateOfVisit = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(0).StringCellValue; string MatHealth_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(1).StringCellValue; string MatHealth_HiEHRef_ReRefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(2).StringCellValue; string MatHealth_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(3).StringCellValue; string MatHealth_CP_DateOfFirstANC = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(5).StringCellValue; string MatHealth_CP_DateOfLastANC = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(6).StringCellValue; string MatHealth_CP_RefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(7).StringCellValue; string MatHealth_CP_RefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(8).StringCellValue; string MatHealth_CP_RegisterForMomConnect = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(9).StringCellValue; string MatHealth_CP_ReRefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(10).StringCellValue; string MatHealth_CP_ReRefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(11).StringCellValue; string MatHealth_CP_DateOfNextANC = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(12).StringCellValue; string MatHealth_CP_DeliveryDate = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(13).StringCellValue; string MatHealth_CP_IntendBreastFeed = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(14).StringCellValue; string MatHealth_CP_IntendFormula = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(15).StringCellValue; string MatHealth_PP_AreYouPregnant = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(17).StringCellValue; string MatHealth_PP_DonePregnancyTest = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(18).StringCellValue; string MatHealth_PP_Result = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(19).StringCellValue; string MatHealth_PP_RefToClinic = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(20).StringCellValue; string MatHealth_PP_RefNum = MyWorkbook.GetSheet("Mat Health").GetRow(5).GetCell(21).StringCellValue; #endregion #region ChildHealth //HiEHRef = HiEH Referral //NB = New Born //CD = Child Development //SDR = Social Development Referral //CSDR = Current Social Development Referral string ChildHealthDateOfVisit = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(0).StringCellValue; string ChildHealth_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(1).StringCellValue; string ChildHealth_HiEHRef_ReRefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(2).StringCellValue; string ChildHealth_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(3).StringCellValue; string ChildHealth_NB_ChildWithRHC = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(5).StringCellValue; string ChildHealth_NB_RefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(6).StringCellValue; string ChildHealth_NB_RefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(7).StringCellValue; string ChildHealth_NB_MotherHIVPos = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(8).StringCellValue; string ChildHealth_NB_ChildBreastfed = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(9).StringCellValue; string ChildHealth_NB_BreastfedHowLong = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(10).StringCellValue; string ChildHealth_NB_OnNevirapine = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(11).StringCellValue; string ChildHealth_NB_RefToClininc2 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(12).StringCellValue; string ChildHealth_NB_RefNum2 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(13).StringCellValue; string ChildHealth_NB_PCRDone = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(14).StringCellValue; string ChildHealth_NB_RefToClinic3 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(15).StringCellValue; string ChildHealth_NB_RefNum3 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(16).StringCellValue; string ChildHealth_NB_ImmuneUpToDate = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(17).StringCellValue; string ChildHealth_NB_ImmuneOutstanding = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(18).StringCellValue; string ChildHealth_NB_VITAandWormsGiven = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(19).StringCellValue; string ChildHealth_NB_RefToClinic4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(20).StringCellValue; string ChildHealth_NB_RefNum4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(21).StringCellValue; string ChildHealth_CD_WalkRight = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(23).StringCellValue; string ChildHealth_CD_TalkRight = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(24).StringCellValue; string ChildHealth_CD_RefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(25).StringCellValue; string ChildHealth_CD_RefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(26).StringCellValue; string ChildHealth_SDR_ChildAssisted = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(28).StringCellValue; string ChildHealth_SDR_ReRefToSD = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(29).StringCellValue; string ChildHealth_SDR_ReRefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(30).StringCellValue; string ChildHealth_CSDR_ListOfConcerns = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(31).StringCellValue; string ChildHealth_CSDR_RefToClinic = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(32).StringCellValue; string ChildHealth_CSDR_RefToSD = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(33).StringCellValue; string ChildHealth_CSDR_RefNum = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(34).StringCellValue; #endregion #region Other //HiEHRef = HiEH Referral //OC = Other Condition string OCDateOfVisit = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(0).StringCellValue; string OC_HiEHRef_WentToClinic = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(1).StringCellValue; string OC_HiEHRef_ReRefToClinic = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(2).StringCellValue; string OC_HiEHRef_ReRefNum = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(3).StringCellValue; string OC_ConditionThat = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(5).StringCellValue; string OC_RefToClinic = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(6).StringCellValue; string OC_RefNum = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(7).StringCellValue; #endregion // Queries here } } catch (Exception ex) { Success = false; ErrorList.Add(ex.Message); } } if (Success == false) { System.Windows.MessageBox.Show("Some errors occurred. Please check the log file for a list of errors.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information); File.WriteAllLines(Directory.GetCurrentDirectory() + "ImportErrorLog.txt", ErrorList); System.Diagnostics.Process.Start(Directory.GetCurrentDirectory() + "ImportErrorLog.txt"); } else System.Windows.MessageBox.Show(Files.Length.ToString() + " file(s) have been successfully imported.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information); return Success; }
public void TestMergingOverlappingCols_OVERLAPS_2_MINOR() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test"); CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0); CT_Col col = cols.AddNewCol(); col.min=(2 + 1); col.max=(4 + 1); col.width=(20); col.customWidth=(true); sheet.GroupColumn((short)1, (short)3); cols = sheet.GetCTWorksheet().GetColsArray(0); //logger.log(POILogger.DEBUG, "testMergingOverlappingCols_OVERLAPS_2_MINOR/cols:" + cols); Assert.AreEqual(1, cols.GetColArray(0).outlineLevel); Assert.AreEqual(2, cols.GetColArray(0).min); // 1 based Assert.AreEqual(2, cols.GetColArray(0).max); // 1 based Assert.AreEqual(false, cols.GetColArray(0).customWidth); Assert.AreEqual(1, cols.GetColArray(1).outlineLevel); Assert.AreEqual(3, cols.GetColArray(1).min); // 1 based Assert.AreEqual(4, cols.GetColArray(1).max); // 1 based Assert.AreEqual(true, cols.GetColArray(1).customWidth); Assert.AreEqual(0, cols.GetColArray(2).outlineLevel); Assert.AreEqual(5, cols.GetColArray(2).min); // 1 based Assert.AreEqual(5, cols.GetColArray(2).max); // 1 based Assert.AreEqual(true, cols.GetColArray(2).customWidth); Assert.AreEqual(3, cols.sizeOfColArray()); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testMergingOverlappingCols_OVERLAPS_2_MINOR"); sheet = (XSSFSheet)wb.GetSheet("test"); for (int i = 2; i <= 4; i++) { Assert.AreEqual(20 * 256, sheet.GetColumnWidth(i), "Unexpected width of column " + i); } Assert.AreEqual(sheet.DefaultColumnWidth * 256, sheet.GetColumnWidth(1),"Unexpected width of column " + 1 ); }
public IList<NextWeekTask> getNextWeekTask(Stream nwstream) { var list = new List<NextWeekTask>(); var workbook = new XSSFWorkbook(nwstream); var sheet = workbook.GetSheet("项目周报"); int maxrow = sheet.LastRowNum-1; int startrow = 0,endrow=maxrow; for (int i = 0; i < maxrow - 1; i++) { var currentRow = sheet.GetRow(i); var startcellLbl=currentRow.Cells[0].ToString(); var endcellLbl=currentRow.Cells[0].ToString(); if (startcellLbl == "下周工作计划") startrow = i; } for (int i = startrow+2; i < maxrow - 1; i++) { var currentRow = sheet.GetRow(i); var cells = currentRow.Cells; var task = new NextWeekTask(); if (currentRow != null) { foreach (var cell in cells) { switch (cell.ColumnIndex) { case 1: task.TaskNum = this.CellValueTyper(cell).ToString(); break; case 2: task.TaskName = this.CellValueTyper(cell).ToString(); break; case 3: task.ProjectNum = this.CellValueTyper(cell).ToString(); break; case 4: task.TaskType = this.CellValueTyper(cell).ToString(); break; case 5: task.InPlan = this.CellValueTyper(cell).ToString(); break; case 6: task.Description = this.CellValueTyper(cell).ToString(); break; case 7: task.Percent = this.CellValueTyper(cell).ToString(); break; case 8: try { task.StartTime = cell.DateCellValue.ToString("yyyy-MM-dd"); } catch { task.StartTime = string.Empty; } break; case 9: try { task.EndTime = cell.DateCellValue.ToString("yyyy-MM-dd"); ; } catch { task.EndTime = string.Empty; } break; case 10: try { task.ManDay = int.Parse(this.CellValueTyper(cell).ToString()); } catch { task.ManDay = 0; } break; case 11: task.Employee = this.CellValueTyper(cell).ToString(); break; case 12: task.PlanEmployee = this.CellValueTyper(cell).ToString(); break; case 13: task.Result = this.CellValueTyper(cell).ToString(); break; case 14: task.Commit = this.CellValueTyper(cell).ToString(); break; default: break; } } list.Add(task); } } return list; }
public ActionResult ImportExcel(MProjectImportExcelStub model) { if (ModelState.IsValid) { XSSFSheet sheet; int colCount; int rowCount; int startRow = 3; int colNum; int id; List<string> messageList = new List<string>(); string currentFilePathInServer = Server.MapPath(model.ExcelFilePath); XSSFWorkbook book; try { using (FileStream file = new FileStream(currentFilePathInServer, FileMode.Open, FileAccess.Read)) { book = new XSSFWorkbook(file); } sheet = (XSSFSheet)book.GetSheet("Data"); rowCount = sheet.LastRowNum; colNum = 0; id = 0; bool allowSave = true; for (int row = startRow; row <= rowCount; row++) { colNum = 0; var dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { allowSave = true; sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != "") { m_project insertData = new m_project(); m_project filledData = this.FillDataFromExcel(insertData, sheet, row, colNum,ref messageList, ref allowSave); if (allowSave == true) { RepoMProject.Save(filledData); } } } } } catch (Exception e) { messageList.Add("An error has occurred. Please contact administrator."); } ViewBag.MessageList = messageList; return View("ImportExcelForm", model); //return ret; } else { ViewBag.MessageList = null; return View("ImportExcelForm", model); } }
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; string GenCurrentNotOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(6).StringCellValue; string GenCurrentHPT = MyWorkbook.GetSheet("General").GetRow(4).GetCell(8).StringCellValue; string GenCurrentHPTListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(9).StringCellValue; string GenCurrentHPTListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(9).StringCellValue; string GenCurrentHPTListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(9).StringCellValue; string GenCurrentHPTListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(9).StringCellValue; string GenCurrentHPTListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(9).StringCellValue; string GenCurrentHPTStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(10).StringCellValue; string GenCurrentHPTDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(11).StringCellValue; string GenCurrentHPTReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(12).StringCellValue; string GenCurrentHPTReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(13).StringCellValue; string GenCurrentDiabetes = MyWorkbook.GetSheet("General").GetRow(4).GetCell(15).StringCellValue; string GenCurrentDiabetesListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(16).StringCellValue; string GenCurrentDiabetesListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(16).StringCellValue; string GenCurrentDiabetesListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(16).StringCellValue; string GenCurrentDiabetesListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(16).StringCellValue; string GenCurrentDiabetesListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(16).StringCellValue; string GenCurrentDiabetesStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(17).StringCellValue; string GenCurrentDiabetesDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(18).StringCellValue; string GenCurrentDiabetesReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(19).StringCellValue; string GenCurrentDiabetesReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(20).StringCellValue; string GenCurrentEpilepsy = MyWorkbook.GetSheet("General").GetRow(4).GetCell(22).StringCellValue; string GenCurrentEpilepsyListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(23).StringCellValue; string GenCurrentEpilepsyListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(23).StringCellValue; string GenCurrentEpilepsyListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(23).StringCellValue; string GenCurrentEpilepsyListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(23).StringCellValue; string GenCurrentEpilepsyListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(23).StringCellValue; string GenCurrentEpilepsyStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(24).StringCellValue; string GenCurrentEpilepsyDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(25).StringCellValue; string GenCurrentEpilepsyReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(26).StringCellValue; string GenCurrentEpilepsyReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(27).StringCellValue; string GenCurrentAsthma = MyWorkbook.GetSheet("General").GetRow(4).GetCell(29).StringCellValue; string GenCurrentAsthmaListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(30).StringCellValue; string GenCurrentAsthmaListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(30).StringCellValue; string GenCurrentAsthmaListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(30).StringCellValue; string GenCurrentAsthmaListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(30).StringCellValue; string GenCurrentAsthmaListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(30).StringCellValue; string GenCurrentAsthmaStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(31).StringCellValue; string GenCurrentAsthmaDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(32).StringCellValue; string GenCurrentAsthmaReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(33).StringCellValue; string GenCurrentAsthmaReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(34).StringCellValue; string GenCurrentOther = MyWorkbook.GetSheet("General").GetRow(4).GetCell(36).StringCellValue; string GenCurrentOtherListMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(37).StringCellValue; string GenCurrentOtherListMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(37).StringCellValue; string GenCurrentOtherListMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(37).StringCellValue; string GenCurrentOtherListMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(37).StringCellValue; string GenCurrentOtherListMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(37).StringCellValue; string GenCurrentOtherStartDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(38).StringCellValue; string GenCurrentOtherDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(39).StringCellValue; string GenCurrentOtherReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(40).StringCellValue; string GenCurrentOtherReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(41).StringCellValue; string GenBPOnMedsSystolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(43).StringCellValue; string GenBPOnMedsDiatolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(44).StringCellValue; string GenBPNotOnMedsSystolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(45).StringCellValue; string GenBPNotOnMedsDiatolic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(46).StringCellValue; string GenBPReferCHOW = MyWorkbook.GetSheet("General").GetRow(4).GetCell(47).StringCellValue; string GenBPReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(48).StringCellValue; string GenBPReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(49).StringCellValue; string GenBSOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(51).StringCellValue; string GenBSNotOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(52).StringCellValue; string GenBSReferChow = MyWorkbook.GetSheet("General").GetRow(4).GetCell(53).StringCellValue; string GenBSReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(54).StringCellValue; string GenBSReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(55).StringCellValue; string GenEPFitsMonth = MyWorkbook.GetSheet("General").GetRow(4).GetCell(57).StringCellValue; string GenEPReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(58).StringCellValue; string GenEPReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(59).StringCellValue; string GenHIVPosStatus = MyWorkbook.GetSheet("General").GetRow(4).GetCell(61).StringCellValue; string GenHIVNegStatus = MyWorkbook.GetSheet("General").GetRow(4).GetCell(62).StringCellValue; string GenHIVTestDone = MyWorkbook.GetSheet("General").GetRow(4).GetCell(63).StringCellValue; string GenHIVResult = MyWorkbook.GetSheet("General").GetRow(4).GetCell(64).StringCellValue; string GenHIVReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(65).StringCellValue; string GenHIVReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(66).StringCellValue; string GenPregCurrently = MyWorkbook.GetSheet("General").GetRow(4).GetCell(68).StringCellValue; string GenPregPossible = MyWorkbook.GetSheet("General").GetRow(4).GetCell(69).StringCellValue; string GenPregTestDate = MyWorkbook.GetSheet("General").GetRow(4).GetCell(70).StringCellValue; string GenPregResult = MyWorkbook.GetSheet("General").GetRow(4).GetCell(71).StringCellValue; string GenPregReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(72).StringCellValue; string GenPregReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(73).StringCellValue; string GenTBCurrentHave = MyWorkbook.GetSheet("General").GetRow(4).GetCell(75).StringCellValue; string GenTBCurrentMeds1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(76).StringCellValue; string GenTBCurrentMeds2 = MyWorkbook.GetSheet("General").GetRow(5).GetCell(76).StringCellValue; string GenTBCurrentMeds3 = MyWorkbook.GetSheet("General").GetRow(6).GetCell(76).StringCellValue; string GenTBCurrentMeds4 = MyWorkbook.GetSheet("General").GetRow(7).GetCell(76).StringCellValue; string GenTBCurrentMeds5 = MyWorkbook.GetSheet("General").GetRow(8).GetCell(76).StringCellValue; string GenTBCurrentDefaulting = MyWorkbook.GetSheet("General").GetRow(4).GetCell(77).StringCellValue; string GenTBSymtomWeightLoss = MyWorkbook.GetSheet("General").GetRow(4).GetCell(78).StringCellValue; string GenTBSymtomSweat = MyWorkbook.GetSheet("General").GetRow(4).GetCell(79).StringCellValue; string GenTBSymtomFeaver = MyWorkbook.GetSheet("General").GetRow(4).GetCell(80).StringCellValue; string GenTBSymtomCough = MyWorkbook.GetSheet("General").GetRow(4).GetCell(81).StringCellValue; string GenTBSymtomApetite = MyWorkbook.GetSheet("General").GetRow(4).GetCell(82).StringCellValue; string GenTBSymtomReferClininc = MyWorkbook.GetSheet("General").GetRow(4).GetCell(83).StringCellValue; string GenTBSymtomReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(84).StringCellValue; string GenTBTraceHousholdOnMeds = MyWorkbook.GetSheet("General").GetRow(4).GetCell(85).StringCellValue; string GenTBTraceReferClininc = MyWorkbook.GetSheet("General").GetRow(4).GetCell(86).StringCellValue; string GenTBTraceReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(87).StringCellValue; string GenOtherBloodUrine = MyWorkbook.GetSheet("General").GetRow(4).GetCell(89).StringCellValue; string GenOtherReferClinic1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(90).StringCellValue; string GenOtherReferNo1 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(91).StringCellValue; string GenOtherSmoking = MyWorkbook.GetSheet("General").GetRow(4).GetCell(93).StringCellValue; string GenOtherAlcohol = MyWorkbook.GetSheet("General").GetRow(4).GetCell(94).StringCellValue; string GenOtherDiarrhoea = MyWorkbook.GetSheet("General").GetRow(4).GetCell(96).StringCellValue; string GenOtherReferClinic2 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(97).StringCellValue; string GenOtherReferNo2 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(98).StringCellValue; string GenOtherInitiationSchool = MyWorkbook.GetSheet("General").GetRow(4).GetCell(100).StringCellValue; string GenOtherLegCramps = MyWorkbook.GetSheet("General").GetRow(4).GetCell(102).StringCellValue; string GenOtherLegNumb = MyWorkbook.GetSheet("General").GetRow(4).GetCell(103).StringCellValue; string GenOtherFootUlcer = MyWorkbook.GetSheet("General").GetRow(4).GetCell(104).StringCellValue; string GenOtherReferClinic3 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(105).StringCellValue; string GenOtherReferNo3 = MyWorkbook.GetSheet("General").GetRow(4).GetCell(106).StringCellValue; string GenElderAmputation = MyWorkbook.GetSheet("General").GetRow(4).GetCell(108).StringCellValue; string GenElderVision = MyWorkbook.GetSheet("General").GetRow(4).GetCell(109).StringCellValue; string GenElderBedridden = MyWorkbook.GetSheet("General").GetRow(4).GetCell(110).StringCellValue; string GenElderMovingAid = MyWorkbook.GetSheet("General").GetRow(4).GetCell(111).StringCellValue; string GenElderWash = MyWorkbook.GetSheet("General").GetRow(4).GetCell(112).StringCellValue; string GenElderFeed = MyWorkbook.GetSheet("General").GetRow(4).GetCell(113).StringCellValue; string GenElderDress = MyWorkbook.GetSheet("General").GetRow(4).GetCell(114).StringCellValue; string GenElderReferClinic = MyWorkbook.GetSheet("General").GetRow(4).GetCell(115).StringCellValue; string GenElderReferNo = MyWorkbook.GetSheet("General").GetRow(4).GetCell(116).StringCellValue; string GenFamilyPlan = MyWorkbook.GetSheet("General").GetRow(4).GetCell(118).StringCellValue; #endregion #region Hypertention string HypYear = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(1).StringCellValue; string HypHeadache = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(3).StringCellValue; string HypVision = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(4).StringCellValue; string HypShortBreath = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(5).StringCellValue; string HypConfusion = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(6).StringCellValue; string HypChestPain = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(7).StringCellValue; string HypReferClinic = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(8).StringCellValue; string HypReferNo = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(9).StringCellValue; string HypHadStroke = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(11).StringCellValue; string HypHadStrokeYear = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(12).StringCellValue; string HypFamilyOnMeds = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(13).StringCellValue; string HypFamilyStroke = MyWorkbook.GetSheet("Hypertention").GetRow(3).GetCell(14).StringCellValue; #endregion #region Diabetes string DYear = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(1).StringCellValue; string DThirsty = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(3).StringCellValue; string DWeightloss = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(4).StringCellValue; string DVision = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(5).StringCellValue; string DUrinate = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(6).StringCellValue; string DNausea = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(7).StringCellValue; string DFoot = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(8).StringCellValue; string DReferClinic = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(9).StringCellValue; string DReferNo = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(10).StringCellValue; string DFamilyMember = MyWorkbook.GetSheet("Diabetes").GetRow(3).GetCell(12).StringCellValue; #endregion #region HIV string HIVYear = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(1).StringCellValue; string HIVOnMeds = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(2).StringCellValue; string HIVListMeds1 = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(3).StringCellValue; string HIVListMeds2 = MyWorkbook.GetSheet("HIV").GetRow(3).GetCell(3).StringCellValue; string HIVListMeds3 = MyWorkbook.GetSheet("HIV").GetRow(4).GetCell(3).StringCellValue; string HIVListMeds4 = MyWorkbook.GetSheet("HIV").GetRow(5).GetCell(3).StringCellValue; string HIVListMeds5 = MyWorkbook.GetSheet("HIV").GetRow(6).GetCell(3).StringCellValue; string HIVAdherence = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(4).StringCellValue; string HIVReferClinic = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(5).StringCellValue; string HIVReferNo = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(6).StringCellValue; string HIVARVNo = MyWorkbook.GetSheet("HIV").GetRow(2).GetCell(7).StringCellValue; #endregion #region Maternal Health string MHPregnantBefore = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(1).StringCellValue; string MHNoPregnant = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(2).StringCellValue; string MHNOSuccessful = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(3).StringCellValue; string MHWhereDeliveredLast = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(4).StringCellValue; string MHCaesarian = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(5).StringCellValue; string MHBabyUnder25 = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(6).StringCellValue; string MHChildrenDied1 = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(8).StringCellValue; string MHChildrenDied15 = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(9).StringCellValue; string MHPAPSmear = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(10).StringCellValue; string MHBloodResult = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(11).StringCellValue; string MHFirstANCDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(13).StringCellValue; string MHLastANCDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(14).StringCellValue; string MHReferClinic = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(15).StringCellValue; string MHReferNo = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(16).StringCellValue; string MHNextANCDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(17).StringCellValue; string MHExpectedDeliverDate = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(18).StringCellValue; string MHBreastfeed = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(19).StringCellValue; string MHFormula = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(20).StringCellValue; string MHRegisteredMomConnect = MyWorkbook.GetSheet("Maternal health").GetRow(3).GetCell(21).StringCellValue; #endregion #region Child Health string CHNameMother = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(1).StringCellValue; string CHChildRTHC = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(3).StringCellValue; string CHReferClinic1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(4).StringCellValue; string CHReferNo1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(5).StringCellValue; string CHMotherHIV = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(7).StringCellValue; string CHChildBreastfeed = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(8).StringCellValue; string CHHowLong = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(9).StringCellValue; string CHChildOnNevirapine = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(10).StringCellValue; string CHPCR = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(11).StringCellValue; string CHPCRResult = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(12).StringCellValue; string CHReferClininc2 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(13).StringCellValue; string CHReferNo2 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(14).StringCellValue; string CHImmunisationUpToDate = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(15).StringCellValue; string CHImmunisationOutstanding1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(16).StringCellValue; string CHImmunisationOutstanding2 = MyWorkbook.GetSheet("Child health").GetRow(3).GetCell(16).StringCellValue; string CHImmunisationOutstanding3 = MyWorkbook.GetSheet("Child health").GetRow(4).GetCell(16).StringCellValue; string CHImmunisationOutstanding4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(16).StringCellValue; string CHImmunisationOutstanding5 = MyWorkbook.GetSheet("Child health").GetRow(6).GetCell(16).StringCellValue; string CHReferClinic3 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(17).StringCellValue; string CHReferNo3 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(18).StringCellValue; string CHMedsGiven = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(19).StringCellValue; string CHWalkAppropriate = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(21).StringCellValue; string CHTalkAppropriate = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(22).StringCellValue; string CHChildConcerns1 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(24).StringCellValue; string CHChildConcerns2 = MyWorkbook.GetSheet("Child health").GetRow(3).GetCell(24).StringCellValue; string CHChildConcerns3 = MyWorkbook.GetSheet("Child health").GetRow(4).GetCell(24).StringCellValue; string CHChildConcerns4 = MyWorkbook.GetSheet("Child health").GetRow(5).GetCell(24).StringCellValue; string CHChildConcerns5 = MyWorkbook.GetSheet("Child health").GetRow(6).GetCell(24).StringCellValue; string CHReferClinic4 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(25).StringCellValue; string CHReferOVC = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(26).StringCellValue; string CHReferNo4 = MyWorkbook.GetSheet("Child health").GetRow(2).GetCell(27).StringCellValue; #endregion #region Other string OCondition1 = MyWorkbook.GetSheet("Other").GetRow(2).GetCell(1).StringCellValue; string OReferClinic1 = MyWorkbook.GetSheet("Other").GetRow(2).GetCell(2).StringCellValue; string OReferNo1 = MyWorkbook.GetSheet("Other").GetRow(2).GetCell(2).StringCellValue; string OCondition2 = MyWorkbook.GetSheet("Other").GetRow(3).GetCell(1).StringCellValue; string OReferClinic2 = MyWorkbook.GetSheet("Other").GetRow(3).GetCell(2).StringCellValue; string OReferNo2 = MyWorkbook.GetSheet("Other").GetRow(3).GetCell(2).StringCellValue; string OCondition3 = MyWorkbook.GetSheet("Other").GetRow(4).GetCell(1).StringCellValue; string OReferClinic3 = MyWorkbook.GetSheet("Other").GetRow(4).GetCell(2).StringCellValue; string OReferNo3 = MyWorkbook.GetSheet("Other").GetRow(4).GetCell(2).StringCellValue; string OCondition4 = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(1).StringCellValue; string OReferClinic4 = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(2).StringCellValue; string OReferNo4 = MyWorkbook.GetSheet("Other").GetRow(5).GetCell(2).StringCellValue; string OCondition5 = MyWorkbook.GetSheet("Other").GetRow(6).GetCell(1).StringCellValue; string OReferClinic5 = MyWorkbook.GetSheet("Other").GetRow(6).GetCell(2).StringCellValue; string OReferNo5 = MyWorkbook.GetSheet("Other").GetRow(6).GetCell(2).StringCellValue; #endregion // Queries here string ScreeningID = Utilities.GenerateScreeningID(BioName, BioSurname); #region Biographical SqlConnection tempConnectionBio = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionBio.Open(); SqlCommand tempCommand = new SqlCommand("InsertBiographical", tempConnectionBio); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", BioChowName); tempCommand.Parameters.AddWithValue("@", BioUniqueID); tempCommand.Parameters.AddWithValue("@", BioDateOfScreen); tempCommand.Parameters.AddWithValue("@", BioHeadOfHousehold); tempCommand.Parameters.AddWithValue("@", BioName); tempCommand.Parameters.AddWithValue("@", BioSurname); tempCommand.Parameters.AddWithValue("@", BioGPSLat); tempCommand.Parameters.AddWithValue("@", BioGPSLong); tempCommand.Parameters.AddWithValue("@", BioIDNum); tempCommand.Parameters.AddWithValue("@", BioClinicUsed); tempCommand.Parameters.AddWithValue("@", BioDateOfBirth); tempCommand.Parameters.AddWithValue("@", BioMale); tempCommand.Parameters.AddWithValue("@", BioFemale); tempCommand.Parameters.AddWithValue("@", BioAttendingSchool); tempCommand.Parameters.AddWithValue("@", BioSchoolName); tempCommand.Parameters.AddWithValue("@", BioGrade); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionBio.Close(); } #endregion #region Environmental SqlConnection tempConnectionEnv = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionEnv.Open(); SqlCommand tempCommand = new SqlCommand("InsertEnvironmental", tempConnectionEnv); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", EnNoOfPeople); tempCommand.Parameters.AddWithValue("@", EnNoLiveAway); tempCommand.Parameters.AddWithValue("@", EnListWhere0); tempCommand.Parameters.AddWithValue("@", EnListWhere1); tempCommand.Parameters.AddWithValue("@", EnListWhere2); tempCommand.Parameters.AddWithValue("@", EnListWhere3); tempCommand.Parameters.AddWithValue("@", EnListWhere4); tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit0); tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit1); tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit2); tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit3); tempCommand.Parameters.AddWithValue("@", EnFamilyLastVisit4); tempCommand.Parameters.AddWithValue("@", EnWhichClinic); tempCommand.Parameters.AddWithValue("@", EnMainHutStructure); tempCommand.Parameters.AddWithValue("@", EnMainHutTypeRoof); tempCommand.Parameters.AddWithValue("@", EnMainHutVentilation); tempCommand.Parameters.AddWithValue("@", EnMainHutTotalRooms); tempCommand.Parameters.AddWithValue("@", EnHut2Structure); tempCommand.Parameters.AddWithValue("@", EnHut2TypeRoof); tempCommand.Parameters.AddWithValue("@", EnHut2Ventilation); tempCommand.Parameters.AddWithValue("@", EnHut2TotalRooms); tempCommand.Parameters.AddWithValue("@", EnHut3Structure); tempCommand.Parameters.AddWithValue("@", EnHut3TypeRoof); tempCommand.Parameters.AddWithValue("@", EnHut3Ventilation); tempCommand.Parameters.AddWithValue("@", EnHut3TotalRooms); tempCommand.Parameters.AddWithValue("@", EnHut4Structure); tempCommand.Parameters.AddWithValue("@", EnHut4TypeRoof); tempCommand.Parameters.AddWithValue("@", EnHut4Ventilation); tempCommand.Parameters.AddWithValue("@", EnHut4TotalRooms); tempCommand.Parameters.AddWithValue("@", EnHut5Structure); tempCommand.Parameters.AddWithValue("@", EnHut5TypeRoof); tempCommand.Parameters.AddWithValue("@", EnHut5Ventilation); tempCommand.Parameters.AddWithValue("@", EnHut5TotalRooms); tempCommand.Parameters.AddWithValue("@", EnNoSleepingInOneRoom); tempCommand.Parameters.AddWithValue("@", EnNoOfStructures); tempCommand.Parameters.AddWithValue("@", EnRainWaterCollection); tempCommand.Parameters.AddWithValue("@", EnWaterSupply); tempCommand.Parameters.AddWithValue("@", EnWaterSupply1); tempCommand.Parameters.AddWithValue("@", EnWalkingDistanceWater); tempCommand.Parameters.AddWithValue("@", EnTreatWater); tempCommand.Parameters.AddWithValue("@", EnHutElectricity); tempCommand.Parameters.AddWithValue("@", EnFridge); tempCommand.Parameters.AddWithValue("@", EnUseForCooking); tempCommand.Parameters.AddWithValue("@", EnUseForCooking1); tempCommand.Parameters.AddWithValue("@", EnUseForCooking2); tempCommand.Parameters.AddWithValue("@", EnTypeToilet); tempCommand.Parameters.AddWithValue("@", EnDisposeWaste); tempCommand.Parameters.AddWithValue("@", EnDisposeWaste1); tempCommand.Parameters.AddWithValue("@", EnSourceIncomeHousehold); tempCommand.Parameters.AddWithValue("@", EnFoodParcel); tempCommand.ExecuteScalar(); } catch { } finally { tempConnectionEnv.Close(); } #endregion #region General SqlConnection tempConnectionGen = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionGen.Open(); SqlCommand tempCommand = new SqlCommand("InsertGeneral", tempConnectionGen); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", GenWeight); tempCommand.Parameters.AddWithValue("@", GenHeight); tempCommand.Parameters.AddWithValue("@", GenBMI); tempCommand.Parameters.AddWithValue("@", GenCurrentOnMeds); tempCommand.Parameters.AddWithValue("@", GenCurrentNotOnMeds); tempCommand.Parameters.AddWithValue("@", GenCurrentHPT); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds1); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds2); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds3); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds4); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTListMeds5); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTStartDate); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTDefaulting); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTReferClinic); tempCommand.Parameters.AddWithValue("@", GenCurrentHPTReferNo); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetes); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds1); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds2); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds3); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds4); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesListMeds5); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesStartDate); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesDefaulting); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesReferClinic); tempCommand.Parameters.AddWithValue("@", GenCurrentDiabetesReferNo); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsy); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds1); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds2); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds3); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds4); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyListMeds5); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyStartDate); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyDefaulting); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyReferClinic); tempCommand.Parameters.AddWithValue("@", GenCurrentEpilepsyReferNo); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthma); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds1); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds2); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds3); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds4); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaListMeds5); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaStartDate); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaDefaulting); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaReferClinic); tempCommand.Parameters.AddWithValue("@", GenCurrentAsthmaReferNo); tempCommand.Parameters.AddWithValue("@", GenCurrentOther); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds1); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds2); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds3); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds4); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherListMeds5); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherStartDate); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherDefaulting); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherReferClinic); tempCommand.Parameters.AddWithValue("@", GenCurrentOtherReferNo); tempCommand.Parameters.AddWithValue("@", GenBPOnMedsSystolic); tempCommand.Parameters.AddWithValue("@", GenBPOnMedsDiatolic); tempCommand.Parameters.AddWithValue("@", GenBPNotOnMedsSystolic); tempCommand.Parameters.AddWithValue("@", GenBPNotOnMedsDiatolic); tempCommand.Parameters.AddWithValue("@", GenBPReferCHOW); tempCommand.Parameters.AddWithValue("@", GenBPReferClinic); tempCommand.Parameters.AddWithValue("@", GenBPReferNo); tempCommand.Parameters.AddWithValue("@", GenBSOnMeds); tempCommand.Parameters.AddWithValue("@", GenBSNotOnMeds); tempCommand.Parameters.AddWithValue("@", GenBSReferChow); tempCommand.Parameters.AddWithValue("@", GenBSReferClinic); tempCommand.Parameters.AddWithValue("@", GenBSReferNo); tempCommand.Parameters.AddWithValue("@", GenEPFitsMonth); tempCommand.Parameters.AddWithValue("@", GenEPReferClinic); tempCommand.Parameters.AddWithValue("@", GenEPReferNo); tempCommand.Parameters.AddWithValue("@", GenHIVPosStatus); tempCommand.Parameters.AddWithValue("@", GenHIVNegStatus); tempCommand.Parameters.AddWithValue("@", GenHIVTestDone); tempCommand.Parameters.AddWithValue("@", GenHIVResult); tempCommand.Parameters.AddWithValue("@", GenHIVReferClinic); tempCommand.Parameters.AddWithValue("@", GenHIVReferNo); tempCommand.Parameters.AddWithValue("@", GenPregCurrently); tempCommand.Parameters.AddWithValue("@", GenPregPossible); tempCommand.Parameters.AddWithValue("@", GenPregTestDate); tempCommand.Parameters.AddWithValue("@", GenPregResult); tempCommand.Parameters.AddWithValue("@", GenPregReferClinic); tempCommand.Parameters.AddWithValue("@", GenPregReferNo); tempCommand.Parameters.AddWithValue("@", GenTBCurrentHave); tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds1); tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds2); tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds3); tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds4); tempCommand.Parameters.AddWithValue("@", GenTBCurrentMeds5); tempCommand.Parameters.AddWithValue("@", GenTBCurrentDefaulting); tempCommand.Parameters.AddWithValue("@", GenTBSymtomWeightLoss); tempCommand.Parameters.AddWithValue("@", GenTBSymtomSweat); tempCommand.Parameters.AddWithValue("@", GenTBSymtomFeaver); tempCommand.Parameters.AddWithValue("@", GenTBSymtomCough); tempCommand.Parameters.AddWithValue("@", GenTBSymtomApetite); tempCommand.Parameters.AddWithValue("@", GenTBSymtomReferClininc); tempCommand.Parameters.AddWithValue("@", GenTBSymtomReferNo); tempCommand.Parameters.AddWithValue("@", GenTBTraceHousholdOnMeds); tempCommand.Parameters.AddWithValue("@", GenTBTraceReferClininc); tempCommand.Parameters.AddWithValue("@", GenTBTraceReferNo); tempCommand.Parameters.AddWithValue("@", GenOtherBloodUrine); tempCommand.Parameters.AddWithValue("@", GenOtherReferClinic1); tempCommand.Parameters.AddWithValue("@", GenOtherReferNo1); tempCommand.Parameters.AddWithValue("@", GenOtherSmoking); tempCommand.Parameters.AddWithValue("@", GenOtherAlcohol); tempCommand.Parameters.AddWithValue("@", GenOtherDiarrhoea); tempCommand.Parameters.AddWithValue("@", GenOtherReferClinic2); tempCommand.Parameters.AddWithValue("@", GenOtherReferNo2); tempCommand.Parameters.AddWithValue("@", GenOtherInitiationSchool); tempCommand.Parameters.AddWithValue("@", GenOtherLegCramps); tempCommand.Parameters.AddWithValue("@", GenOtherLegNumb); tempCommand.Parameters.AddWithValue("@", GenOtherFootUlcer); tempCommand.Parameters.AddWithValue("@", GenOtherReferClinic3); tempCommand.Parameters.AddWithValue("@", GenOtherReferNo3); tempCommand.Parameters.AddWithValue("@", GenElderAmputation); tempCommand.Parameters.AddWithValue("@", GenElderVision); tempCommand.Parameters.AddWithValue("@", GenElderBedridden); tempCommand.Parameters.AddWithValue("@", GenElderMovingAid); tempCommand.Parameters.AddWithValue("@", GenElderWash); tempCommand.Parameters.AddWithValue("@", GenElderFeed); tempCommand.Parameters.AddWithValue("@", GenElderDress); tempCommand.Parameters.AddWithValue("@", GenElderReferClinic); tempCommand.Parameters.AddWithValue("@", GenElderReferNo); tempCommand.Parameters.AddWithValue("@", GenFamilyPlan); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionGen.Close(); } #endregion #region Hypertention SqlConnection tempConnectionHyp = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionHyp.Open(); SqlCommand tempCommand = new SqlCommand("InsertHypertention", tempConnectionHyp); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", HypYear); tempCommand.Parameters.AddWithValue("@", HypHeadache); tempCommand.Parameters.AddWithValue("@", HypVision); tempCommand.Parameters.AddWithValue("@", HypShortBreath); tempCommand.Parameters.AddWithValue("@", HypConfusion); tempCommand.Parameters.AddWithValue("@", HypChestPain); tempCommand.Parameters.AddWithValue("@", HypReferClinic); tempCommand.Parameters.AddWithValue("@", HypReferNo); tempCommand.Parameters.AddWithValue("@", HypHadStroke); tempCommand.Parameters.AddWithValue("@", HypHadStrokeYear); tempCommand.Parameters.AddWithValue("@", HypFamilyOnMeds); tempCommand.Parameters.AddWithValue("@", HypFamilyStroke); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionHyp.Close(); } #endregion #region Diabetes SqlConnection tempConnectionDia = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionDia.Open(); SqlCommand tempCommand = new SqlCommand("InsertDiabetes", tempConnectionDia); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", DYear); tempCommand.Parameters.AddWithValue("@", DThirsty); tempCommand.Parameters.AddWithValue("@", DWeightloss); tempCommand.Parameters.AddWithValue("@", DVision); tempCommand.Parameters.AddWithValue("@", DUrinate); tempCommand.Parameters.AddWithValue("@", DNausea); tempCommand.Parameters.AddWithValue("@", DFoot); tempCommand.Parameters.AddWithValue("@", DReferClinic); tempCommand.Parameters.AddWithValue("@", DReferNo); tempCommand.Parameters.AddWithValue("@", DFamilyMember); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionDia.Close(); } #endregion #region HIV SqlConnection tempConnectionHIV = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionHIV.Open(); SqlCommand tempCommand = new SqlCommand("InsertHIV", tempConnectionHIV); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", HIVYear); tempCommand.Parameters.AddWithValue("@", HIVOnMeds); tempCommand.Parameters.AddWithValue("@", HIVListMeds1); tempCommand.Parameters.AddWithValue("@", HIVListMeds2); tempCommand.Parameters.AddWithValue("@", HIVListMeds3); tempCommand.Parameters.AddWithValue("@", HIVListMeds4); tempCommand.Parameters.AddWithValue("@", HIVListMeds5); tempCommand.Parameters.AddWithValue("@", HIVAdherence); tempCommand.Parameters.AddWithValue("@", HIVReferClinic); tempCommand.Parameters.AddWithValue("@", HIVReferNo); tempCommand.Parameters.AddWithValue("@", HIVARVNo); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionHIV.Close(); } #endregion #region Maternal Health SqlConnection tempConnectionMat = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionMat.Open(); SqlCommand tempCommand = new SqlCommand("InsertMaternalHealth", tempConnectionMat); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", MHPregnantBefore); tempCommand.Parameters.AddWithValue("@", MHNoPregnant); tempCommand.Parameters.AddWithValue("@", MHNOSuccessful); tempCommand.Parameters.AddWithValue("@", MHWhereDeliveredLast); tempCommand.Parameters.AddWithValue("@", MHCaesarian); tempCommand.Parameters.AddWithValue("@", MHBabyUnder25); tempCommand.Parameters.AddWithValue("@", MHChildrenDied1); tempCommand.Parameters.AddWithValue("@", MHChildrenDied15); tempCommand.Parameters.AddWithValue("@", MHPAPSmear); tempCommand.Parameters.AddWithValue("@", MHBloodResult); tempCommand.Parameters.AddWithValue("@", MHFirstANCDate); tempCommand.Parameters.AddWithValue("@", MHLastANCDate); tempCommand.Parameters.AddWithValue("@", MHReferClinic); tempCommand.Parameters.AddWithValue("@", MHReferNo); tempCommand.Parameters.AddWithValue("@", MHNextANCDate); tempCommand.Parameters.AddWithValue("@", MHExpectedDeliverDate); tempCommand.Parameters.AddWithValue("@", MHBreastfeed); tempCommand.Parameters.AddWithValue("@", MHFormula); tempCommand.Parameters.AddWithValue("@", MHRegisteredMomConnect); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionMat.Close(); } #endregion #region Child Health SqlConnection tempConnectionChild = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionChild.Open(); SqlCommand tempCommand = new SqlCommand("InsertChildHealth", tempConnectionChild); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", CHNameMother); tempCommand.Parameters.AddWithValue("@", CHChildRTHC); tempCommand.Parameters.AddWithValue("@", CHReferClinic1); tempCommand.Parameters.AddWithValue("@", CHReferNo1); tempCommand.Parameters.AddWithValue("@", CHMotherHIV); tempCommand.Parameters.AddWithValue("@", CHChildBreastfeed); tempCommand.Parameters.AddWithValue("@", CHHowLong); tempCommand.Parameters.AddWithValue("@", CHChildOnNevirapine); tempCommand.Parameters.AddWithValue("@", CHPCR); tempCommand.Parameters.AddWithValue("@", CHPCRResult); tempCommand.Parameters.AddWithValue("@", CHReferClininc2); tempCommand.Parameters.AddWithValue("@", CHReferNo2); tempCommand.Parameters.AddWithValue("@", CHImmunisationUpToDate); tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding1); tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding2); tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding3); tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding4); tempCommand.Parameters.AddWithValue("@", CHImmunisationOutstanding5); tempCommand.Parameters.AddWithValue("@", CHReferClinic3); tempCommand.Parameters.AddWithValue("@", CHReferNo3); tempCommand.Parameters.AddWithValue("@", CHMedsGiven); tempCommand.Parameters.AddWithValue("@", CHWalkAppropriate); tempCommand.Parameters.AddWithValue("@", CHTalkAppropriate); tempCommand.Parameters.AddWithValue("@", CHChildConcerns1); tempCommand.Parameters.AddWithValue("@", CHChildConcerns2); tempCommand.Parameters.AddWithValue("@", CHChildConcerns3); tempCommand.Parameters.AddWithValue("@", CHChildConcerns4); tempCommand.Parameters.AddWithValue("@", CHChildConcerns5); tempCommand.Parameters.AddWithValue("@", CHReferClinic4); tempCommand.Parameters.AddWithValue("@", CHReferOVC); tempCommand.Parameters.AddWithValue("@", CHReferNo4); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionChild.Close(); } #endregion #region Other SqlConnection tempConnectionOther = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { tempConnectionOther.Open(); SqlCommand tempCommand = new SqlCommand("InsertBiographical", tempConnectionOther); tempCommand.CommandType = CommandType.StoredProcedure; tempCommand.Parameters.AddWithValue("@", OCondition1); tempCommand.Parameters.AddWithValue("@", OReferClinic1); tempCommand.Parameters.AddWithValue("@", OReferNo1); tempCommand.Parameters.AddWithValue("@", OCondition2); tempCommand.Parameters.AddWithValue("@", OReferClinic2); tempCommand.Parameters.AddWithValue("@", OReferNo2); tempCommand.Parameters.AddWithValue("@", OCondition3); tempCommand.Parameters.AddWithValue("@", OReferClinic3); tempCommand.Parameters.AddWithValue("@", OReferNo3); tempCommand.Parameters.AddWithValue("@", OCondition4); tempCommand.Parameters.AddWithValue("@", OReferClinic4); tempCommand.Parameters.AddWithValue("@", OReferNo4); tempCommand.Parameters.AddWithValue("@", OCondition5); tempCommand.Parameters.AddWithValue("@", OReferClinic5); tempCommand.Parameters.AddWithValue("@", OReferNo5); tempCommand.ExecuteNonQuery(); } catch { } finally { tempConnectionOther.Close(); } #endregion } } catch(Exception ex) { Success = false; ErrorList.Add(ex.Message); } } if (Success == false) { System.Windows.MessageBox.Show("Some errors occurred. Please check the log file for a list of errors.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information); File.WriteAllLines(Directory.GetCurrentDirectory() + "ImportErrorLog.txt", ErrorList); System.Diagnostics.Process.Start(Directory.GetCurrentDirectory() + "ImportErrorLog.txt"); } else System.Windows.MessageBox.Show(Files.Length.ToString() + " file(s) have been successfully imported.", "Notification", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Information); return Success; }
public IList<Task> GetTaskList(Stream stream) { var list = new List<Task>(); var workbook = new XSSFWorkbook(stream); var sheet = workbook.GetSheet("个人周报"); var employeeName = sheet.GetRow(2).Cells[15].ToString(); if(employeeName==string.Empty) employeeName = sheet.GetRow(2).Cells[16].ToString(); int maxrow = sheet.LastRowNum; for (int i = 4; i < maxrow-1; i++) { var currentRow = sheet.GetRow(i); if (currentRow != null) { var cells = currentRow.Cells; var task = new Task(); task.EmpName = employeeName; foreach (var cell in cells) { switch (cell.ColumnIndex) { case 1: task.TaskNum = this.CellValueTyper(cell).ToString(); break; case 2: task.TaskName = this.CellValueTyper(cell).ToString(); break; case 3: task.ProjectNum = this.CellValueTyper(cell).ToString(); break; case 4: task.TaskType = this.CellValueTyper(cell).ToString(); break; case 5: task.InPlan = this.CellValueTyper(cell).ToString(); break; case 6: task.Description = this.CellValueTyper(cell).ToString(); break; case 7: if (cell.CellType == CellType.Blank) task.Mon = 0; else task.Mon =(double)this.CellValueTyper(cell); break; case 8: if (cell.CellType == CellType.Blank) task.Tue = 0; else task.Tue = (double)this.CellValueTyper(cell); break; case 9: if (cell.CellType == CellType.Blank) task.Wen = 0; else task.Wen = (double)this.CellValueTyper(cell); break; case 10: if (cell.CellType == CellType.Blank) task.Thr = 0; else task.Thr = (double)this.CellValueTyper(cell); break; case 11: if (cell.CellType == CellType.Blank) task.Fir = 0; else task.Fir = (double)this.CellValueTyper(cell); break; case 12: if (cell.CellType == CellType.Blank) task.San = 0; else task.San = (double)this.CellValueTyper(cell); break; case 13: if (cell.CellType == CellType.Blank) task.Sun = 0; else task.Sun = (double)this.CellValueTyper(cell); break; case 14: //if (cell.CellType == CellType.Blank) // task.Sum = 0; //else //task.Sum = (double)this.CellValueTyper(cell); task.Sum = 0; break; case 15: if (cell.CellType == CellType.Blank) task.Percent = 0.0; else task.Percent = (double)this.CellValueTyper(cell); break; case 16: task.Result = this.CellValueTyper(cell).ToString(); break; case 17: task.WillFinDate = this.CellValueTyper(cell).ToString(); break; case 18: task.WillFinMD = this.CellValueTyper(cell).ToString(); break; case 19: task.Advince = this.CellValueTyper(cell).ToString(); break; default: break; } } list.Add(task); } } return list; }
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"); }
public void TestSetForceFormulaRecalculation() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet 1"); // Set sheet.ForceFormulaRecalculation = (true); Assert.AreEqual(true, sheet.ForceFormulaRecalculation); // calcMode="manual" is unset when forceFormulaRecalculation=true CT_CalcPr calcPr = workbook.GetCTWorkbook().AddNewCalcPr(); calcPr.calcMode = (ST_CalcMode.manual); sheet.ForceFormulaRecalculation=(true); Assert.AreEqual(ST_CalcMode.auto, calcPr.calcMode); // Check sheet.ForceFormulaRecalculation = (false); Assert.AreEqual(false, sheet.ForceFormulaRecalculation); // Save, re-load, and re-check workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); sheet = (XSSFSheet)workbook.GetSheet("Sheet 1"); Assert.AreEqual(false, sheet.ForceFormulaRecalculation); }
private string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleNumDTO> hnDTOList, List<IWEHAVE.ERP.CenterBE.Deploy.HandleQuanDTO> hqDTOList, string fileName) { if (hnDTOList == null || hqDTOList == null) { return string.Empty; } string url = "http://blessing.wang/ExcelModel/model.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)); row = sheet.CreateRow(8 + hnDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("3.高端合同(英国前四所大学、博士)需在“英国前四所大学”、“博士”项点黑"); cell.CellStyle = style; sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10)); row = sheet.CreateRow(9 + hnDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("4.国内合作合同需在“国内合作”项点黑"); cell.CellStyle = style; sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10)); //导出量化 sheet = workbook.GetSheet("寄出量化"); row = sheet.GetRow(1); cell = row.GetCell(1); cell.SetCellValue(hqDTOList.Count + "个"); row = sheet.GetRow(2); cell = row.GetCell(6); cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd")); //设置单元格格式 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 < hqDTOList.Count; i++) { row = sheet.CreateRow(5 + i); cell = row.CreateCell(0); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].ContractNo); cell = row.CreateCell(1); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Student); cell = row.CreateCell(2); cell.CellStyle = style; cell.SetCellValue(""); cell = row.CreateCell(3); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].School); cell = row.CreateCell(4); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Online); cell = row.CreateCell(5); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Ps); cell = row.CreateCell(6); cell.CellStyle = style; cell.SetCellValue("□"); cell = row.CreateCell(7); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Visa); cell = row.CreateCell(8); cell.CellStyle = style; cell.SetCellValue("□"); cell = row.CreateCell(9); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].ApplicationType); cell = row.CreateCell(10); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Author); cell = row.CreateCell(11); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Note); } style = workbook.CreateCellStyle(); font = workbook.CreateFont(); font.FontName = "微软雅黑"; font.FontHeightInPoints = 9; style.SetFont(font); row = sheet.CreateRow(5 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("编制人签名:"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("经理审核签名:"); cell.CellStyle = style; cell = row.CreateCell(7); cell.SetCellValue("总经理/分管总裁批准签字:"); cell.CellStyle = style; cell = row.CreateCell(10); cell.SetCellValue("保持:人力资源中心"); cell.CellStyle = style; row = sheet.CreateRow(6 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("说明:"); cell.CellStyle = style; cell = row.CreateCell(1); cell.SetCellValue("1.高端研究生"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("5.艺术"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("9.老学生换无条件材料翻译"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("13.有拒签史学生签证(VO)"); cell.CellStyle = style; cell = row.CreateCell(10); cell.SetCellValue("17.学生签证(VO)"); cell.CellStyle = style; row = sheet.CreateRow(7 + hqDTOList.Count); cell = row.CreateCell(1); cell.SetCellValue("2.博士"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("6.其他只申请语言学校"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("10.国内合作"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("14.探亲、访友、陪读 (VO)"); cell.CellStyle = style; row = sheet.CreateRow(8 + hqDTOList.Count); cell = row.CreateCell(1); cell.SetCellValue("3.非高端研究生"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("7.个人陈述+推荐信"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("11.本科预科和研究生预科/高中"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("15.学生访问 (VO)"); cell.CellStyle = style; row = sheet.CreateRow(9 + hqDTOList.Count); cell = row.CreateCell(1); cell.SetCellValue("4.本科"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("8.单写个人陈述(推荐信)"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("12.至尊高中"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("16.结婚、学术访问、定居、团聚、工作签证 (VO)"); cell.CellStyle = style; row = sheet.CreateRow(10 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("1.文案工作进度(量化)日报单》汇总动态记录JX-16《文案工作量化日报表》;此单必须机打,手写量化将直接作废;"); cell.CellStyle = style; row = sheet.CreateRow(11 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("2.本报单请于16:00之前申报,□确认√确认填报项。请勿集中延期申报"); cell.CellStyle = style; //保存为文件 string path = HttpContext.Current.Server.MapPath("/ExportTemp/"); DirectoryInfo info = new DirectoryInfo(path); FileInfo[] files = info.GetFiles(); if (files.Length > 0) { for (int i = files.Length - 1; i >= 0; i--) { files[i].Delete(); } } fileName += ".xlsx"; FileStream file = File.Create(path + fileName); workbook.Write(file); file.Close(); stream.Close(); return "http://blessing.wang/ExportTemp/" + fileName; }
public static List<ProductLine> GetProductsData(string fileName, string fileExtension, string sheetName) { ISheet sheet; try { if (fileExtension.ToLower() == ".xls") { HSSFWorkbook hssfwb; // Create temp XLSX file using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { hssfwb = new HSSFWorkbook(fileStream); } sheet = hssfwb.GetSheet(sheetName); } else { XSSFWorkbook hssfwb; using (FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { hssfwb = new XSSFWorkbook(fileStream); } sheet = hssfwb.GetSheet(sheetName); } } finally { // Make sure temp file is deleted File.Delete(fileName); } var productLines = new List<ProductLine>(); if(sheet != null) { // DataTable dt = new DataTable(); // IRow headerRow = sheet.GetRow(0); IEnumerator rows = sheet.GetRowEnumerator(); int rowCount = sheet.LastRowNum; IRow firstRow = null; // try to get first row for (int i=0; i < 10; i++) { if (firstRow == null) firstRow = sheet.GetRow(i); else break; } TemplateEnum.ExcelFileTypeEnum templateType = TemplateEnum.ExcelFileTypeEnum.Type1; int colCount = firstRow.LastCellNum; for (int c = 0; c < colCount; c++) { ICell cell = firstRow.GetCell(c); if(cell != null) { if (cell.ToString().Contains("Код фабрики")) { templateType = TemplateEnum.ExcelFileTypeEnum.Type1; break; } else { templateType = TemplateEnum.ExcelFileTypeEnum.Type2; break; } } } bool skipReadingHeaderRow = rows.MoveNext(); if (templateType == TemplateEnum.ExcelFileTypeEnum.Type1) { bool readSupplierRow = rows.MoveNext(); IRow rowSupplier = (XSSFRow)rows.Current; ICell supplierCell = rowSupplier.GetCell(1); string supplierName = supplierCell.ToString(); skipReadingHeaderRow = rows.MoveNext(); while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; ICell cell1 = row.GetCell(1); if (cell1 != null) { if (String.IsNullOrEmpty(cell1.ToString())) // all rows was read break; else { int quantity = 0; if (row.GetCell(2) != null) Int32.TryParse(row.GetCell(2).ToString(), out quantity); decimal price = 0; if (row.GetCell(3) != null) { string priceStr = row.GetCell(3).ToString().Trim().Replace('.', ','); if(!String.IsNullOrEmpty(priceStr)) price = Convert.ToDecimal(priceStr); } decimal discount = 0; if (row.GetCell(4) != null) { string discountStr = row.GetCell(4).ToString().Trim().Replace('.', ','); if (!String.IsNullOrEmpty(discountStr)) discount = Convert.ToDecimal(discountStr); } productLines.Add( new ProductLine { ProductArticle = cell1.ToString().Trim(), Quantity = quantity, PurchasePrice = price, Discount = discount, Comment = (row.GetCell(5) == null ? String.Empty: row.GetCell(5).ToString().Trim()), SupplierCode = supplierName } ); } } } } else if (templateType == TemplateEnum.ExcelFileTypeEnum.Type2) { int emptyRowLimit = 0; Stack<ProductLine> tempProducts = new Stack<ProductLine>(); string supplierName = String.Empty; skipReadingHeaderRow = rows.MoveNext(); skipReadingHeaderRow = rows.MoveNext(); while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; ICell productArticleCell = row.GetCell(1); if (productArticleCell != null) { if (!String.IsNullOrEmpty(productArticleCell.ToString().Trim())) { emptyRowLimit = 0; // check supplierName ICell supplierNameCell = row.GetCell(0); if (supplierNameCell != null) { if (!String.IsNullOrEmpty(supplierNameCell.ToString())) supplierName = supplierNameCell.ToString(); } int quantity = GetIntCellValue(row.GetCell(2)); // Закупочная цена decimal price = GetDecimalCellValue(row.GetCell(3)); // Цена decimal salePrice = GetDecimalCellValue(row.GetCell(4)); decimal discount = GetDecimalCellValue(row.GetCell(7)); tempProducts.Push( new ProductLine { ProductArticle = productArticleCell.ToString().Trim(), Quantity = quantity, MarginAbs = salePrice -price, PurchasePrice = price, Discount = discount, SalePrice = salePrice }); } else { // dump products from stack while(tempProducts.Count > 0) { var tempProduct = tempProducts.Pop(); productLines.Add( new ProductLine { SupplierCode = supplierName, ProductArticle = tempProduct.ProductArticle, Quantity = tempProduct.Quantity, Discount = tempProduct.Discount, SalePrice = tempProduct.SalePrice, PurchasePrice = tempProduct.PurchasePrice, MarginAbs = tempProduct.MarginAbs } ); } // empty row if (emptyRowLimit > 3) break; else { supplierName = String.Empty; emptyRowLimit++; } } } } } sheet = null; return productLines; } else return null; }
private void btnProcess_Click(object sender, EventArgs e) { XSSFWorkbook wk; using (FileStream fs = File.OpenRead(txtPath.Text)) //打开myxls.xls文件 { wk = new XSSFWorkbook(fs); //把xls文件中的数据写入wk中 } ISheet sheetLead = wk.GetSheet("Lead"); if (sheetLead == null) { MessageBox.Show("Cannot find 'Lead' sheet"); return; } ISheet sheetClosedOpp = wk.GetSheet("Closed Opp"); if (sheetClosedOpp == null) { MessageBox.Show("Cannot find 'Closed Opp' sheet"); return; } ISheet sheetPipeline = wk.GetSheet("Pipeline"); if (sheetPipeline == null) { MessageBox.Show("Cannot find 'Pipeline' sheet"); return; } var leadData = GetSheetData(sheetLead); var closedOppData = GetSheetData(sheetClosedOpp); var pipelineData = GetSheetData(sheetPipeline); var verticals = new List<string>(); leadData.ForEach(l => { if (!verticals.Contains(l[0])) { verticals.Add(l[0]); } }); closedOppData.ForEach(l => { if (!verticals.Contains(l[0])) { verticals.Add(l[0]); } }); pipelineData.ForEach(l => { if (!verticals.Contains(l[0])) { verticals.Add(l[0]); } }); verticals.Add("Grand Total"); //创建工作薄 XSSFWorkbook wkNew = new XSSFWorkbook(); List<string> leadStatus = new List<string>{ "Leads", "New","Working","Accepted","Converted","Rejected" }; Dictionary<string,string> pipelineStatus = new Dictionary<string, string> { { "ClosedWon", "Closed Won" }, { "ClosedLost", "Closed Lost" }, { "ClosedWonM", "Closed Won" }, { "ClosedLostM", "Closed Lost" }, { "PipelineCreated", "" } }; //创建一个名称为mySheet的表 ISheet tb = wkNew.CreateSheet("ReferenceData"); for (int i = 0; i< verticals.Count;i++) { //创建一行,此行为第二行 for (int j = 0; j < leadStatus.Count; j++) { IRow row = tb.CreateRow(i* (leadStatus.Count+pipelineStatus.Count)+j); ICell cell = row.CreateCell(0); //在第二行中创建单元格 cell.SetCellValue(verticals[i]);//循环往第二行的单元格中添加数据 ICell cell1 = row.CreateCell(1); cell1.SetCellValue(leadStatus[j]); for (int k = 2; k < 12 + 2; k++) { int data = 0; ICell cellData = row.CreateCell(k); if (verticals[i] == "Grand Total") { if (j == 0) data = leadData.Where(L => L[1].StartsWith((k - 1).ToString() + "/")).Count(); else data = leadData.Where(L => L[1].StartsWith((k - 1).ToString() + "/") && L[2] == leadStatus[j]).Count(); cellData.SetCellValue(data); } else { if (j == 0) data = leadData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/")).Count(); else data = leadData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/") && L[2] == leadStatus[j]).Count(); cellData.SetCellValue(data); } } } for (int j = 0; j < pipelineStatus.Count;j++) { IRow row = tb.CreateRow(i * (leadStatus.Count + pipelineStatus.Count)+leadStatus.Count + j); ICell cell = row.CreateCell(0); //在第二行中创建单元格 cell.SetCellValue(verticals[i]);//循环往第二行的单元格中添加数据 ICell cell1 = row.CreateCell(1); cell1.SetCellValue(pipelineStatus.ElementAt(j).Key); for (int k = 2; k < 12 + 2; k++) { ICell cellData = row.CreateCell(k); if (j == 0 || j == 1) { if(verticals[i]=="Grand Total") { int data = closedOppData.Where(L=> L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Count(); cellData.SetCellValue(data); } else { int data = closedOppData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Count(); cellData.SetCellValue(data); } } else if (j == 2 || j == 3) { if (verticals[i] == "Grand Total") { double data = closedOppData.Where(L => L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Sum(L => { double ret = 0.0; double.TryParse(L[4], out ret); return ret; }); cellData.SetCellValue(data); } else { double data = closedOppData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/") && L[2] == pipelineStatus.ElementAt(j).Value).Sum(L => { double ret = 0.0; double.TryParse(L[4], out ret); return ret; }); cellData.SetCellValue(data); } } else { if (verticals[i] == "Grand Total") { double data = pipelineData.Where(L => L[1].StartsWith((k - 1).ToString() + "/")).Sum(L => { double ret = 0.0; double.TryParse(L[4], out ret); return ret; }); cellData.SetCellValue(data); } else { double data = pipelineData.Where(L => L[0] == verticals[i] && L[1].StartsWith((k - 1).ToString() + "/")).Sum(L => { double ret = 0.0; double.TryParse(L[4], out ret); return ret; }); cellData.SetCellValue(data); } } } } } using (FileStream fs1 = File.OpenWrite(Application.StartupPath+@"/myxls.xlsx")) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件! { wkNew.Write(fs1); //向打开的这个xls文件中写入mySheet表并保存。 MessageBox.Show("提示:创建成功!"); } }
private string ImportStudentPointExcel(HttpPostedFile postedFile, string schoolNo, string banjiName, string examTime, string examType, string examName, List<StudentPointModel> newstudentPoints) { try { var ext = Path.GetExtension(postedFile.FileName); ISheet sheet; if (ext == ".xls") { //office 97-2003 HSSFWorkbook wk = new HSSFWorkbook(postedFile.InputStream); sheet = wk.GetSheet(wk.GetSheetName(0)); } else if (ext == ".xlsx") { //office 2007-now XSSFWorkbook wk = new XSSFWorkbook(postedFile.InputStream); sheet = wk.GetSheet(wk.GetSheetName(0)); } else { return "请使用xls或xlsx格式"; } if (sheet.LastRowNum < 1) { return "表内容不能为空"; } Dictionary<string, int> rowName = new Dictionary<string, int>(); IList<QueryModel> qmList = new List<QueryModel>(); qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo)); if (banjiName != "") { qmList.Add(MakeUtil.getQueryModel("D_Name", "'", SqlWhere.WhereOperator.Equal, banjiName)); } var students = _departStaffEbi.getModelList(qmList, -1); qmList.Clear(); qmList.Add(MakeUtil.getQueryModel("SchoolNo", "'", SqlWhere.WhereOperator.Equal, schoolNo)); if (banjiName != "") { qmList.Add(MakeUtil.getQueryModel("BanjiName", "'", SqlWhere.WhereOperator.Equal, banjiName)); } qmList.Add(MakeUtil.getQueryModel("ExamType", "'", SqlWhere.WhereOperator.Equal, examType)); qmList.Add(MakeUtil.getQueryModel("ExamName", "'", SqlWhere.WhereOperator.Equal, examName)); var studentpoints = _studentPointEbi.getModelList(qmList, -1); // if (sheet.LastRowNum > students.Count) // { // return "Excel的导入内容比学生人数多"; // } for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数 { IRow row = sheet.GetRow(j); //读取当前行数据 //先读取表头信息 if (j == 0) { if (row != null) { for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数 { ICell cell = row.GetCell(k); //当前表格 if (cell != null && cell.ToString() != "") { //获取表头信息 rowName.Add(cell.ToString(), k); } } } } else { if (row != null) { var banji = row.GetCell(rowName["班级名称"]).ToString(); var xinming = row.GetCell(rowName["姓名"]).ToString(); var bianhao = row.GetCell(rowName["编号"]).ToString(); if (banjiName != "" && banji != banjiName) { return string.Format("Excel中的班级名称有误,你选择了 {0} 这个班级,Excel中不应该再出现别的班级", banjiName); } //根据前四项找出学生的成绩资料 if (!students.Any(p => p.D_Name == banji && p.DS_Name == xinming && p.DS_JID == bianhao)) { return string.Format("在班级{2}中找不到姓名为{0}编号为{1}的学生", xinming, bianhao, banji); } //然后在excel表中如果有相关资料的话便导入 var studentpointList = studentpoints.Where(p => p.BanjiName == banji && p.XinMing == xinming && p.BianHao == bianhao); if (studentpointList.Any()) { var studentpoint = studentpointList.FirstOrDefault(); if (studentpoint.TempIsOld) { continue; } SetCellToStudentPoint(studentpoint, row, rowName); studentpoint.TempIsOld = true; newstudentPoints.Add(studentpoint); } else { var studentpoint = new StudentPointModel(); studentpoint.SchoolNo = schoolNo; studentpoint.BanjiName = banji; studentpoint.XinMing = xinming; studentpoint.BianHao = bianhao; studentpoint.ExamType = examType; studentpoint.ExamName = examName; studentpoint.ExamTime = Convert.ToDateTime(examTime); SetCellToStudentPoint(studentpoint, row, rowName); newstudentPoints.Add(studentpoint); } } } } if (newstudentPoints.Any()) { _studentPointEbi.add(newstudentPoints); } return "OK"; } catch (Exception ex) { return "Excel格式不正确," + ex.Message; } catch { return "Excel格式不正确"; } }
public string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleQuanDTO> hqDTOList, string fileName) { if (hqDTOList == null) { return string.Empty; } string url = "http://blessing.wang/ExcelModel/quanModel.xlsx"; WebRequest request = WebRequest.Create(url); WebResponse response = request.GetResponse(); Stream stream = response.GetResponseStream(); XSSFWorkbook workbook = new XSSFWorkbook(stream); ISheet sheet = workbook.GetSheet("�ij�����"); IRow row = sheet.GetRow(1); ICell cell = row.GetCell(1); cell.SetCellValue(hqDTOList.Count + "��"); row = sheet.GetRow(2); cell = row.GetCell(6); 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 < hqDTOList.Count; i++) { row = sheet.CreateRow(5 + i); cell = row.CreateCell(0); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].ContractNo); cell = row.CreateCell(1); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Student); cell = row.CreateCell(2); cell.CellStyle = style; cell.SetCellValue(""); cell = row.CreateCell(3); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].School); cell = row.CreateCell(4); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Online); cell = row.CreateCell(5); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Ps); cell = row.CreateCell(6); cell.CellStyle = style; cell.SetCellValue("��"); cell = row.CreateCell(7); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Visa); cell = row.CreateCell(8); cell.CellStyle = style; cell.SetCellValue("��"); cell = row.CreateCell(9); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].ApplicationType); cell = row.CreateCell(10); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Author); cell = row.CreateCell(11); cell.CellStyle = style; cell.SetCellValue(hqDTOList[i].Note); } style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); font.FontName = "���ź�"; font.FontHeightInPoints = 9; style.SetFont(font); row = sheet.CreateRow(5 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("������ǩ��:"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("�������ǩ����"); cell.CellStyle = style; cell = row.CreateCell(7); cell.SetCellValue("�ܾ���/�ֹ��ܲ���ǩ�֣�"); cell.CellStyle = style; cell = row.CreateCell(10); cell.SetCellValue("���֣�������Դ����"); cell.CellStyle = style; row = sheet.CreateRow(6 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("˵����"); cell.CellStyle = style; cell = row.CreateCell(1); cell.SetCellValue("1.�߶��о���"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("5.����"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("9.��ѧ�������������Ϸ���"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("13.�о�ǩʷѧ��ǩ֤��VO��"); cell.CellStyle = style; cell = row.CreateCell(10); cell.SetCellValue("17.ѧ��ǩ֤��VO)"); cell.CellStyle = style; row = sheet.CreateRow(7 + hqDTOList.Count); cell = row.CreateCell(1); cell.SetCellValue("2.��ʿ"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("6.����ֻ��������ѧУ"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("10.���ں���"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("14.̽�ס����ѡ���� (VO)"); cell.CellStyle = style; row = sheet.CreateRow(8 + hqDTOList.Count); cell = row.CreateCell(1); cell.SetCellValue("3.�Ǹ߶��о���"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("7.���˳���+�Ƽ���"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("11.����Ԥ�ƺ��о���Ԥ��/����"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("15.ѧ������ (VO)"); cell.CellStyle = style; row = sheet.CreateRow(9 + hqDTOList.Count); cell = row.CreateCell(1); cell.SetCellValue("4.����"); cell.CellStyle = style; cell = row.CreateCell(3); cell.SetCellValue("8.��д���˳������Ƽ��ţ�"); cell.CellStyle = style; cell = row.CreateCell(5); cell.SetCellValue("12.�������"); cell.CellStyle = style; cell = row.CreateCell(8); cell.SetCellValue("16.��顢ѧ�����ʡ����ӡ��žۡ�����ǩ֤ (VO)"); cell.CellStyle = style; row = sheet.CreateRow(10 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("1���İ��������ȣ��������ձ��������ܶ�̬��¼JX-16���İ����������ձ�������˵����������д������ֱ�����ϣ�"); cell.CellStyle = style; row = sheet.CreateRow(11 + hqDTOList.Count); cell = row.CreateCell(0); cell.SetCellValue("2������������16:00֮ǰ�걨����ȷ�ϡ�ȷ���������������걨"); cell.CellStyle = style; string path = HttpContext.Current.Server.MapPath("/ExportTemp/"); DirectoryInfo info = new DirectoryInfo(path); FileInfo[] files = info.GetFiles(); if (files.Length > 0) { for (int i = files.Length - 1; i >= 0; i--) { files[i].Delete(); } } fileName += ".xlsx"; FileStream file = File.Create(path + fileName); workbook.Write(file); file.Close(); stream.Close(); return "http://blessing.wang/ExportTemp/" + fileName; }