精华内容
下载资源
问答
  • How can I construct the following string in an Excel formula: 如何在Excel公式中构造以下字符串: Maurice "The

    本文翻译自:How to create strings containing double quotes in Excel formulas?

    How can I construct the following string in an Excel formula: 如何在Excel公式中构造以下字符串:

    Maurice "The Rocket" Richard 莫里斯·“火箭”理查德

    If I'm using single quotes, it's trivial = "Maurice 'The Rocket' Richard" but what about double quotes? 如果我使用单引号,那么它就很简单= "Maurice 'The Rocket' Richard"但是双引号又如何呢?


    #1楼

    参考:https://stackoom.com/question/uLo/如何在Excel公式中创建包含双引号的字符串


    #2楼

    I use a function for this (if the workbook already has VBA). 为此,我使用了一个功能(如果工作簿已经具有VBA)。

    Function Quote(inputText As String) As String
      Quote = Chr(34) & inputText & Chr(34)
    End Function
    

    This is from Sue Mosher's book "Microsoft Outlook Programming". 这来自Sue Mosher的书“ Microsoft Outlook编程”。 Then your formula would be: 那么您的公式将是:

    ="Maurice "&Quote("Rocket")&" Richard"

    This is similar to what Dave DuPlantis posted. 这类似于Dave DuPlantis发布的内容。


    #3楼

    Three double quotes: " " " x " " " = "x" Excel will auto change to one double quote. 三个双引号: " " " x " " " = "x" Excel将自动更改为一个双引号。 eg: 例如:

    =CONCATENATE("""x"""," hi")  
    

    = "x" hi =“ x”嗨


    #4楼

    VBA Function VBA功能

    1) .Formula = "=""THEFORMULAFUNCTION ""&(CHAR(34) & ""STUFF"" & CHAR(34))" 1).Formula =“ =”“ THEFORMULAFUNCTION”“&(CHAR(34)&”“ STUFF”“&CHAR(34))”

    2) .Formula = "THEFORMULAFUNCTION ""STUFF""" 2).Formula =“ THEFORMULAFUNCTION”“ STUFF”“”“

    The first method uses vba to write a formula in a cell which results in the calculated value: 第一种方法使用vba在单元格中写入公式,该公式将得出计算值:

     THEFORMULAFUNCTION "STUFF"
    

    The second method uses vba to write a string in a cell which results in the value: 第二种方法使用vba在单元格中写入一个字符串,该字符串产生以下值:

     THEFORMULAFUNCTION "STUFF"
    

    Excel Result/Formula Excel结果/公式

    1) ="THEFORMULAFUNCTION "&(CHAR(34) & "STUFF" & CHAR(34)) 1)=“ THEFORMULAFUNCTION”&(CHAR(34)&“ STUFF”&CHAR(34))

    2) THEFORMULAFUNCTION "STUFF" 2)形式功能“填充”


    #5楼

    您是否尝试过使用双引号进行转义?

    = "Maurice ""The Rocket"" Richard"
    

    #6楼

    另外,您可以使用CHAR函数:

    = "Maurice " & CHAR(34) & "Rocket" & CHAR(34) & " Richard"
    
    展开全文
  • Excel创建与编辑公式.pdf
  • Excel2000如何创建计算公式.docx
  • Excel中的数组是指一行、一列或多行多列的一组数据元素的集合,在Excel中用{}进行创建,其中同一行的元素用逗号“,”隔开,不同行的元素用分号“;”隔开。 如下图展示的是一个3行5列的数组: 1 2 3 4 5 6 ...
  • excel随机填充文本 在Excel中创建随机文本 (Create Random Text in Excel)Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-...

    excel随机填充文本

    Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.

    上周,我用示例数据创建了一个Excel文件,用于一些实验。 但请放心,它们不是疯狂科学家式的实验-我正在进行Power Pivot实验,需要一些数据才能使用。

    I needed 2 types of data:

    我需要两种类型的数据:

    • Numbers: sample test scores in one column

      数字:在一列样本测试分数
    • Text: random Region names and Gender in other columns.

      文本:其他列中包含随机的区域名称和性别。
    Create random text in Excel http://blog.contextures.com/

    创建随机数 (Create Random Numbers)

    It’s easy to create random numbers in Excel – just use the RANDBETWEEN function. Set a bottom and top number, and a random number from that range is returned. I wanted test scores between 30 and 100, so I used this formula:

    在Excel中创建随机数很容易-只需使用RANDBETWEEN函数即可。 设置一个底部和顶部数字,并返回该范围内的随机数。 我希望测试成绩在30到100之间,因此我使用了以下公式:

    =RANDBETWEEN(30,100)

    = RANDBETWEEN(30,100)

    chooserandomtext02

    获取随机文本 (Get Random Text)

    The RANDBETWEEN function can only return numbers, so it’s a little trickier to create a set of random text data. I’ve tried different ways in the past, and they usually involve multiple steps. It’s nothing too complicated, but it takes longer than just using RANDBETWEEN.

    RANDBETWEEN函数只能返回数字,因此创建一组随机文本数据比较麻烦。 过去,我尝试过不同的方法,它们通常涉及多个步骤。 没什么太复杂的,但是比使用RANDBETWEEN花费的时间更长。

    This time, I tested a new method – using RANDBETWEEN inside a CHOOSE formula. Both lists of options were short – 2 genders and 3 regions – so they would work nicely as values in a CHOOSE formula.

    这次,我测试了一种新方法-在CHOOSE公式中使用RANDBETWEEN。 这两个选项列表都很简短-2个性别和3个地区-因此它们可以很好地用作CHOOSE公式中的值。

    chooserandomtext03

    输入选择值列表 (Type the List of CHOOSE Values)

    In my Gender formula, there would be two values, so the RANDBETWEEN function used 1 as the bottom value and 2 as the top.

    在我的Gender公式中,会有两个值,因此RANDBETWEEN函数使用1作为下限值,使用2作为上限值。

    Then, I typed in the Genders, as the values, putting double quote marks around each value.

    然后,我输入Genders作为值,在每个值周围加上双引号。

    =CHOOSE(RANDBETWEEN(1,2),"Male","Female")

    = CHOOSE(RANDBETWEEN(1,2),“ Male”,“ Female”)

    Create random text in Excel http://blog.contextures.com/

    使用单元格引用作为选择值 (Use Cell References as CHOOSE Values)

    In my Region formula, there are 3 values, so the RANDBETWEEN function used 1 as the bottom value and 3 as the top.

    在我的Region公式中,有3个值,因此RANDBETWEEN函数使用1作为下限值,使用3作为上限值。

    Instead of typing the region names, I used absolute references to the cells which contained the region names. Cell references are a better choice if the values are long, or if they’ll change frequently. Also, it’s quicker than typing!

    我没有键入区域名称,而是使用对包含区域名称的单元格的绝对引用。 如果值很长或它们会经常更改,则单元格引用是一个更好的选择。 此外,它比打字还快!

    =CHOOSE(RANDBETWEEN(1,3),$K$4,$K$5,$K$6)

    =选择(RANDBETWEEN(1,3),$ K $ 4,$ K $ 5,$ K $ 6)

    Create random text in Excel http://blog.contextures.com/

    粘贴为值 (Paste As Values)

    The final step in setting up the random data is to copy the columns, and paste them as values. That will lock in the values, so they don’t keep changing, as you work in the file.

    设置随机数据的最后一步是复制列,并将其粘贴为值。 这将锁定值,因此在您处理文件时,它们将不会不断更改。

    • I use the mouse to select the columns, then drag to the right, while holding the mouse button.

      我使用鼠标选择列,然后在按住鼠标按钮的同时向右拖动。
    • Drag the column back to its original location, and let go of the mouse button.

      将列拖回到其原始位置,然后放开鼠标按钮。
    • Then, in the popup menu, click on Copy here as Values Only

      然后,在弹出菜单中,单击“此处复制为仅值”

    You can see that step near the end of the video, below.

    您可以在下面的视频结尾处看到该步骤。

    视频:使用“选择”创建随机文本 (Video: Create Random Text With CHOOSE)

    Watch this video to see the steps for using RANDBETWEEN on its own, and combined with CHOOSE.

    观看此视频,以查看结合使用RANDBETWEEN和CHOOSE的步骤。

    演示地址

    下载样本文件 (Download the Sample File)

    To download the sample file, go to the Excel Sample Files page on my website, and in the Functions section, look for FN0035 - Create Random Text with CHOOSE Function. The file is in xlsx format, and does not contain macros. One sheet has the formulas, and the other has the data pasted as values.

    要下载示例文件,请转到我网站上的“ Excel示例文件”页面,然后在“功能”部分中查找“ FN0035-使用CHOOSE函数创建随机文本” 。 该文件为xlsx格式,不包含宏。 一张纸具有公式,另一张纸具有粘贴为值的数据。

    创建随机测试数据 (Create Random Test Data)

    This video shows how to create random numbers with RANDBETWEEN, then change the formulas to static values.

    该视频演示了如何使用RANDBETWEEN创建随机数,然后将公式更改为静态值。

    演示地址

    翻译自: https://contexturesblog.com/archives/2014/08/21/create-random-text-in-excel/

    excel随机填充文本

    展开全文
  • 本文,我将向您展示如何使用Free Spire.XLS for Java在Excel单元格应用公式和函数。 Add Spire.Xls.jar as dependency Method 1: Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire....

    公式定义为执行计算并以单元格形式返回结果值的方程式。 它包含几个函数,单元格引用,函数运算符。 根据用户需求,公式可以简单也可以复杂。 可以将函数定义为Excel中的内置操作,该操作在单元格中返回结果值。 函数仅包含单元格引用和值。

    在本文中,我将向您展示如何使用Free Spire.XLS for Java在Excel单元格中应用公式和函数。

    Add Spire.Xls.jar as dependency

    Method 1: Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire.Doc.jar file from the “lib” folder. Import the jar file in your project as a dependency.

    方法2:如果要创建Maven项目,则可以通过将以下配置添加到pom.xml来轻松添加jar依赖项。

    <repositories>
            <repository>
                <id>com.e-iceblue</id>
                <name>e-iceblue</name>
                <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
            </repository>
    </repositories>
    <dependencies>
        <dependency>
            <groupId> e-iceblue </groupId>
            <artifactId>spire.xls.free</artifactId>
            <version>2.2.0</version>
        </dependency>
    </dependencies>
    

    Using the code

    import com.spire.xls.*;
    
    public class InsertFormulas {
    
        public static void main(String[] args) {
    
            //Create a Workbook object
            Workbook workbook = new Workbook();
    
            //Get the first worksheet
            Worksheet sheet = workbook.getWorksheets().get(0);
    
            //Declare two variables: currentRow、currentFormula
            int currentRow = 1;
            String currentFormula = null;
    
            //Set the column width
            sheet.setColumnWidth(1, 32);
            sheet.setColumnWidth(2, 16);
    
            //Write test data into cells
            sheet.getCellRange(currentRow,1).setValue("Test data:");
            sheet.getCellRange(currentRow,2).setNumberValue(1);
            sheet.getCellRange(currentRow,3).setNumberValue(2);
            sheet.getCellRange(currentRow,4).setNumberValue(3);
            sheet.getCellRange(currentRow,5).setNumberValue(4);
            sheet.getCellRange(currentRow,6).setNumberValue(5);
    
            //Write text in cells
            currentRow += 2;
            sheet.getCellRange(currentRow,1).setValue("Formulas or functions:") ; ;
            sheet.getCellRange(currentRow,2).setValue("result:");
    
            //Format cells
            CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
            range.getStyle().getFont().isBold(true);
            range.getStyle().setKnownColor(ExcelColors.BlueGray);
            range.getStyle().setFillPattern(ExcelPatternType.Solid);
            range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);
    
            //Formula
            currentFormula = "=1/2+3*4";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //IF function
            currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //Count function
            currentFormula = "=Count(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //MAX function
            currentFormula = "=MAX(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //AVERAGE function
            currentFormula = "=AVERAGE(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //SUM function
            currentFormula = "=SUM(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //NOW function
            currentFormula = "=NOW()";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
            sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");
    
            //SECOND function
            currentFormula = "=SECOND(11)";
            sheet.getCellRange(++currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //MINUTE function
            currentFormula = "=MINUTE(12)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //MONTH function
            currentFormula = "=MONTH(9)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //DAY function
            currentFormula = "=DAY(10)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //TIME function
            currentFormula = "=TIME(4,5,7)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //DATE function
            currentFormula = "=DATE(6,4,2)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //TODAY function
            currentFormula = "=TODAY()";
            sheet.getCellRange(currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow++,2).getStyle().setNumberFormat("YYYY/MM/DD");
    
            //RAND function
            currentFormula = "=RAND()";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //HOUR function
            currentFormula = "=HOUR(12)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //MOD function
            currentFormula = "=MOD(5,3)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //WEEKDAY function
            currentFormula = "=WEEKDAY(3)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //YEAR function
            currentFormula = "=YEAR(23)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //NOT function
            currentFormula = "=NOT(true)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //OR function
            currentFormula = "=OR(true)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //AND function
            currentFormula = "=AND(TRUE)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //VALUE function
            currentFormula = "=VALUE(30)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //LEN function
            currentFormula = "=LEN(\"world\")";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //MID function
            currentFormula = "=MID(\"world\",4,2)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //ROUND function
            currentFormula = "=ROUND(7,3)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //SIGN function
            currentFormula = "=SIGN(4)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //INT function
            currentFormula = "=INT(200)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //ABS function
            currentFormula = "=ABS(-1.21)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //LN function
            currentFormula = "=LN(15)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //EXP function
            currentFormula = "=EXP(20)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //SQRT function
            currentFormula = "=SQRT(40)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //PI function
            currentFormula = "=PI()";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //COS function
            currentFormula = "=COS(9)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            //SIN function
            currentFormula = "=SIN(45)";
            sheet.getCellRange(currentRow, 1).setText(currentFormula);
            sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);
    
            workbook.saveToFile("output/ApplyFormulas.xlsx",ExcelVersion.Version2013);
        }
    }
    

    Output
    Alt Text

    from: https://dev.to//eiceblue/apply-formulas-and-functions-in-excel-in-java-m17

    展开全文
  • 对于数据量较大的表格,需要计算一些特殊数值时,我们通过运用公式...下面的示例中将分享通过C# 来创建、读取Excel公式的方法。 工具使用 Spire.XLS for .NET 8.0 下载安装该类库后,注意程序添加引用Sp...

    对于数据量较大的表格,需要计算一些特殊数值时,我们通过运用公式能有效提高我们数据处理的速度和效率,对于后期数据的增删改查等的批量操作也很方便。此外,对于某些数值的信息来源,我们也可以通过读取数据中包含的公式来获取。下面的示例中将分享通过C# 来创建、读取Excel公式的方法。

    工具使用

    下载安装该类库后,注意在程序中添加引用Spire.Xls.dll(dll文件可在安装路径下的Bin文件夹中获取)

    代码示例(供参考)

    【示例1】创建Excel公式

    步骤 1 :新建工作簿

    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];

    步骤 2 : 添加测试数据及文本,并设置文本格式等

    //初始化currentRow、currentFormula
    int currentColumn = 1;
    int currentRow = 1;
    string currentFormula = string.Empty;
    
    //设置1、2列列宽
    sheet.SetColumnWidth(1, 20);
    sheet.SetColumnWidth(2, 12);
    
    //写入测试数据
    sheet.Range[currentColumn, 1].Value = "测试数据:";
    sheet.Range[currentColumn, 2].NumberValue = 10;
    sheet.Range[currentColumn, 3].NumberValue = 20; 
    sheet.Range[currentColumn, 4].NumberValue = 30;
    sheet.Range[currentColumn, 5].NumberValue = 40;
    sheet.Range[currentColumn, 6].NumberValue = 50;
    
    //写入文本并设置区域格式
    currentRow += 2;
    sheet.Range[currentRow, 1].Value = "公式"; 
    sheet.Range[currentRow, 2].Value = "结果";
    CellRange range = sheet.Range[currentRow, 1, currentRow, 2];
    range.Style.Font.IsBold = true;
    range.Style.KnownColor = ExcelColors.LightGreen1;
    range.Style.FillPattern = ExcelPatternType.Solid;
    range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

    步骤 3 :写入函数

    //算术运算
    currentFormula = "=1/2+3*4";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    
    //日期函数
    currentFormula = "=Today()";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    sheet.Range[currentRow, 2].Style.NumberFormat = "YYYY/MM/DD";
    
    //时间函数
    currentFormula = "=NOW()";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    sheet.Range[currentRow, 2].Style.NumberFormat = "H:MM AM/PM";
    
    //IF逻辑函数
    currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    
    //PI函数
    currentFormula = "=PI()";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    
    //三角函数
    currentFormula = "=SIN(PI()/6)";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    
    //计数函数
    currentFormula = "=Count(B1:F1)";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    
    //求最大值函数
    currentFormula = "=MAX(B1:F1)";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    
    //平均值函数
    currentFormula = "=AVERAGE(B1:F1)";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;
    
    //求和函数
    currentFormula = "=SUM(B1:F1)";
    sheet.Range[++currentRow, 1].Text = currentFormula;
    sheet.Range[currentRow, 2].Formula = currentFormula;

    步骤 4 :保存文档

    workbook.SaveToFile("Excel公式.xlsx", FileFormat.Version2013);
    System.Diagnostics.Process.Start("Excel公式.xlsx");

    完成代码后,调试运行程序,生成文档:

    全部代码:

    using Spire.Xls;
    
    namespace CreateFormula
    {
        class Program
        {
            static void Main(string[] args)
            {
                //新建一个工作簿,获取第一张工作表
                Workbook workbook = new Workbook();
                Worksheet sheet = workbook.Worksheets[0];
    
                //初始化currentRow、currentFormula
                int currentColumn = 1;
                int currentRow = 1;
                string currentFormula = string.Empty;
    
                //设置1、2列列宽
                sheet.SetColumnWidth(1, 20);
                sheet.SetColumnWidth(2, 12);
    
                //写入测试数据
                sheet.Range[currentColumn, 1].Value = "测试数据:";
                sheet.Range[currentColumn, 2].NumberValue = 10;
                sheet.Range[currentColumn, 3].NumberValue = 20; 
                sheet.Range[currentColumn, 4].NumberValue = 30;
                sheet.Range[currentColumn, 5].NumberValue = 40;
                sheet.Range[currentColumn, 6].NumberValue = 50;
    
                //写入文本并设置区域格式
                currentRow += 2;
                sheet.Range[currentRow, 1].Value = "公式"; 
                sheet.Range[currentRow, 2].Value = "结果";
                CellRange range = sheet.Range[currentRow, 1, currentRow, 2];
                range.Style.Font.IsBold = true;
                range.Style.KnownColor = ExcelColors.LightGreen1;
                range.Style.FillPattern = ExcelPatternType.Solid;
                range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
    
                //算术运算
                currentFormula = "=1/2+3*4";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //日期函数
                currentFormula = "=Today()";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
                sheet.Range[currentRow, 2].Style.NumberFormat = "YYYY/MM/DD";
    
                //时间函数
                currentFormula = "=NOW()";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
                sheet.Range[currentRow, 2].Style.NumberFormat = "H:MM AM/PM";
    
                //IF逻辑函数
                currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //PI函数
                currentFormula = "=PI()";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //三角函数
                currentFormula = "=SIN(PI()/6)";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //计数函数
                currentFormula = "=Count(B1:F1)";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //求最大值函数
                currentFormula = "=MAX(B1:F1)";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //平均值函数
                currentFormula = "=AVERAGE(B1:F1)";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //求和函数
                currentFormula = "=SUM(B1:F1)";
                sheet.Range[++currentRow, 1].Text = currentFormula;
                sheet.Range[currentRow, 2].Formula = currentFormula;
    
                //保存文档并打开
                workbook.SaveToFile("Excel公式.xlsx", FileFormat.Version2013);
                System.Diagnostics.Process.Start("Excel公式.xlsx");
            }
        }
    }
    View Code

     

    【示例2】读取Excel公式

    步骤 1 :实例化Workbook类,加载测试文档

    Workbook workbook = new Workbook();
    workbook.LoadFromFile("test.xlsx");

    步骤 2 :获取工作表

    Worksheet sheet = workbook.Worksheets[0];

    步骤 3:读取公式

    //遍历[B1:B13]的单元格
    foreach (var cell in sheet.Range["B1:B13"])
    {
        //判断是否含有公式
        if (cell.HasFormula)
        {
            //输出含有公式的单元格及公式
            string certainCell = String.Format("Cell[{0},{1}]", cell.Row, cell.Column);
            Console.WriteLine(certainCell + " 含有公式: " + cell.Formula);
        }
    }
    Console.ReadLine();

    公式读取结果:

    全部代码:

    using Spire.Xls;
    using System;
    
    namespace ReadFormula
    {
        class Program
        {
            static void Main(string[] args)
            {
                //实例化一个Workbook
                Workbook workbook = new Workbook();
    
                //加载测试文档
                workbook.LoadFromFile("test.xlsx");
    
                //获取第一个工作表
                Worksheet sheet = workbook.Worksheets[0];
    
                //遍历[B1:B13]的单元格
                foreach (var cell in sheet.Range["B1:B13"])
                {
                    //判断是否含有公式
                    if (cell.HasFormula)
                    {
                        //输出含有公式的单元格及公式
                        string certainCell = String.Format("Cell[{0},{1}]", cell.Row, cell.Column);
                        Console.WriteLine(certainCell + " 含有公式: " + cell.Formula);
                    }
                }
                Console.ReadLine();
            }
        }
    }
    View Code

     

    以上是本次关于“C# 创建、读取Excel公式”的全部内容。

    (本文完)

     

    转载于:https://www.cnblogs.com/Yesi/p/9707429.html

    展开全文
  • Your Excel data changes frequently, so it’s useful to create a dynamic defined range that automatically expands and contracts to the size of your ... Excel数据经常更改,因此创建动态定义的范围会自动...
  • 对于数据量较大的表格,需要计算一些特殊数值时,我们通过...下面的示例中将分享通过C# 来创建、读取Excel公式的方法。     工具使用 Spire.XLS for .NET 8.0   下载安装该类库后,注意程序添加引...
  • Excel中的数组是指一行、一列或多行多列的一组数据元素的集合,在Excel中用{}进行创建,其中同一行的元素用逗号“,”隔开,不同行的元素用分号“;”隔开。 如下图展示的是一个3行5列的数组: 用Exc...
  • C# 导出Excel 设置公式、格式问题

    千次阅读 2011-02-25 11:37:00
    C#导出Excel时遇到需要自定义公式的问题,总结一下。第一步添加 Microsoft.Office.Interop.Excel 引用,需要安装Excel。代码:创建Excel文件Excel.Application m_objExcel = new Excel.Application(); Excel....
  • 原文:http://coolketang.com/staticOffice/5a97f34f9f545442015d39ed.html1. 本节课将为您演示,如何插入图形,并图形输入计算公式。首先通过公式获得成绩表总分最高的学生姓名。点击选择J3单元格。 2. ...
  • 在Excel中创建默认的工作簿工作表模板sheet.xlt和book.xlt  在Excel 2003可以保存的默认设置相当少,只有“工具→选项”的小部分设置可以被保存下来,对于最常用的页眉页脚、打印页边距、单元格格式...
  • 在EXCEL中创建动态的名称

    千次阅读 2007-04-24 17:56:00
    利用Offset和Counta函数的组合,可以创建一个动态的名称使用方法如下:1 打开定义名称的对话框(Ctrl+F3)2 对话框输入一个指定的名称如:“data”, 引用位置的文本框输入公式:=Offset(Sheet1!$C$3,,,...
  • excel中创建索引目录

    千次阅读 2016-12-03 14:42:34
    创建索引目录
  • Excel如何写如果那么公式,如何写多条的如果那么公式。使用这个方法,只需2分钟,让你以后自己写公式,不用再求人,不用再发帖子到处问。 地址:http://www.excelfb.com/ 点击:Excel自动写公式工具(根据汉字描述...
  • 在excel中使用宏和公式把汉字转换成拼音 参考文献: https://jingyan.baidu.com/article/fc07f9897f480712ffe519e5.html
  • 在创建Excel报表时,我们经常会用到很多公式来计算数据,创建后...下面Excel文档工作表D列的单元格都包含有公式,我选中了其中一个单元格来截图:使用代码前,需要创建一个C#应用程序,下载Free Spire.XLS组件并...
  • 点击上方△蓝字可关注我们会计人2019职场充电站坚持阅读... Excel最常用快捷键 8大类常用公式 一.数字处理1取绝对值=ABS(数字)2数字取整=INT(数字)3数字四舍五入=ROUND(数字,小数位数)二.判断公式4把公式返回的错误...
  • EXCEL表格怎样插入“EMBED”文件是文档还可以看见并打开里面的子文件?①EMBED的语法 { EMBED ClassName [Switches] } 插入支持 OLE 的其他应用程序所创建的对象。使用“插入”菜单的“对象”命令、“编辑”...
  • 操作excel表格用公式来处理数据时,可通过创建公式来运算数据,或通过读取公式来获取数据信息来源。本文以通过Java代码来演示在Excel中创建及读取公式的方法。这里使用了Excel Java类库(Free Spire.XLS for Java ...
  • 用python调用excel文件显示带公式的表格内容,有两种显示结果:第一种是直接显示表格中公式人内容;另一种是显示其表格中公式的结果。 显示第一种,可以这样输入代码: 显示第二种,可以这样输入代码: ...
  • Excel 函数公式大全

    千次阅读 2020-10-08 21:38:08
    excel中内置好的一些公式,只要往公式填写参数即可,能处理、分析、汇总数据 2、最常用函数 求和:sum 平均值:average 计数:count 最大值:max 最小值:min 3、写函数公式时的注意事项 怎么写函数? 怎么...
  • 1、sheet1 ,选中所有数据,进入 公式 - 定义的名称 - 根据所选内容创建对话框中将「最左列」前面方框里的勾取消掉,只保留「首行」前面的勾。 2、进入sheet2 ,选中单元格A1,进入 数据 - 数据工具 - ...
  • Excel中的绝对引用

    2020-02-08 17:28:29
    在Excel中创建公式时,若单元格的绝对单元格引用(例如 $A$1)总是指定位置引用单元格。如果公式所在单元格的位置改变,绝对引用保持不变。如果多行或多列地复制公式,绝对引用将不作调整。默认情况下,新公式...
  • Java编程调用PageOffice实现从空白的excel文件动态生成excel表格,设置公式并填充数据。 集成PageOffice不但能够实现在线编辑、保存真正的Office文档,而且还可以轻松实现Word、Excel文档的动态数据填充、格式控制和...
  • 1. 目标 Excel中,公式形如"A1+B1-C1*D1",网页中将表格单元格也按照Excel方式定位(字母A~Z代表列,数字1-n代表行,字母... 获取公式中的变量;2).计算公式;3).javascript浮点数计算的精度问题(1.4->1.4000...
  • 3.9.4 命名公式中使用单元格和范围引用 3.9.5 使用包含相对引用的命名公式  3.10 使用名称的高级技术 3.10.1 使用INDIRECT函数处理命名范围 3.10.2 使用INDIRECT函数创建带固定地址的命名范围 3.10.3 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 20,383
精华内容 8,153
关键字:

在excel中创建公式