//[Obsolete] //public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet, // HSSFWorkbook workbook) //{ // SetBorderBottom(border, toCRA(region), sheet, workbook); //} /// <summary> /// Sets the borderBottom attribute of the HSSFRegionUtil object /// </summary> /// <param name="border">The new border</param> /// <param name="region">The region that should have the border</param> /// <param name="sheet">The sheet that the region is on.</param> /// <param name="workbook">The workbook that the region is on.</param> public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook workbook) { int colStart = region.FirstColumn; int colEnd = region.LastColumn; int rowIndex = region.LastRow; CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_BOTTOM, (int)border); NPOI.SS.UserModel.Row row = HSSFCellUtil.GetRow(rowIndex, sheet); for (int i = colStart; i <= colEnd; i++) { cps.SetProperty(row, i); } }
public static CSException <List <TH_HAZALOCA> > BuildListByNPOISheet(NPOI.HSSF.UserModel.HSSFSheet HSSFSheet) { try { var db = new SAFEDB(); var rowcount = HSSFSheet.LastRowNum + 1; var hazaList = new List <TH_HAZALOCA>(); for (var i = 0; i < rowcount; i++) { var row = HSSFSheet.GetRow(i); var deptCode = row.Cells[0].StringCellValue; var locaName = row.Cells[1].StringCellValue; var newLoca = new TH_HAZALOCA(locaName, deptCode); var allowed = newLoca.IsAllowed(); if (!allowed.Flag) { db.TH_HAZALOCA.Add(newLoca); db.SaveChanges(); } else { throw new Exception(allowed.Msg); } } return(new CSException <List <TH_HAZALOCA> >(true, rowcount.ToString(), hazaList)); } catch (Exception ex) { return(new CSException <List <TH_HAZALOCA> >(ex.Message, new List <TH_HAZALOCA>())); } }
protected void GSD_btn_Click(object sender, EventArgs e) { try { this.workbook = new HSSFWorkbook(PYS_FileUpload.FileContent); this.u_sheet = (HSSFSheet)workbook.GetSheetAt(0); //取得第0個Sheet //不同於Microsoft Object Model,NPOI都是從索引0開始算起 //從第一個Worksheet讀資料 SaveOrInsertSheet(this.u_sheet); ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入完成", "alert('匯入完成');", true); } catch (Exception) { ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入失敗", "alert('匯入失敗');", true); } finally { //釋放 NPOI的資源 if (this.workbook != null) this.workbook = null; if (this.u_sheet != null) this.u_sheet = null; //是否刪除Server上的Excel檔(預設true) /*bool isDeleteFileFromServer = false; if (isDeleteFileFromServer) { System.IO.File.Delete(excel_filePath); }*/ GC.Collect(); } }
private ExcelSheet ResolveSheet(Npoi.HSSFSheet native) { if (!_sheets.ContainsKey(native)) { _sheets.Add(native, new NpoiExcelSheet(native)); } return(_sheets[native]); }
/// <summary> /// Creates new HSSFRow from scratch. Only HSSFSheet should do this. /// </summary> /// <param name="book">low-level Workbook object containing the sheet that Contains this row</param> /// <param name="sheet">low-level Sheet object that Contains this Row</param> /// <param name="rowNum">the row number of this row (0 based)</param> ///<see cref="NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/> public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, int rowNum) { this.rowNum = rowNum; this.book = book; this.sheet = sheet; row = new RowRecord(rowNum); RowNum=(rowNum); }
/// <summary> /// Creates the patriarch. /// </summary> /// <param name="sheet">the sheet this patriarch is stored in.</param> /// <param name="boundAggregate">The bound aggregate.</param> public HSSFPatriarch(HSSFSheet sheet, EscherAggregate boundAggregate) { _boundAggregate = boundAggregate; _sheet = sheet; _mainSpgrContainer = _boundAggregate.GetEscherContainer().ChildContainers[0]; EscherContainerRecord spContainer = (EscherContainerRecord)_boundAggregate.GetEscherContainer() .ChildContainers[0].GetChild(0); _spgrRecord = (EscherSpgrRecord)spContainer.GetChildById(EscherSpgrRecord.RECORD_ID); BuildShapeTree(); }
private void CreateFilterModeRecord(HSSFSheet sheet,int insertPos) { //look for the FilterModeRecord NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(FilterModeRecord.sid); // this local variable hides the class one: FilterModeRecord filtermode; //if not found, add a new one if (record == null) { filtermode = new FilterModeRecord(); sheet.Sheet.Records.Insert(insertPos, filtermode); } }
public CellEvaluationFrame(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum) { if (workbook == null) { throw new ArgumentException("workbook must not be null"); } if (sheet == null) { throw new ArgumentException("sheet must not be null"); } _workbook = workbook; _sheet = sheet; _srcRowNum = srcRowNum; _srcColNum = srcColNum; }
public ExcalHelper(String sheetName, String[] rowTitle, int[] rowWidth, int dataRowNum) { //创建标题行 workbook = new HSSFWorkbook(); sheet = (HSSFSheet)workbook.CreateSheet(sheetName); this.dataRowNum = dataRowNum; HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < rowTitle.Length; i++) { //设置列宽 sheet.SetColumnWidth(i, rowWidth[i] * 255); rowtitle.CreateCell(i).SetCellValue(rowTitle[i]); } }
private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg) { //look for the AutoFilterInfo Record NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid); AutoFilterInfoRecord info; if (record == null) { info = new AutoFilterInfoRecord(); sheet.Sheet.Records.Insert(insertPos, info); } else { info = record as AutoFilterInfoRecord; } info.NumEntries = (short)(ptg.LastColumn - ptg.FirstColumn + 1); }
/** * Creates a map (i.e. two-dimensional array) filled with ranges. Allow fast * retrieving {@link CellRangeAddress} of any cell, if cell is contained in * range. * * @see #getMergedRange(CellRangeAddress[][], int, int) */ public static CellRangeAddress[][] BuildMergedRangesMap(HSSFSheet sheet) { CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][]; for ( int m = 0; m < sheet.NumMergedRegions; m++ ) { CellRangeAddress cellRangeAddress = sheet.GetMergedRegion( m ); int requiredHeight = cellRangeAddress.LastRow + 1; if ( mergedRanges.Length < requiredHeight ) { CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][]; Array.Copy( mergedRanges, 0, newArray, 0, mergedRanges.Length ); mergedRanges = newArray; } for ( int r = cellRangeAddress.FirstRow; r <= cellRangeAddress.LastRow; r++ ) { int requiredWidth = cellRangeAddress.LastColumn + 1; CellRangeAddress[] rowMerged = mergedRanges[r]; if ( rowMerged == null ) { rowMerged = new CellRangeAddress[requiredWidth]; mergedRanges[r] = rowMerged; } else { int rowMergedLength = rowMerged.Length; if ( rowMergedLength < requiredWidth ) { CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth]; Array.Copy(rowMerged, 0, newRow, 0,rowMergedLength ); mergedRanges[r] = newRow; rowMerged = newRow; } } //Arrays.Fill( rowMerged, cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn + 1, cellRangeAddress ); for (int i = cellRangeAddress.FirstColumn; i < cellRangeAddress.LastColumn + 1; i++) { rowMerged[i] = cellRangeAddress; } } } return mergedRanges; }
public static HSSFPatriarch CreatePatriarch(HSSFPatriarch patriarch, HSSFSheet sheet) { HSSFPatriarch newPatriarch = new HSSFPatriarch(sheet, new EscherAggregate(true)); newPatriarch.AfterCreate(); foreach (HSSFShape shape in patriarch.Children) { HSSFShape newShape; if (shape is HSSFShapeGroup) { newShape = ((HSSFShapeGroup)shape).CloneShape(newPatriarch); } else { newShape = shape.CloneShape(); } newPatriarch.OnCreate(newShape); newPatriarch.AddShape(newShape); } return newPatriarch; }
private void SaveOrInsertSheet_Source(HSSFSheet u_sheet) { Database.MSSQL DB = new Database.MSSQL("Web"); List<SqlParameter> PMS = new List<SqlParameter>(); //因為要讀取的資料列不包含標頭,所以i從u_sheet.FirstRowNum + 1開始讀 /*一列一列地讀取資料*/ int Company_check = 0; for (int i = u_sheet.FirstRowNum + 1; i <= u_sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)u_sheet.GetRow(i);//取得目前的資料列 2015/03/18[新增資料區段] for (int j = 0; j < Stock_Num.Rows.Count; j++) { if (Equals(Stock_Num.Rows[j][0], row.GetCell(0).ToString())) { Company_check = 1; } } if (Company_check == 1) { PMS = new List<SqlParameter>(); PMS.Add(new SqlParameter("@證券代號", row.GetCell(0).ToString())); PMS.Add(new SqlParameter("@成交股數", Convert.ToInt32(row.GetCell(2).ToString()))); PMS.Add(new SqlParameter("@成交筆數", Convert.ToInt32(row.GetCell(3).ToString()))); PMS.Add(new SqlParameter("@成交金額", Convert.ToSingle(row.GetCell(4).ToString()))); PMS.Add(new SqlParameter("@開盤價", Convert.ToSingle(row.GetCell(5).ToString()))); PMS.Add(new SqlParameter("@最高價", Convert.ToSingle(row.GetCell(6).ToString()))); PMS.Add(new SqlParameter("@最低價", Convert.ToSingle(row.GetCell(7).ToString()))); PMS.Add(new SqlParameter("@收盤價", Convert.ToSingle(row.GetCell(8).ToString()))); PMS.Add(new SqlParameter("@最後揭示買價", Convert.ToSingle(row.GetCell(11).ToString()))); PMS.Add(new SqlParameter("@最後揭示買量", Convert.ToInt32(row.GetCell(12).ToString()))); PMS.Add(new SqlParameter("@最後揭示賣價", Convert.ToSingle(row.GetCell(13).ToString()))); PMS.Add(new SqlParameter("@最後揭示賣量", Convert.ToInt32(row.GetCell(14).ToString()))); PMS.Add(new SqlParameter("@資料日期", Convert.ToDateTime(DataTime_Source_txt.Text))); PMS.Add(new SqlParameter("@下載日期", dt)); DB.ExecutionStoredProcedure("[Source_Insert]", PMS.ToArray()); } Company_check = 0; } }
/** * Notifies this evaluation tracker that the evaluation of the specified * cell Is complete. <p/> * * Every successful call to <tt>startEvaluate</tt> must be followed by a * call to <tt>endEvaluate</tt> (recommended in a finally block) to enable * proper tracking of which cells are being evaluated at any point in time.<p/> * * Assuming a well behaved client, parameters to this method would not be * required. However, they have been included to assert correct behaviour, * and form more meaningful error messages. */ public void EndEvaluate(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum) { int nFrames = _evaluationFrames.Count; if (nFrames < 1) { throw new InvalidOperationException("Call to endEvaluate without matching call to startEvaluate"); } nFrames--; CellEvaluationFrame cefExpected = (CellEvaluationFrame)_evaluationFrames[nFrames]; CellEvaluationFrame cefActual = new CellEvaluationFrame(workbook, sheet, srcRowNum, srcColNum); if (!cefActual.Equals(cefExpected)) { throw new Exception("Wrong cell specified. " + "Corresponding startEvaluate() call was for cell {" + cefExpected.FormatAsString() + "} this endEvaluate() call Is for cell {" + cefActual.FormatAsString() + "}"); } // else - no problems so pop current frame _evaluationFrames.Remove(nFrames); }
/// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable Import(string strFileName) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } NPOI.HSSF.UserModel.HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet; System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = sheet.GetRow(0) as HSSFRow; int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { HSSFCell cell = headerRow.GetCell(j) as HSSFCell; dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i) as HSSFRow; DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dataRow); } return(dt); }
/** * @return aggregate info or null if the sheet does not contain Drawing objects */ internal static DrawingAggregateInfo Get(HSSFSheet sheet) { DrawingAggregateInfo info = null; InternalSheet isheet = HSSFTestHelper.GetSheetForTest(sheet); List<RecordBase> records = isheet.Records; for (int i = 0; i < records.Count; i++) { RecordBase rb = records[(i)]; if ((rb is DrawingRecord) && info == null) { info = new DrawingAggregateInfo(); info.startRecordIndex = i; info.endRecordIndex = i; } else if (info != null && ( rb is DrawingRecord || rb is ObjRecord || rb is TextObjectRecord || rb is ContinueRecord || rb is NoteRecord )) { info.endRecordIndex = i; } else { if (rb is EscherAggregate) throw new InvalidOperationException("Drawing data already aggregated. " + "You should cal this method before the first invocation of HSSFSheet#getDrawingPatriarch()"); if (info != null) break; } } if (info != null) { info.aggRecords = new List<RecordBase>( records.GetRange(info.startRecordIndex, info.endRecordIndex + 1)); } return info; }
protected void Source_btn_Click(object sender, EventArgs e) { //try //{ this.workbook = new HSSFWorkbook(Source_FileUpload.FileContent); this.u_sheet = (HSSFSheet)workbook.GetSheetAt(0); //取得第0個Sheet //不同於Microsoft Object Model,NPOI都是從索引0開始算起 //從第一個Worksheet讀資料 SaveOrInsertSheet_Source(this.u_sheet); ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入完成", "alert('匯入完成');", true); //} //catch (Exception) //{ // ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入失敗", "alert('匯入失敗');", true); //} //finally //{ //釋放 NPOI的資源 if (this.workbook != null) this.workbook = null; if (this.u_sheet != null) this.u_sheet = null; GC.Collect(); //} }
private void SaveOrInsertSheet(HSSFSheet u_sheet) { Database.MSSQL DB = new Database.MSSQL("Web"); List<SqlParameter> PMS = new List<SqlParameter>(); //因為要讀取的資料列不包含標頭,所以i從u_sheet.FirstRowNum + 1開始讀 /*一列一列地讀取資料*/ int Company_check = 0; for (int i = u_sheet.FirstRowNum + 1; i <= u_sheet.LastRowNum; i++) { HSSFRow row = (HSSFRow)u_sheet.GetRow(i);//取得目前的資料列 2015/03/18[新增資料區段] for (int j = 0; j < Stock_Num.Rows.Count; j++ ) { if (Equals(Stock_Num.Rows[j][0], row.GetCell(0).ToString())) { Company_check = 1; } } if (Company_check == 0) { PMS = new List<SqlParameter>(); PMS.Add(new SqlParameter("@C_ID", row.GetCell(0).ToString())); PMS.Add(new SqlParameter("@C_Name", row.GetCell(1).ToString())); DB.ExecutionStoredProcedure("[GetStockNum_Insert]", PMS.ToArray()); } PMS = new List<SqlParameter>(); PMS.Add(new SqlParameter("@證券代號", row.GetCell(0).ToString())); PMS.Add(new SqlParameter("@本益比", Convert.ToSingle(row.GetCell(2).ToString()))); PMS.Add(new SqlParameter("@殖利率", Convert.ToSingle(row.GetCell(3).ToString()))); PMS.Add(new SqlParameter("@股價淨值比", Convert.ToSingle(row.GetCell(4).ToString()))); PMS.Add(new SqlParameter("@資料日期", Convert.ToDateTime(DataTime_PYS_txt.Text))); PMS.Add(new SqlParameter("@下載日期", dt)); DB.ExecutionStoredProcedure("[PYS_Insert]", PMS.ToArray()); Company_check = 0; } }
/// <summary> /// Calculates the height of a client anchor in points. /// </summary> /// <param name="sheet">the sheet the anchor will be attached to</param> /// <returns>the shape height.</returns> public float GetAnchorHeightInPoints(HSSFSheet sheet) { int y1 = Dy1; int y2 = Dy2; int row1 = Math.Min(Row1, Row2); int row2 = Math.Max(Row1, Row2); float points = 0; if (row1 == row2) { points = ((y2 - y1) / 256.0f) * GetRowHeightInPoints(sheet, row2); } else { points += ((256.0f - y1) / 256.0f) * GetRowHeightInPoints(sheet, row1); for (int i = row1 + 1; i < row2; i++) { points += GetRowHeightInPoints(sheet, i); } points += (y2 / 256.0f) * GetRowHeightInPoints(sheet, row2); } return points; }
/// <summary>Get a worksheet by name.</summary> /// <param name="name">Name of the desired sheet.</param> /// <returns>A facade representing the sheet.</returns> public override ExcelSheet GetSheet(string name) { Npoi.HSSFSheet native = (Npoi.HSSFSheet) this._workbook.GetSheet(name); return(ResolveSheet(native)); }
/** * Returns all the charts for the given sheet. * * NOTE: You won't be able to do very much with * these charts yet, as this is very limited support */ public static HSSFChart[] GetSheetCharts(HSSFSheet sheet) { List<HSSFChart> charts = new List<HSSFChart>(); HSSFChart lastChart = null; HSSFSeries lastSeries = null; // Find records of interest IList records = sheet.Sheet.Records; foreach (RecordBase r in records) { if (r is ChartRecord) { lastSeries = null; lastChart = new HSSFChart(sheet, (ChartRecord)r); charts.Add(lastChart); } else if (r is LegendRecord) { lastChart.legendRecord = (LegendRecord)r; } else if (r is SeriesRecord) { HSSFSeries series = new HSSFSeries((SeriesRecord)r); lastChart.series.Add(series); lastSeries = series; } else if (r is AlRunsRecord) { lastChart.chartTitleFormat = (AlRunsRecord)r; } else if (r is SeriesTextRecord) { // Applies to a series, unless we've seen // a legend already SeriesTextRecord str = (SeriesTextRecord)r; if (lastChart.legendRecord == null && lastChart.series.Count > 0) { HSSFSeries series = (HSSFSeries) lastChart.series[lastChart.series.Count - 1]; series.seriesTitleText = str; } else { lastChart.chartTitleText = str; } } else if (r is BRAIRecord) { BRAIRecord linkedDataRecord = (BRAIRecord)r; if (lastSeries != null) { lastSeries.InsertData(linkedDataRecord); } } else if (r is ValueRangeRecord) { lastChart.valueRanges.Add((ValueRangeRecord)r); } else if (r is Record) { if (lastChart != null) { Record record = (Record)r; foreach (int type in Enum.GetValues(typeof(HSSFChartType))) { if (type == 0) { continue; } if (record.Sid == type) { lastChart.type = (HSSFChartType)type; break; } } } } } return (HSSFChart[]) charts.ToArray(); }
/** * TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea * this call will produce the wrong results if the formula contains any cell references * One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int) * Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects. * * @return <code>null</code> if <c>formula</c> was null. */ private static Ptg[] ParseFormula(String formula, HSSFSheet sheet) { if (formula == null) { return null; } int sheetIndex = sheet.Workbook.GetSheetIndex(sheet); return HSSFFormulaParser.Parse(formula, (HSSFWorkbook)sheet.Workbook, FormulaType.Cell, sheetIndex); }
public ActionResult ExportReportExcel(DateTime selected_date) { String company_name = "DANGOTE CEMENT"; List <view_message_dto> _sms_list = TempData["sms_list"] as List <view_message_dto>; // Opening the Excel template... FileStream fs = new FileStream(Server.MapPath(@"\Content\template\reporting_sms_List_Selected.xls"), FileMode.Open, FileAccess.Read); MemoryStream ms = new MemoryStream(); // Getting the complete workbook... HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true); try { // Getting the worksheet by its name... NPOI.HSSF.UserModel.HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("SmsList"); //RO's Name NPOI.HSSF.UserModel.HSSFRow dataRow = (HSSFRow)sheet.GetRow(3); ICell cell_default = dataRow.Cells[0]; ICellStyle boldStyle = cell_default.CellStyle; dataRow = (HSSFRow)sheet.GetRow(0); dataRow.Cells[0].SetCellValue(company_name); int Row = 3; foreach (view_message_dto item in _sms_list) { dataRow = (HSSFRow)sheet.CreateRow(Row); // dataRow.CreateCell(0).SetCellValue(Row - 2); dataRow.CreateCell(1).SetCellValue(item.atc_no); dataRow.CreateCell(2).SetCellValue(item.delivery_no); dataRow.CreateCell(3).SetCellValue(item.customer_no); dataRow.CreateCell(4).SetCellValue(item.phone_no); dataRow.CreateCell(5).SetCellValue(item.waybill_no); dataRow.CreateCell(6).SetCellValue(item.customer_name); dataRow.CreateCell(7).SetCellValue(item.ip_address); dataRow.CreateCell(8).SetCellValue(item.code_a); dataRow.CreateCell(9).SetCellValue(item.code_b); dataRow.CreateCell(10).SetCellValue(item.sms_status); dataRow.CreateCell(11).SetCellValue(item.response); dataRow.CreateCell(12).SetCellValue(item.create_datetime.ToString()); dataRow.CreateCell(13).SetCellValue(item.response_datetime.ToString()); if (Row % 2 == 0) { for (int i = 0; i < 5; i++) { ICell cell = dataRow.Cells[i]; cell.CellStyle = boldStyle; } } Row++; } sheet.ForceFormulaRecalculation = true; TempData["Message"] = "Excel report created successfully!"; } catch (Exception ex) { TempData["Message"] = "Oops! Something went wrong." + "<br/>" + ex.Message.ToString(); } String FileName = "sms_list_report_" + selected_date.ToString("-yyyy-MM-dd") + ".xls"; // Writing the workbook content to the FileStream... templateWorkbook.Write(ms); return(File(ms.ToArray(), "application/vnd.ms-excel", FileName)); }
public static CFRuleRecord Create(HSSFSheet sheet, String formulaText) { Ptg[] formula1 = ParseFormula(formulaText, sheet); return new CFRuleRecord(CONDITION_TYPE_FORMULA, ComparisonOperator.NoComparison, formula1, null); }
/** * Creates a new comparison operation rule */ public static CFRuleRecord Create(HSSFSheet sheet, byte comparisonOperation, String formulaText1, String formulaText2) { Ptg[] formula1 = ParseFormula(formulaText1, sheet); Ptg[] formula2 = ParseFormula(formulaText2, sheet); return new CFRuleRecord(CONDITION_TYPE_CELL_VALUE_IS, (ComparisonOperator)comparisonOperation, formula1, formula2); }
private static string MakeFileExcelSample(CMixExcel mixExcel, ref int sheetsNumber) { string result = string.Empty; Stream _stream = null; string fileNameTail = mixExcel.PathFile.Substring(mixExcel.PathFile.LastIndexOf('.')); string samplefileName = mixExcel.PathFile.Substring(0, mixExcel.PathFile.LastIndexOf('.')); string pathsamplefileName = String.Format("{0}_Sample{1}", samplefileName, fileNameTail); FileStream fstream = File.Open(pathsamplefileName, FileMode.OpenOrCreate); fstream.Close(); File.Copy(mixExcel.PathFile, pathsamplefileName, true); CMixExcel mixExcelSample = new CMixExcel(pathsamplefileName); if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage) { #region Excel 2007++ ExcelPackage excelSamplePackage = (ExcelPackage)mixExcelSample.ExcelMixCore; ExcelPackage excelPackage = (ExcelPackage)mixExcel.ExcelMixCore; ExcelWorksheets workSheetsSample = excelSamplePackage.Workbook.Worksheets; ExcelWorksheets workSheets = excelPackage.Workbook.Worksheets; sheetsNumber = workSheetsSample.Count; for (int i = 1; i <= workSheetsSample.Count; i++) { var worksheetSample = workSheetsSample[i]; var worksheet = workSheets[i]; System.Xml.XmlNodeList test = worksheetSample.WorksheetXml.GetElementsByTagName("dimension"); if (test != null && test.Count > 0) { System.Xml.XmlNode value = test[0]; //<dimension ref="A1:J17" /> string outerXml = value.OuterXml.Substring("<dimension ref=\"".Length); string rangesize = outerXml.Substring(0, outerXml.IndexOf('"')); if (rangesize != "A1") { string[] arrayRange = rangesize.Split(':'); int indextemp = 0; int temp; string MinRow = string.Empty, MaxRow = string.Empty; int MinColumn = 0, MaxColumn = 0; string range1 = arrayRange[0]; while (!int.TryParse(range1[indextemp].ToString(), out temp) && indextemp < range1.Length) { MinColumn += (System.Text.Encoding.ASCII.GetBytes(range1[indextemp].ToString())[0] - 64); indextemp++; } MinRow = range1.Substring(indextemp); indextemp = 0; string range2 = arrayRange[1]; while (!int.TryParse(range2[indextemp].ToString(), out temp) && indextemp < range2.Length) { MaxColumn += (System.Text.Encoding.ASCII.GetBytes(range2[indextemp].ToString())[0] - 64); indextemp++; } MaxRow = range2.Substring(indextemp); if (!string.IsNullOrEmpty(MinRow) && MinColumn > 0 && !string.IsNullOrEmpty(MaxRow) && MaxColumn > 0) { int intMinRow = int.Parse(MinRow); int intMaxRow = int.Parse(MaxRow); for (int r = intMinRow; r <= intMaxRow; r++) { for (int c = MinColumn; c <= MaxColumn + 1; c++) { if (c > MinColumn) { worksheetSample.Cells[r, c].Value = worksheet.Cells[r, c - 1].Value; } else if (r == intMinRow) { worksheetSample.Cells[r, c].Value = "Heading"; } else if (r == intMinRow + 1) { worksheetSample.Cells[r, c].Value = "Schema"; } else { worksheetSample.Cells[r, c].Value = string.Empty; } } } } }//(rangesize != "A1") } } excelSamplePackage.Save(); _stream = excelSamplePackage.Stream; #endregion } else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook) { #region Excel 2003-- HSSFWorkbook hssWorkbookSample = (HSSFWorkbook)mixExcelSample.ExcelMixCore; HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore; sheetsNumber = hssWorkbookSample.NumberOfSheets; for (int i = 0; i < hssWorkbookSample.NumberOfSheets; i++) { NPOI.HSSF.UserModel.HSSFSheet excelSheetSample = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbookSample.GetSheetAt(i); NPOI.HSSF.UserModel.HSSFSheet excelSheet = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbook.GetSheetAt(i); NPOI.HSSF.Record.DimensionsRecord sheetDementionSample = excelSheetSample.Sheet.Dimensions; NPOI.HSSF.Record.DimensionsRecord sheetDemention = excelSheet.Sheet.Dimensions; for (int r = sheetDementionSample.FirstRow; r <= sheetDementionSample.LastRow; r++) { IRow rowSample = excelSheetSample.GetRow(r); if (rowSample != null) { excelSheetSample.RemoveRow(rowSample); } } for (int r = sheetDemention.FirstRow; r <= sheetDemention.LastRow; r++) { int maxCol = sheetDementionSample.LastCol; int minCol = sheetDementionSample.FirstCol; for (int c = minCol; c <= maxCol + 1; c++) { try { IRow rowSample = excelSheetSample.GetRow(r); IRow row = excelSheet.GetRow(r); if (row != null) { ICell excelSheetGetRowGetCellSample = rowSample.GetCell(c); if (excelSheetGetRowGetCellSample == null) { excelSheetGetRowGetCellSample = rowSample.CreateCell(c); } if (c > sheetDementionSample.FirstCol) { ICell excelSheetGetRowGetCell = row.GetCell(c - 1); if (excelSheetGetRowGetCell != null) { switch (excelSheetGetRowGetCell.CellType) { case CellType.String: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue); break; case CellType.Numeric: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue); break; case CellType.Boolean: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue); break; case CellType.Blank: excelSheetGetRowGetCellSample.SetCellValue(string.Empty); break; case CellType.Error: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue); break; case CellType.Formula: { switch (excelSheetGetRowGetCellSample.CachedFormulaResultType) { case CellType.Numeric: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue); break; case CellType.String: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue); break; case CellType.Boolean: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue); break; case CellType.Error: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue); break; } } break; case CellType.Unknown: excelSheetGetRowGetCellSample.SetCellValue("Unknown"); break; } } } else //if (c > sheetDementionSample.FirstCol) { if (r == sheetDementionSample.FirstRow) { excelSheetGetRowGetCellSample.SetCellValue("Heading"); } else if (r == sheetDementionSample.FirstRow + 1) { excelSheetGetRowGetCellSample.SetCellValue("Schema"); } else { excelSheetGetRowGetCellSample.SetCellValue(string.Empty); } } } } catch { } } } } FileStream fs = new FileStream(mixExcelSample.PathFile, FileMode.OpenOrCreate); hssWorkbookSample.Write(fs); _stream = fs; #endregion } if (_stream != null) { byte[] binaryData = new byte[_stream.Length]; long bytesRead = _stream.Read(binaryData, 0, (int)_stream.Length); _stream.Close(); result = Convert.ToBase64String(binaryData); } mixExcelSample.CloseStream(); return(result); }
private void RemoveFilterModeRecord(HSSFSheet sheet) { if(filtermode!=null) sheet.Sheet.Records.Remove(filtermode); }
public HSSFAutoFilter(HSSFSheet sheet) { _sheet = sheet; }
/// <summary>Get the sheet at the specific index.</summary> /// <param name="index">0-based index of the sheet.</param> /// <returns>A facade representing the sheet.</returns> public override ExcelSheet GetSheet(int index) { Npoi.HSSFSheet native = (Npoi.HSSFSheet) this._workbook.GetSheetAt(index); return(ResolveSheet(native)); }
private HSSFChart(HSSFSheet sheet, ChartRecord chartRecord) { this.chartRecord = chartRecord; this.sheet = sheet; }
/** * Creates a bar chart. API needs some work. :) * * NOTE: Does not yet work... checking it in just so others * can take a look. */ public void CreateBarChart(HSSFWorkbook workbook, HSSFSheet sheet) { List<RecordBase> records = new List<RecordBase>(); records.Add(CreateMSDrawingObjectRecord()); records.Add(CreateOBJRecord()); records.Add(CreateBOFRecord()); records.Add(new HeaderRecord(string.Empty)); records.Add(new FooterRecord(string.Empty)); records.Add(CreateHCenterRecord()); records.Add(CreateVCenterRecord()); records.Add(CreatePrintSetupRecord()); // unknown 33 records.Add(CreateFontBasisRecord1()); records.Add(CreateFontBasisRecord2()); records.Add(new ProtectRecord(false)); records.Add(CreateUnitsRecord()); records.Add(CreateChartRecord(0, 0, 30434904, 19031616)); records.Add(CreateBeginRecord()); records.Add(CreateSCLRecord((short)1, (short)1)); records.Add(CreatePlotGrowthRecord(65536, 65536)); records.Add(CreateFrameRecord1()); records.Add(CreateBeginRecord()); records.Add(CreateLineFormatRecord(true)); records.Add(CreateAreaFormatRecord1()); records.Add(CreateEndRecord()); records.Add(CreateSeriesRecord()); records.Add(CreateBeginRecord()); records.Add(CreateTitleLinkedDataRecord()); records.Add(CreateValuesLinkedDataRecord()); records.Add(CreateCategoriesLinkedDataRecord()); records.Add(CreateDataFormatRecord()); // records.Add(CreateBeginRecord()); // unknown // records.Add(CreateEndRecord()); records.Add(new SerToCrtRecord()); records.Add(CreateEndRecord()); records.Add(CreateSheetPropsRecord()); records.Add(CreateDefaultTextRecord((short)TextFormatInfo.FontScaleNotSet)); records.Add(CreateAllTextRecord()); records.Add(CreateBeginRecord()); // unknown records.Add(CreateFontIndexRecord(5)); records.Add(CreateDirectLinkRecord()); records.Add(CreateEndRecord()); records.Add(CreateDefaultTextRecord((short)3)); // eek, undocumented text type records.Add(CreateUnknownTextRecord()); records.Add(CreateBeginRecord()); records.Add(CreateFontIndexRecord((short)6)); records.Add(CreateDirectLinkRecord()); records.Add(CreateEndRecord()); records.Add(CreateAxisUsedRecord((short)1)); CreateAxisRecords(records); records.Add(CreateEndRecord()); records.Add(CreateDimensionsRecord()); records.Add(CreateSeriesIndexRecord(2)); records.Add(CreateSeriesIndexRecord(1)); records.Add(CreateSeriesIndexRecord(3)); records.Add(EOFRecord.instance); sheet.InsertChartRecords(records); workbook.InsertChartRecord(); }
public static CellRangeAddress GetMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) { for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress merged = sheet.GetMergedRegion(i); if (rowNum >= merged.FirstRow && rowNum <= merged.LastRow) { if (cellNum >= merged.FirstColumn && cellNum <= merged.LastColumn) { return merged; } } } return null; }
/// <summary>Default Constructor</summary> /// <param name="sheet">The native XLSX sheet</param> public NpoiExcelSheet(Npoi.HSSFSheet sheet) { this._sheet = sheet; }
private static void CopyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, IDictionary<Int32, HSSFCellStyle> styleMap, Dictionary<short, short> paletteMap, bool keepFormulas) { List<SS.Util.CellRangeAddress> mergedRegions = destSheet.Sheet.MergedRecords.MergedRegions; destRow.Height = srcRow.Height; destRow.IsHidden = srcRow.IsHidden; destRow.RowRecord.OptionFlags = srcRow.RowRecord.OptionFlags; for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++) { HSSFCell oldCell = (HSSFCell)srcRow.GetCell(j); HSSFCell newCell = (HSSFCell)destRow.GetCell(j); if (srcSheet.Workbook == destSheet.Workbook) { newCell = (HSSFCell)destRow.GetCell(j); } if (oldCell != null) { if (newCell == null) { newCell = (HSSFCell)destRow.CreateCell(j); } HSSFCellUtil.CopyCell(oldCell, newCell, styleMap, paletteMap, keepFormulas); CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow, mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn); if (IsNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.Add(newMergedRegion); } } } } }
/// <summary> /// Clones the _sheet. /// </summary> /// <param name="workbook">The _workbook.</param> /// <returns>the cloned sheet</returns> public ISheet CloneSheet(HSSFWorkbook workbook) { IDrawing iDrawing = this.DrawingPatriarch;/*Aggregate drawing records*/ HSSFSheet sheet = new HSSFSheet(workbook, _sheet.CloneSheet()); int pos = sheet._sheet.FindFirstRecordLocBySid(DrawingRecord.sid); DrawingRecord dr = (DrawingRecord)sheet._sheet.FindFirstRecordBySid(DrawingRecord.sid); if (null != dr) { sheet._sheet.Records.Remove(dr); } if (DrawingPatriarch != null) { HSSFPatriarch patr = HSSFPatriarch.CreatePatriarch(this.DrawingPatriarch as HSSFPatriarch, sheet); sheet._sheet.Records.Insert(pos, patr.GetBoundAggregate()); sheet._patriarch = patr; } return sheet; }
public HSSFEvaluationSheet(HSSFSheet hs) { _hs = hs; }
/// <summary> /// HSSFSheet清除指定区域的图片 /// </summary> /// <param name="sheet"></param> /// <param name="minRow"></param> /// <param name="maxRow"></param> /// <param name="minCol"></param> /// <param name="maxCol"></param> /// <param name="onlyInternal"></param> private static void RemovePictures(HSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal) { var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer; if (null != shapeContainer) { var shapeList = shapeContainer.Children; for (int i = 0; i < shapeList.Count; i++) { var shape = shapeList[i]; if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor) { var picture = (HSSFPicture)shape; var anchor = (HSSFClientAnchor)shape.Anchor; if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal)) { shapeContainer.RemoveShape(shape); } } } } }
//[Obsolete] //public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet, // HSSFWorkbook workbook) //{ // SetBorderLeft(border, toCRA(region), sheet, workbook); //} /// <summary> /// Sets the left border for a region of cells by manipulating the cell style /// of the individual cells on the left /// </summary> /// <param name="border">The new border</param> /// <param name="region">The region that should have the border</param> /// <param name="sheet">The sheet that the region is on.</param> /// <param name="workbook">The workbook that the region is on.</param> public static void SetBorderLeft(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook workbook) { int rowStart = region.FirstRow; int rowEnd = region.LastRow; int column = region.FirstColumn; CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_LEFT, (int)border); for (int i = rowStart; i <= rowEnd; i++) { cps.SetProperty(HSSFCellUtil.GetRow(i, sheet), column); } }