java中将查询数据导出_如何在R中将数据框导出到Excel

柔情只为你懂 2023-03-01 06:21 115阅读 0赞

java中将查询数据导出

What if I tell, that you can export data frames to excel in R within a couple of minutes? This is possible with the help of functions like **writexl** and **openxlsx.** Seems interesting? In this tutorial, we are going to see how we can export data frames to excel in R.

如果我告诉您，您可以在几分钟之内将数据帧导出到R中的excel，该怎么办？ 这可以通过**writexl**和**openxlsx之**类的功能**来** **实现。** 看起来很有趣？ 在本教程中，我们将看到如何在R中将数据帧导出到excel。

Before we dive into the tutorials, let’s understand what is writexl and openxlsx functions.

在深入学习教程之前，让我们了解什么是writexl和openxlsx函数。

## 从安装所需的软件包开始 **(****Start with installing required packages****)** ##

**writexl:** writexl is a library in R, which is capable of writing R data frames into excel files. To install this package, run the below code.

**writexl：** writexl是R中的一个库，能够将R数据帧写入excel文件。 要安装此软件包，请运行以下代码。

install.packages('writexl')

**Openxlsx:** The R library which is very helpful in manipulating the data and export to Excel. To install this package, run the below code.

**Openxlsx：** R库，它在处理数据并将其导出到Excel中非常有用。 要安装此软件包，请运行以下代码。

install.packages('openxlsx')

--------------------

## 创建数据框并导出到Excel **(****Create a data frame and Export to Excel****)** ##

Well, I hope you have successfully installed those packages. Now let’s create a data frame in R and export it to Excel using **‘writexl’** function.

好吧，希望您已经成功安装了这些软件包。 现在，让我们在R中创建一个数据框，并使用**'writexl'**函数将其导出到Excel。

#creates a dataframe in R
    mydataframe<- data.frame(name=c('John','Angelina','Lisa','Joseph','Antonio'), 
    age=c(32,30,26,23,28),
    gender=c('male','female','female','male','male'))

After creating this data frame, let’s export it to Excel using the **writexl** function.

创建此数据框后，让我们使用**writexl**函数将其导出到Excel。

#import the required library
    library(writexl)
    #exports the data to excel and dumps it to the working directory/folder
    #to see your working directory run 'getwd()' in R studio
    
    mydataframe<- data.frame(name=c('John','Angelina','Lisa','Joseph','Antonio'), 
    age=c(32,30,26,23,28),
    gender=c('male','female','female','male','male'))
    
    write_xlsx(mydataframe, 'export_a_dataframe_to_excel.xlsx')

The output excel file is shown below.

输出的excel文件如下所示。

![Exporting A Datafame In R To Excel][]

--------------------

## 在R中导入数据集并将其导出到Excel **(****Import a dataset in R and export it to Excel** **)** ##

You have exported a simple data frame in R to excel in the above section. Here we are going to import a data set or a CSV file and export it to Excel file.

在上一节中，您已经在R中导出了一个简单的数据框以使其表现出色。 在这里，我们将导入数据集或CSV文件，并将其导出到Excel文件。

#imports the iris data set
    x<-datasets::iris
    View(x) 
    #exports the data frames to excel 
    write_xlsx(x, 'Exporitng_a_csv_file_to_excel.xlsx')

![Data Frame To Excel][]

--------------------

## 通过使用openxlsx处理数据将数据框导出到Excel **(****Export a data frame to Excel by manipulating data using openxlsx****)** ##

Till now, we have exported the data frames to Excel without any data manipulations. But what if you need to export the data with some meaningful changes? There comes the use of functions **‘dplyr’** and **‘openxlsx’**.

到目前为止，我们无需任何数据操作即可将数据框导出到Excel。 但是，如果您需要导出数据并进行一些有意义的更改怎么办？ 函数**'dplyr'**和**'openxlsx'的使用** 。

**Dplyr** library will assist you in data manipulation i.e. you can make changes to the dataset by adding a column, a row, or a percentage and more.

**Dplyr**库将帮助您进行数据操作，即，您可以通过添加列，行或百分比或更多来更改数据集。

**Openxlsx** function allows you to make all possible changes such as adding a style, font, color and you can also create a worksheet using this function.

**Openxlsx**函数允许您进行所有可能的更改，例如添加样式，字体，颜色，还可以使用此函数创建工作表。

The below image shows the basic data set available in R.

下图显示了R中可用的基本数据集。

![Openxlsx Function In R][]

Let’s make some changes in the header of the data frame using dplyr. Here we are changing the color of headlines and alignment to center. We have also changed the values in the Illiteracy column as percentages.

让我们使用dplyr对数据帧的标题进行一些更改。 在这里，我们正在更改标题的颜色和居中对齐。 我们还更改了“文盲”列中的百分比值。

Execute the below code to create excel file with new header style.

执行以下代码以创建具有新标题样式的excel文件。

