通过HTML/CSS导出excel友好表格 [英] export excel friendly tables via HTML/CSS
问题描述
我想用RStudio格式导出Excel表格. 像openxlsx这样的软件包可以完美地工作，但是我不能在工作环境中使用它们. 无法安装Java或Rtools应用程序.
I would like to export tables for Excel with formatting from RStudio. Packages as openxlsx work perfect, but I cannot use them in my work situation. It is not possible to install Java or Rtools applications.
导出具有Excel格式的表的唯一方法似乎是复制HTML输出(来自Markdown)或打开HTML文件(来自Markdown).
The only way to export tables with formatting for Excel seems to copy HTML output (from Markdown), or open the HTML file (from Markdown).
我可以使用明智的起点吗?例子? 适用于Excel的CSS示例(用于Markdown)?
Are there smart starting points that I can use? Examples? CSS examples (for Markdown) that will work for excel?
目前唯一可行的方法是从RStudio复制HTML:Prview HTML.其他方式对(我的)格式效果不佳.
The only thing that seems to work at the moment is to copy the HTML from the RStudio: Prview HTML. Other ways don't give good results for (my) formatting.
我想做的是标题(第一行)颜色和其余所有行的两种不同颜色.
Things I would like to do is header (first row) color and two different colors for all the rest of the rows.
问题之一是Excel只能使用一种背景色.
One of the problems is that Excel can only work with one background color.
更新:
所以我不得不假设openxlsx对我不起作用. prettydoc软件包中的architect.css文件显示了相同的结果.请参阅降价代码. RStudio预览中的输出表很好. 也在浏览器中.复制到Excel仅在RStudio预览中有效.
So I have to assume that openxlsx will not work for me. The architect.css file from the prettydoc package is showing the same result. See the Markdown code. The output table in the RStudio preview is good. Also in browsers. Copying to Excel is only working from the RStudio preview.
在Excel中打开HTML文件时，Excel仅显示一种背景色. 在这种情况下为白色.
When you open the HTML-file in Excel, Excel is only showing one background color. White in this case.
我正在寻找(我认为)的是更基本的CSS，重点放在表格样式上. (具有两种背景色，如:nth-child(n)).
What I am looking for (I think) is more basic CSS, with a focus on table style. (with two background colors as by :nth-child(n)).
<style>
/*! normalize.css v3.0.2 | MIT License | git.io/normalize */
/**
* 1. Set default font family to sans-serif.
* 2. Prevent iOS text size adjust after orientation change, without disabling
* user zoom.
*/
@font-face {
font-family: 'Open Sans';
font-style: normal;
font-weight: 400;
src: local('Open Sans'), local('OpenSans'), url('fonts/open-sans-400.woff') format('woff');
}
@font-face {
font-family: 'Open Sans';
font-style: normal;
font-weight: 700;
src: local('Open Sans Bold'), local('OpenSans-Bold'), url('fonts/open-sans-700.woff') format('woff');
}
html {
font-family: sans-serif; /* 1 */
-webkit-text-size-adjust: 100%; /* 2 */
-ms-text-size-adjust: 100%; /* 2 */
}
/**
* Remove default margin.
*/
body {
margin: 0;
}
/* HTML5 display definitions
========================================================================== */
/**
* Correct `block` display not defined for any HTML5 element in IE 8/9.
* Correct `block` display not defined for `details` or `summary` in IE 10/11
* and Firefox.
* Correct `block` display not defined for `main` in IE 11.
*/
article,
aside,
details,
figcaption,
figure,
footer,
header,
hgroup,
main,
menu,
nav,
section,
summary {
display: block;
}
/**
* 1. Correct `inline-block` display not defined in IE 8/9.
* 2. Normalize vertical alignment of `progress` in Chrome, Firefox, and Opera.
*/
audio,
canvas,
progress,
video {
display: inline-block; /* 1 */
vertical-align: baseline; /* 2 */
}
/**
* Prevent modern browsers from displaying `audio` without controls.
* Remove excess height in iOS 5 devices.
*/
audio:not([controls]) {
display: none;
height: 0;
}
/**
* Address `[hidden]` styling not present in IE 8/9/10.
* Hide the `template` element in IE 8/9/11, Safari, and Firefox < 22.
*/
[hidden],
template {
display: none;
}
/* Links
========================================================================== */
/**
* Remove the gray background color from active links in IE 10.
*/
a {
background-color: transparent;
}
/**
* Improve readability when focused and also mouse hovered in all browsers.
*/
a:active,
a:hover {
outline: 0;
}
/* Text-level semantics
========================================================================== */
/**
* Address styling not present in IE 8/9/10/11, Safari, and Chrome.
*/
abbr[title] {
border-bottom: 1px dotted;
}
/**
* Address style set to `bolder` in Firefox 4+, Safari, and Chrome.
*/
b,
strong {
font-weight: bold;
}
/**
* Address styling not present in Safari and Chrome.
*/
dfn {
font-style: italic;
}
/**
* Address variable `h1` font-size and margin within `section` and `article`
* contexts in Firefox 4+, Safari, and Chrome.
*/
h1 {
margin: 0.67em 0;
font-size: 2em;
}
/**
* Address styling not present in IE 8/9.
*/
mark {
color: #000;
background: #ff0;
}
/**
* Address inconsistent and variable font size in all browsers.
*/
small {
font-size: 80%;
}
/**
* Prevent `sub` and `sup` affecting `line-height` in all browsers.
*/
sub,
sup {
position: relative;
font-size: 75%;
line-height: 0;
vertical-align: baseline;
}
sup {
top: -0.5em;
}
sub {
bottom: -0.25em;
}
/* Embedded content
========================================================================== */
/**
* Remove border when inside `a` element in IE 8/9/10.
*/
img {
border: 0;
}
/**
* Correct overflow not hidden in IE 9/10/11.
*/
svg:not(:root) {
overflow: hidden;
}
/* Grouping content
========================================================================== */
/**
* Address margin not present in IE 8/9 and Safari.
*/
figure {
margin: 1em 40px;
}
/**
* Address differences between Firefox and other browsers.
*/
hr {
height: 0;
-moz-box-sizing: content-box;
box-sizing: content-box;
}
/**
* Contain overflow in all browsers.
*/
pre {
overflow: auto;
}
/**
* Address odd `em`-unit font size rendering in all browsers.
*/
code,
kbd,
pre,
samp {
font-family: monospace, monospace;
font-size: 1em;
}
/* Tables
========================================================================== */
/**
* Remove most spacing between table cells.
*/
table {
border-spacing: 0;
border-collapse: collapse;
width: 50%;
margin: 20px auto;
}
th, td {
border-bottom: 1px solid #bbbbbb;
text-align: left;
padding: 10px;
}
th {
background-color: #63a0e1;
color: white;
}
tr:nth-child(odd) {
background-color: #eeeeee;
}
tr:nth-child(even) {
background-color: #ffffff;
}
/* LAYOUT STYLES */
body {
font-family: 'Open Sans', 'Helvetica Neue', Helvetica, Arial, sans-serif;
font-size: 16px;
font-weight: 400;
line-height: 1.5;
color: #666;
background: #fafafa url(images/body-bg.jpg) 0 0 repeat;
}
p {
margin-top: 0;
}
a {
color: #2879d0;
}
a:hover {
color: #2268b2;
}
header {
padding-top: 40px;
padding-bottom: 20px;
background: #2e7bcf url(images/header-bg.jpg) 0 0 repeat-x;
border-bottom: solid 1px #275da1;
text-align: center;
}
header h1 {
margin-top: 0;
margin-bottom: 0.5em;
font-size: 2em;
font-weight: bold;
line-height: 1;
color: #fff;
letter-spacing: -1px;
}
header h2 {
margin-top: 0;
margin-bottom: 1em;
font-size: 1.5em;
font-weight: normal;
line-height: 1.3;
color: #9ddcff;
letter-spacing: 0;
}
header h3 {
margin-top: 0;
margin-bottom: 1em;
font-size: 1.2em;
font-weight: normal;
line-height: 1.2;
color: #9ddcff;
letter-spacing: 0;
}
.inner, .toc {
position: relative;
width: 840px;
font-size: 1.1em;
margin: 0 auto;
}
.toc {
padding-top: 1em;
padding-bottom: 0;
}
.toc ul {
margin-bottom: 0;
}
#content-wrapper {
padding-top: 30px;
border-top: solid 1px #fff;
}
#main-content img {
max-width: 100%;
}
code {
padding: 0 3px;
background-color: #f2f8fc;
border: solid 1px #dbe7f3;
}
pre {
padding: 20px;
overflow: auto;
text-shadow: none;
background: #fff;
border: solid 1px #f2f2f2;
font-size: 0.9em;
}
pre code {
padding: 0;
color: #2879d0;
background-color: #fff;
border: none;
}
ul, ol, dl {
margin-bottom: 20px;
}
/* COMMON STYLES */
hr {
height: 1px;
margin-top: 1em;
margin-bottom: 1em;
border: 0;
background: #aaa;
background-image: linear-gradient(to right, #eee, #aaa, #eee);
}
form {
padding: 20px;
background: #f2f2f2;
}
/* GENERAL ELEMENT TYPE STYLES */
#main-content h1 {
margin-top: 0;
margin-bottom: 0;
font-size: 2em;
font-weight: bold;
color: #474747;
letter-spacing: -1px;
}
#main-content h1:before {
padding-right: 0.3em;
margin-left: -0.8em;
color: #9ddcff;
content: "/";
}
#main-content h2 {
margin-bottom: 8px;
font-size: 1.5em;
font-weight: bold;
color: #474747;
}
#main-content h2:before {
padding-right: 0.3em;
margin-left: -1.2em;
content: "//";
color: #9ddcff;
}
#main-content h3 {
margin-top: 24px;
margin-bottom: 8px;
font-size: 1.2em;
font-weight: bold;
color: #474747;
}
#main-content h3:before {
padding-right: 0.3em;
margin-left: -1.7em;
content: "///";
color: #9ddcff;
}
#main-content h4 {
margin-bottom: 8px;
font-size: 1.1em;
font-weight: bold;
color: #474747;
}
h4:before {
padding-right: 0.3em;
margin-left: -2em;
content: "////";
color: #9ddcff;
}
#main-content h5 {
margin-bottom: 8px;
font-size: 1em;
color: #474747;
}
h5:before {
padding-right: 0.3em;
margin-left: -2.4em;
content: "/////";
color: #9ddcff;
}
#main-content h6 {
margin-bottom: 8px;
font-size: 0.9em;
color: #474747;
}
h6:before {
padding-right: 0.3em;
margin-left: -3em;
content: "//////";
color: #9ddcff;
}
p {
margin-bottom: 20px;
}
a {
text-decoration: none;
}
p a {
font-weight: 400;
}
blockquote {
padding: 0 0 0 30px;
margin-bottom: 20px;
font-size: 1.1em;
border-left: 10px solid #e9e9e9;
}
ul, ol {
padding-left: 30px;
}
dl dd {
font-style: italic;
font-weight: 100;
}
/* MISC */
.clearfix:after {
display: block;
height: 0;
clear: both;
visibility: hidden;
content: '.';
}
.clearfix {display: inline-block;}
* html .clearfix {height: 1%;}
.clearfix {display: block;}
/* #Media Queries
================================================== */
/* All Mobile Sizes (devices and browser) */
@media only screen and (max-width: 850px) {
.toc, .inner {
width: 93%;
font-size: 1em;
}
header {
padding: 10px 0;
}
header h1, header h2 {
width: 100%;
}
header h1 {
font-size: 1.75em;
}
header h2 {
font-size: 1.2em;
}
header h3 {
font-size: 1em;
}
#main-content h1:before,
#main-content h2:before,
#main-content h3:before,
#main-content h4:before,
#main-content h5:before,
#main-content h6:before {
padding-right: 0;
margin-left: 0;
content: none;
}
}
</style>
```{r, echo=FALSE, results='asis'}
knitr::kable(head(iris, 10), full_width = F)
```
推荐答案
对不起，我不想炫耀. 对于具有我已知知识的人(我认为)，很难在Internet上找到有关此信息.
Sorry, I don't want to show off or something. Information about this is hard to find on the internet, for somebody with my knownledge (I think).
但是在谷歌搜索之后，我不得不接受从HTML/CSS到Excel的转换不可能实现其余所有行都有两种不同的颜色".
But after googling some more I have to accept that "two different colors for all the rest of the rows" is not possible in a conversion from HTML/CSS to Excel.
If this is true, then Excel can only deal with CSS2 selectors.
And with CSS2 selectors you can not select every other row.
这篇关于通过HTML/CSS导出excel友好表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
