/// <summary> /// 修改计算公式 /// </summary> /// <param name="Formula"></param> /// <returns></returns> public static bool UpdateFormula(TFormula Formula) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + TableName); strSql.Append("set "); // strSql.Append("Id=@Id,"); strSql.Append("PId=@PId,"); strSql.Append("StrId=@StrId,"); strSql.Append("FormulaName=@FormulaName,"); strSql.Append("ScriptContent=@ScriptContent "); strSql.Append("where "); strSql.Append("Id=@Id"); SqlParameter[] cmdParams = { new SqlParameter("@Id", Formula.Id), new SqlParameter("@PId", Formula.PId), new SqlParameter("@StrId", Formula.StrId), new SqlParameter("@FormulaName", Formula.FormulaName), new SqlParameter("@ScriptContent", Formula.ScriptContent) }; int result = SQLHelper.ExecuteNonQuery(GlobalData.DBConnStr, CommandType.Text, strSql.ToString(), cmdParams); return(result > 0 ? true : false); } catch { return(false); } }
private void btnAddFormula_Click(object sender, RoutedEventArgs e) { TFormula Formula = TFormulaController.CreateFormula(); if (TFormulaController.InsertFormula(Formula)) { Formula.Id = TFormulaController.GetMaxID(); this.FormulaVMs.Add(new TFormulaViewModel(Formula)); } else { MessageBox.Show("添加规约失败!", "添加失败", MessageBoxButton.OK, MessageBoxImage.Warning); } }
/// <summary> /// 创建计算公式 /// </summary> public static TFormula CreateFormula() { TFormula Formula = new TFormula(); Formula.Id = GetMaxID() + 1; Formula.FormulaName = "名称jy" + Formula.Id; // Formula.ScriptContent ="描述jy"+ Formula.Id; return(Formula); //TProtocolPara protocolPara = new TProtocolPara(); //protocolPara.Id = GetMaxID() + 1; //protocolPara.ProParaName = "规约参数" + protocolPara.Id; //return protocolPara; }
public string GetCellOrFormula(int row) { object cell = xls.GetCellValue(row, 1); if (cell == null) { return(""); } TFormula fmla = (cell as TFormula); if (fmla != null) { return(fmla.Text); } return(Convert.ToString(cell)); }
private void AddData(ExcelFile Xls) { LoadUdfs(Xls); //Register our custom functions. As we are using a local scope, we need to register them each time. Xls.Open(Path.Combine(PathToExe, "udfs.xls")); //Open the file we want to manipulate. //Fill the cell range with other values so we can see how the sheet is recalculated by FlexCel. TXlsCellRange Data = Xls.GetNamedRange("Data", -1); for (int r = Data.Top; r < Data.Bottom; r++) { Xls.SetCellValue(r, Data.Left, r - Data.Top); } //Add an UDF to the sheet. We can enter the fucntion "BoolChoose" here because it was registered into FlexCel in LoadUDF() //If it hadn't been registered, this line would raise an Exception of an unknown function. string FmlaText = "=BoolChoose(TRUE,\"This formula was entered with FlexCel!\",\"It shouldn't display this\")"; Xls.SetCellValue(11, 1, new TFormula(FmlaText)); //Verify the UDF entered is correct. We can read any udf from Excel, even if it is not registered with AddUserDefinedFunction. object o = Xls.GetCellValue(11, 1); TFormula fm = o as TFormula; Debug.Assert(fm != null, "The cell must contain a formula"); if (fm != null) { Debug.Assert(fm.Text == FmlaText, "Error in Formula: It should be \"" + FmlaText + "\" and it is \"" + fm.Text + "\""); } //Recalc the sheet. As we are not saving it yet, we ned to make a manual recalc. Xls.Recalc(); //Export the file to PDF so we can see the values calculated by FlexCel without Excel recalculating them. using (FlexCelPdfExport pdf = new FlexCelPdfExport(Xls, true)) { pdf.Export(saveFileDialog1.FileName); } //Save the file as xls too so we can compare. Xls.Save(Path.ChangeExtension(saveFileDialog1.FileName, "xls")); }
public static DateTime GetCellAsDate(ref ExcelFile xls, int row, int col, string sheetname = "", bool valueonly = false) { DateTime s = DateTime.MinValue; object v; if (sheetname != "") { xls.ActiveSheetByName = sheetname; } // On Error Resume Next v = xls.GetCellValue(row, col); if (v is TFormula) { if (valueonly) { } else { TFormula Fmla = ((TFormula)v); // When we have formulas, we want to write the formula result. // If we wanted the formula text, we would not need this part. try { s = DateTime.Parse((string)Fmla.Result); } catch { } } } else if (v is double) { s = DateTime.FromOADate((double)v); } //s = new DateTime(1900, 1, 1).AddDays((double)v); // On Error GoTo 0 return(s); }
public static string GetCell(ref ExcelFile xls, int row, int col, string sheetname = "", bool valueonly = false) { string s = ""; object v; if (sheetname != "") { xls.ActiveSheetByName = sheetname; } // On Error Resume Next v = xls.GetCellValue(row, col); if (v is TFormula) { if (valueonly) { s = ""; } else { TFormula Fmla = ((TFormula)v); // When we have formulas, we want to write the formula result. // If we wanted the formula text, we would not need this part. s = Convert.ToString(Fmla.Result); } } else { s = Convert.ToString(v); } // On Error GoTo 0 if (s == null) { s = ""; } return(s); }
internal override bool OnFound(ExcelFile xls, object oldval, int row, int col) { TFormula oldFmla = oldval as TFormula; if (!FSearchInFormulas && oldFmla != null) { return(false); //do not replace if it is a formula. } if (oldval == null) { return(false); } TRichString OldStr = oldval as TRichString; if (OldStr == null) { OldStr = new TRichString(FlxConvert.ToString(oldval)); } TRichString newStr = OldStr.Replace(SearchToStr, NewValue.ToString(), FCaseInsensitive); if (newStr != null && newStr.Value != null && newStr.Length > 0 && newStr.Value.StartsWith(TFormulaMessages.TokenString(TFormulaToken.fmStartFormula))) { TFormulaSpan Span = oldFmla != null ? oldFmla.Span : new TFormulaSpan(); xls.SetCellValue(row, col, new TFormula(newStr.Value, null, Span)); } else { if (oldFmla != null && !oldFmla.Span.IsOneCell) { return(false); //can't replace a shared formula with simple text. } xls.SetCellFromString(row, col, newStr); } ReplaceCount++; return(false); }
/// <summary> /// 添加计算公式 /// </summary> /// <param name="Formula"></param> public static bool InsertFormula(TFormula Formula) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into TFormula"); strSql.Append("("); strSql.Append("Id,"); strSql.Append("PId,"); strSql.Append("StrId,"); strSql.Append("FormulaName,"); strSql.Append("ScriptContent"); strSql.Append(")"); strSql.Append("values"); strSql.Append("("); strSql.Append("@Id,"); strSql.Append("@PId,"); strSql.Append("@StrId,"); strSql.Append("@FormulaName,"); strSql.Append("@ScriptContent"); strSql.Append(")"); SqlParameter[] cmdParams = { new SqlParameter("@Id", Formula.Id), new SqlParameter("@PId", Formula.PId), new SqlParameter("@StrId", Formula.StrId), new SqlParameter("@FormulaName", Formula.FormulaName), new SqlParameter("@ScriptContent", Formula.ScriptContent), }; int result = SQLHelper.ExecuteNonQuery(GlobalData.DBConnStr, CommandType.Text, strSql.ToString(), cmdParams); return(result > 0 ? true : false); } catch { return(false); } }
public bool Upload2(HttpPostedFileBase file) { bool formatted = false; List <string> sheetNames = new List <string>(); DataSet dataSet = new DataSet(); MemoryStream target = new MemoryStream(); file.InputStream.CopyTo(target); byte[] data = target.ToArray(); HttpFile httpFile = new HttpFile(file.FileName, file.ContentType, data); Stream stream = new MemoryStream(httpFile.Buffer); XlsFile xls = new XlsFile(false); xls.Open(stream); for (int sheet = 1; sheet <= xls.SheetCount; sheet++) { xls.ActiveSheet = sheet; sheetNames.Add(xls.SheetName); DataTable Data = dataSet.Tables.Add("Sheet" + sheet.ToString()); Data.BeginLoadData(); try { int ColCount = xls.ColCount; //Add one column on the dataset for each used column on Excel. //for (int c = 1; c <= ColCount; c++) //{ // Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String)); //Here we will add all strings, since we do not know what we are waiting for. //} for (int c = 1; c <= ColCount; c++) { int r = 1; int XF = 0; //This is the cell format, we will not use it here. object val = xls.GetCellValueIndexed(r, c, ref XF); string propName = string.Empty; TFormula Fmla = val as TFormula; propName = Convert.ToString(Fmla != null ? Fmla.Result : val); Data.Columns.Add(propName, typeof(String)); } string[] dr = new string[ColCount]; int RowCount = xls.RowCount; for (int r = 2; r <= RowCount; r++) { Array.Clear(dr, 0, dr.Length); //This loop will only loop on used cells. It is more efficient than looping on all the columns. for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once. { int Col = xls.ColFromIndex(r, cIndex); if (formatted) { TRichString rs = xls.GetStringFromCell(r, Col); dr[Col - 1] = rs.Value; } else { int XF = 0; //This is the cell format, we will not use it here. object val = xls.GetCellValueIndexed(r, cIndex, ref XF); TFormula Fmla = val as TFormula; if (Fmla != null) { //When we have formulas, we want to write the formula result. //If we wanted the formula text, we would not need this part. dr[Col - 1] = Convert.ToString(Fmla.Result); } else { dr[Col - 1] = Convert.ToString(val); } } } Data.Rows.Add(dr); } } finally { Data.EndLoadData(); } } DataTable resultDt = dataSet.Tables[0]; List <Student> Studentlist = new List <Student>(); Studentlist = Convertor.ConvertToList <Student>(resultDt); //AutoMapper.Mapper.Initialize(cfg => cfg.CreateMissingTypeMaps = true); //var students = AutoMapper.Mapper.Map<IDataReader, IEnumerable<Student>>(resultDt.CreateDataReader()); return(true); }
internal virtual bool Go(ExcelFile xls, object value, int row, int col) { object v = value; if (FSearchInFormulas) { TFormula fmla = value as TFormula; if (fmla != null) { if (!fmla.Span.IsTopLeft) { return(false); //can't change part of a shared formula. } v = fmla.Text; } } if (SearchItem == null) { if (v == null) { return(OnFound(xls, v, row, col)); } return(false); } if (v == null) { return(false); } if (FWholeCellContents) { if (SearchStr != null) { string sv = v as string; if (sv != null) { if (String.Equals(sv, SearchStr, FStringComparison)) { return(OnFound(xls, v, row, col)); } } return(false); } if (SearchItem.Equals(v)) { return(OnFound(xls, v, row, col)); } return(false); } //partial searches string sv2 = v.ToString(); if (FCaseInsensitive) { if (sv2.ToUpper(CultureInfo.CurrentCulture).IndexOf(UpperSearchItem) >= 0) { return(OnFound(xls, v, row, col)); } } else { if (sv2.IndexOf(SearchToStr) >= 0) { return(OnFound(xls, v, row, col)); } } return(false); }
public TFormulaViewModel(TFormula Formula) { this.Formula = Formula; this.isSelected = false; }
private void CompareXls(XlsFile xls1, XlsFile xls2, DataTable table) { int DiffCount = 0; xls1.Recalc(); for (int sheet = 1; sheet <= xls1.SheetCount; sheet++) { xls1.ActiveSheet = sheet; xls2.ActiveSheet = sheet; int aColCount = xls1.ColCount; for (int r = 1; r <= xls1.RowCount; r++) { for (int c = 1; c <= aColCount; c++) { TFormula f = xls1.GetCellValue(r, c) as TFormula; if (f != null) { TCellAddress ad = new TCellAddress(r, c); TFormula f2 = (TFormula)xls2.GetCellValue(r, c); if (f.Result == null) { f.Result = ""; } if (f2.Result == null) { f2.Result = ""; } double eps = 0; if (f.Result is Double && f2.Result is Double) { if ((Double)f2.Result == 0) { if (Math.Abs((double)f.Result) < Double.Epsilon) { eps = 0; } else { eps = Double.NaN; } } else { eps = (double)f.Result / (Double)f2.Result; } if (Math.Abs(eps - 1) < 0.001) { f.Result = f2.Result; } } if (!f.Result.Equals(f2.Result)) { if (table == null) { report.Text += "\nSheet:" + xls1.SheetName + " --- Cell:" + ad.CellRef + " --- Calculated: " + f.Result.ToString() + " Excel: " + f2.Result.ToString() + " dif: " + eps.ToString() + " formula: " + f.Text; Application.DoEvents(); } else { table.Rows.Add(new object[] { xls1.SheetName, ad.CellRef, f.Result.ToString(), f2.Result.ToString(), eps.ToString(), f.Text }); } DiffCount++; } } } } } if (table == null) { report.Text += "\nFinished Comparing."; if (DiffCount == 0) { report.Text += "\n**********No differences found!**********"; } else { report.Text += String.Format("\n --->Found {0} differences", DiffCount); } } }
private void ImportFile(string FileName, bool Formatted) { try { //Open the Excel file. XlsFile xls = new XlsFile(false); DateTime StartOpen = DateTime.Now; xls.Open(FileName); DateTime EndOpen = DateTime.Now; //Set up the Grid DisplayGrid.DataBindings.Clear(); DisplayGrid.DataSource = null; DisplayGrid.DataMember = null; DataSet dataSet1 = new DataSet(); sheetCombo.Items.Clear(); //We will create a DataTable "SheetN" for each sheet on the Excel sheet. for (int sheet = 1; sheet <= xls.SheetCount; sheet++) { xls.ActiveSheet = sheet; sheetCombo.Items.Add(xls.SheetName); DataTable Data = dataSet1.Tables.Add("Sheet" + sheet.ToString()); Data.BeginLoadData(); try { int ColCount = xls.ColCount; //Add one column on the dataset for each used column on Excel. for (int c = 1; c <= ColCount; c++) { Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String)); //Here we will add all strings, since we do not know what we are waiting for. } string[] dr = new string[ColCount]; int RowCount = xls.RowCount; for (int r = 1; r <= RowCount; r++) { Array.Clear(dr, 0, dr.Length); //This loop will only loop on used cells. It is more efficient than looping on all the columns. for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once. { int Col = xls.ColFromIndex(r, cIndex); if (Formatted) { TRichString rs = xls.GetStringFromCell(r, Col); dr[Col - 1] = rs.Value; } else { int XF = 0; //This is the cell format, we will not use it here. object val = xls.GetCellValueIndexed(r, cIndex, ref XF); TFormula Fmla = val as TFormula; if (Fmla != null) { //When we have formulas, we want to write the formula result. //If we wanted the formula text, we would not need this part. dr[Col - 1] = Convert.ToString(Fmla.Result); } else { dr[Col - 1] = Convert.ToString(val); } } } Data.Rows.Add(dr); } } finally { Data.EndLoadData(); } DateTime EndFill = DateTime.Now; statusBar.Text = String.Format("Time to load file: {0} Time to fill dataset: {1} Total time: {2}", (EndOpen - StartOpen).ToString(), (EndFill - EndOpen).ToString(), (EndFill - StartOpen).ToString()); } //Set up grid. DisplayGrid.DataSource = dataSet1; DisplayGrid.DataMember = "Sheet1"; sheetCombo.SelectedIndex = 0; DisplayGrid.CaptionText = FileName; } catch { DisplayGrid.CaptionText = "Error Loading File"; DisplayGrid.DataSource = null; DisplayGrid.DataMember = ""; sheetCombo.Items.Clear(); throw; } }
private void AnalizeFile(string FileName, int Row, int Col) { XlsFile xls = new XlsFile(); xls.Open(FileName); int XF = 0; MessageBox.Show("Active sheet is \"" + xls.ActiveSheetByName + "\""); object v = xls.GetCellValue(Row, Col, ref XF); if (v == null) { MessageBox.Show("Cell A1 is empty"); return; } //Here we have all the kind of objects FlexCel can return. switch (Type.GetTypeCode(v.GetType())) { case TypeCode.Boolean: MessageBox.Show("Cell A1 is a boolean: " + (bool)v); return; case TypeCode.Double: //Remember, dates are doubles with date format. TUIColor CellColor = Color.Empty; bool HasDate, HasTime; String CellValue = TFlxNumberFormat.FormatValue(v, xls.GetFormat(XF).Format, ref CellColor, xls, out HasDate, out HasTime).ToString(); if (HasDate || HasTime) { MessageBox.Show("Cell A1 is a DateTime value: " + FlxDateTime.FromOADate((double)v, xls.OptionsDates1904).ToString() + "\n" + "The value is displayed as: " + CellValue); } else { MessageBox.Show("Cell A1 is a double: " + (double)v + "\n" + "The value is displayed as: " + CellValue + "\n"); } return; case TypeCode.String: MessageBox.Show("Cell A1 is a string: " + v.ToString()); return; } TFormula Fmla = v as TFormula; if (Fmla != null) { MessageBox.Show("Cell A1 is a formula: " + Fmla.Text + " Value: " + Convert.ToString(Fmla.Result)); return; } TRichString RSt = v as TRichString; if (RSt != null) { MessageBox.Show("Cell A1 is a formatted string: " + RSt.Value); return; } if (v is TFlxFormulaErrorValue) { MessageBox.Show("Cell A1 is an error: " + TFormulaMessages.ErrString((TFlxFormulaErrorValue)v)); return; } throw new Exception("Unexpected value on cell"); }
public static DataSet ToDataSet(byte[] data, bool firstRowIsPropertyName = true) { bool formatted = false; DataSet dataSet = new DataSet(); Stream stream = new MemoryStream(data); XlsFile xls = new XlsFile(false); xls.Open(stream); for (int sheet = 1; sheet <= xls.SheetCount; sheet++) { xls.ActiveSheet = sheet; DataTable Data = dataSet.Tables.Add("Sheet" + sheet.ToString()); Data.BeginLoadData(); try { int ColCount = xls.ColCount; int beginIndexRow = 1; if (firstRowIsPropertyName) { beginIndexRow = 2; for (int c = 1; c <= ColCount; c++) { int r = 1; int XF = 0; // This is the cell format, we will not use it here. object val = xls.GetCellValueIndexed(r, c, ref XF); string propName = string.Empty; TFormula Fmla = val as TFormula; propName = Convert.ToString(Fmla != null ? Fmla.Result : val); Data.Columns.Add(propName, typeof(String)); } } else { beginIndexRow = 1; // Add one column on the dataset for each used column on Excel. for (int c = 1; c <= ColCount; c++) { // Here we will add all strings, since we do not know what we are waiting for. Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String)); } } string[] dr = new string[ColCount]; int rowCount = xls.RowCount; for (int r = beginIndexRow; r <= rowCount; r++) { Array.Clear(dr, 0, dr.Length); //This loop will only loop on used cells. It is more efficient than looping on all the columns. for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once. { int Col = xls.ColFromIndex(r, cIndex); if (formatted) { TRichString rs = xls.GetStringFromCell(r, Col); dr[Col - 1] = rs.Value; } else { int XF = 0; //This is the cell format, we will not use it here. object val = xls.GetCellValueIndexed(r, cIndex, ref XF); TFormula Fmla = val as TFormula; if (Fmla != null) { //When we have formulas, we want to write the formula result. //If we wanted the formula text, we would not need this part. dr[Col - 1] = Convert.ToString(Fmla.Result); } else { dr[Col - 1] = Convert.ToString(val); } } } Data.Rows.Add(dr); } } finally { Data.EndLoadData(); } } return(dataSet); }
internal static void Write(TextWriter OutString, ExcelFile Workbook, TXlsCellRange Range, int[] ColumnWidths, int CharactersForFirstColumn, bool ExportHiddenRowsOrColumns, bool ExportTextOutsideCells) { if (Range == null) { Range = new TXlsCellRange(1, 1, Workbook.RowCount, Workbook.GetColCount(Workbook.ActiveSheet, false)); } for (int r = Range.Top; r <= Range.Bottom; r++) { if (!ExportHiddenRowsOrColumns && Workbook.GetRowHidden(r)) { continue; } int cIndex = 0; double FirstColWidth = Workbook.GetColWidth(Range.Left); string Remaining = string.Empty; int AcumColLen = 0; bool InMergedCell = false; THFlxAlignment MergedAlign = THFlxAlignment.general; TCellType MergedType = TCellType.Unknown; int OrigColLen = 0; for (int c = Range.Left; c <= Range.Right; c++) { if (!ExportHiddenRowsOrColumns && Workbook.GetColHidden(c)) { continue; } string s = Workbook.GetStringFromCell(r, c).ToString(); TFlxFormat fmt = null; if (ExportTextOutsideCells) { fmt = Workbook.GetCellVisibleFormatDef(r, c); } if (string.IsNullOrEmpty(s)) { s = Remaining; } int ColLen = 0; if (ColumnWidths == null) { if (CharactersForFirstColumn <= 0) { ColLen = s.Length; } else if (FirstColWidth <= 0) { ColLen = 0; } else { ColLen = (int)Math.Round((double)CharactersForFirstColumn * Workbook.GetColWidth(c) / FirstColWidth); } } else { if (cIndex >= ColumnWidths.Length) { break; } ColLen = ColumnWidths[cIndex]; } cIndex++; if (InMergedCell) { OrigColLen += ColLen; } else { OrigColLen = ColLen; } if (s.Length == 0) { AcumColLen += ColLen; continue; } THFlxAlignment HAlign = THFlxAlignment.left; TCellType CellType; if (InMergedCell) { HAlign = MergedAlign; CellType = MergedType; } else { Object CellVal = Workbook.GetCellValue(r, c); TFormula fmla = CellVal as TFormula; if (fmla != null) { CellVal = fmla.Result; } CellType = TExcelTypes.ObjectToCellType(CellVal); if (ExportTextOutsideCells && fmt != null && Remaining.Length == 0) { HAlign = GetDataAlign(CellType, fmt); } } if (HAlign == THFlxAlignment.left) { OutString.Write(new string(' ', AcumColLen)); } else { TXlsCellRange mr = Workbook.CellMergedBounds(r, c); InMergedCell = mr.Right > c; if (mr.Right > c) { AcumColLen += ColLen; if (c == mr.Left) { Remaining = s; MergedAlign = HAlign; MergedType = CellType; } continue; } if (mr.Right > mr.Left) { s = Remaining; Remaining = string.Empty; } MergedAlign = THFlxAlignment.left; MergedType = TCellType.Unknown; InMergedCell = false; ColLen += AcumColLen; } AcumColLen = 0; if (s.Length > ColLen) { if (ExportTextOutsideCells && HAlign == THFlxAlignment.right) { if (CellType == TCellType.Number) { OutString.Write(new string('#', ColLen)); } else { OutString.Write(s.Substring(s.Length - ColLen)); } } else { OutString.Write(s.Substring(0, ColLen)); } if (ExportTextOutsideCells && HAlign != THFlxAlignment.right) { Remaining = s.Substring(ColLen); } } else { int Pad = ColLen - s.Length; if (ExportTextOutsideCells && Remaining.Length == 0) { Pad = TextAlign(OutString, HAlign, s.Length, ColLen, OrigColLen); } OutString.Write(s); OutString.Write(new string(' ', Pad)); Remaining = string.Empty; } } if (ExportTextOutsideCells && Remaining.Length > 0) { OutString.Write(Remaining); } Remaining = string.Empty; OutString.Write(TCompactFramework.NewLine); } }