#imports the required libraries
    library(dplyr)
    library(rio)
    library(openxlsx)
    
    #creates a data frame with percentage and manipulated it using dplyr
    mydata<- as.data.frame(state.x77)%>%
     mutate(State=row.names(state.x77),Illiteracy=Illiteracy/100)%>%
     select(State, Population:Area)
    
    #creates a new headline style
    my_headline<-createStyle(halign = "center",textDecoration = "Bold",fontSize = 14,fontColour = "blue")
    
    #changes the headline style
    write.xlsx(mydata, "new_header_style.xlsx", headerStyle=my_headline)
    #chnages the values in illeteracy column as percentages 
    class(mydata$Illiteracy)<-"percentage"
    
    #exports the excel file with new header style and percentage of values
    write.xlsx(mydata, 'with_percentage_stye.xlsx', headerStyle=my_headline)

The data frame with a new headline style and alignment style is shown below.

具有新标题样式和对齐样式的数据框如下所示。

![New Header Style][]

--------------------

### 在R中创建新的表格样式并导出到Excel **(****Create a new table style in R and export to Excel** **)** ###

You can also change the table theme or style by adding this piece of code to the above code.

您还可以通过将这段代码添加到上面的代码中来更改表格的主题或样式。

#creates a new table style or theme
    write.xlsx(mydata, "new_table_style.xlsx", asTable = T, tableStyle='TableStyleLight1')

![New Table Style In R 1][]

Alright, we have created data frames, imported data frames, and exported to excel using the writexl and openxlsx functions. We did data manipulation and exported the data frame with a new look to excel.

好了，我们已经创建了数据框，导入了数据框，并使用writexl和openxlsx函数将其导出到excel。 我们进行了数据处理，并以崭新的外观导出了数据框。

But, there are some other possibilities that you can do in R. Let’s look into that.

但是，您可以在R中进行其他操作。让我们来研究一下。

--------------------

## 我们可以在R中创建工作簿并导出到Excel吗？ **(****Can we create a workbook in R and Export to Excel?****)** ##

The answer will be a big **‘YES’.** You can create an entire workbook and add worksheets into that with some data along with some changes which will be meaningful and make sense to readers.

答案将是**“是”。** 您可以创建一个完整的工作簿，并向其中添加工作表以及一些数据和一些有意义且对读者有意义的更改。

Let’s create!!!

让我们创造！！！

#creates a new workbook with new worksheet
    myworkbook<-createWorkbook()
    addWorksheet(myworkbook,"MySheet")
    writeData(myworkbook,"MySheet",mydata)
     
    #adds the different nemurical styles and the number formates to data frame 
    my_dollar_style<-createStyle(numFmt = "$0,0")
    my_percentage_style<-createStyle(numFmt = "0.0%")
    my_comma_style<-createStyle(numFmt = "0,0")
     
    #incorporates the styles to data
    addStyle(myworkbook, "MySheet", style = my_dollar_style, rows = 2:nrow(mydata),cols = 3)
    addStyle(myworkbook,"MySheet",style = my_percentage_style,rows = 2:nrow(mydata),cols = 4)
    addStyle(myworkbook,"MySheet",style = my_comma_style,rows = 2:nrow(mydata), cols = 9)
    
    #save the workbook with new number formats as well
    saveWorkbook(myworkbook,"with_numerical_styling.xlsx", overwrite = T)

In the below workbook, you can see the income column has been added with a dollar sign and, values are separated by commas. These numerical format changes will make sense and convey the information in a more detailed way.

在下面的工作簿中，您可以看到收入列中添加了美元符号，并且值之间用逗号分隔。 这些数字格式的更改将是有意义的，并以更详细的方式传达信息。

![New Workbook In R][]

--------------------

## 结论 **(****Conclusion****)** ##

R is a perfect language for data analysis. You can do wonders using data in R. In this tutorial we have gone through the various ways of exporting a data frame to excel. You got to know how you can manipulate the data and bring some life into that before exporting to excel.

R是用于数据分析的理想语言。 您可以在R中使用数据来创造奇迹。在本教程中，我们介绍了将数据框导出为excel的各种方法。 在导出到excel之前，您必须了解如何处理数据并为其中带来一些生气。

That’s all for now, stay connected for more R tutorials and I am sure that we can continue playing with some more data the next time. **Happy exporting!!!**

目前仅此而已，请保持联系以获取更多R教程，我相信我们下次可以继续使用更多数据。 **出口愉快！！！**

> 翻译自: [https://www.journaldev.com/39927/export-data-frame-excel-in-r][https_www.journaldev.com_39927_export-data-frame-excel-in-r]

java中将查询数据导出

[Exporting A Datafame In R To Excel]: /images/20230209/809db530fe0544d4bf9fc6becbe4e82e.png
[Data Frame To Excel]: /images/20230209/19fac5e62cac498a8ad58cb86a4f4cad.png
[Openxlsx Function In R]: /images/20230209/291986fc7e23499cb541f7287d112237.png
[New Header Style]: /images/20230209/806231fa7e4043d981dfa92f45a336d6.png
[New Table Style In R 1]: /images/20230209/0fa599d089ff4a42a5091697c15cbca9.png
[New Workbook In R]: /images/20230209/9270ad55aac246f29e9b105842a14d28.png
[https_www.journaldev.com_39927_export-data-frame-excel-in-r]: https://www.journaldev.com/39927/export-data-frame-excel-in-r