public XSSFColor(Color clr)
: this()
{
var c = clr.ToPixel <Rgb24>();
ctColor.SetRgb(c.R, c.G, c.B);
}
public void TestRgbColor()
{
CT_Font ctFont = new CT_Font();
CT_Color color = ctFont.AddNewColor();
//Integer.toHexString(0xFFFFFF).getBytes() = [102, 102, 102, 102, 102, 102]
color.SetRgb(Encoding.ASCII.GetBytes("ffffff"));
ctFont.SetColorArray(0, color);
XSSFFont xssfFont = new XSSFFont(ctFont);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[0], xssfFont.GetXSSFColor().RGB[0]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[1], xssfFont.GetXSSFColor().RGB[1]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], xssfFont.GetXSSFColor().RGB[2]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[3], xssfFont.GetXSSFColor().RGB[3]);
xssfFont.Color = ((short)23);
byte[] bytes = Encoding.ASCII.GetBytes(HexDump.ToHex(0xF1F1F1));
color.rgb = (bytes);
XSSFColor newColor = new XSSFColor(color);
xssfFont.SetColor(newColor);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], newColor.RGB[2]);
CollectionAssert.AreEqual(bytes, xssfFont.GetXSSFColor().RGB);
Assert.AreEqual(0, xssfFont.Color);
}
public void TestRgbColor()
{
CT_Font ctFont = new CT_Font();
CT_Color color = ctFont.AddNewColor();
//Integer.toHexString(0xFFFFFF).getBytes() = [102, 102, 102, 102, 102, 102]
color.SetRgb(Encoding.ASCII.GetBytes("ffffff"));
ctFont.SetColorArray(0, color);
XSSFFont xssfFont = new XSSFFont(ctFont);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[0], xssfFont.GetXSSFColor().GetRgb()[0]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[1], xssfFont.GetXSSFColor().GetRgb()[1]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], xssfFont.GetXSSFColor().GetRgb()[2]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[3], xssfFont.GetXSSFColor().GetRgb()[3]);
//Integer.toHexString(0xF1F1F1).getBytes() = [102, 49, 102, 49, 102, 49]
color.SetRgb(Encoding.ASCII.GetBytes("f1f1f1"));
XSSFColor newColor = new XSSFColor(color);
xssfFont.SetColor(newColor);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], newColor.GetRgb()[2]);
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
/// <param name="startAddress"></param>
/// <param name="dataTable"></param>
/// <param name="iWorkbook"></param>
public void FillDataFromTable(XSSFWorkbook iWorkbook, XSSFSheet sheet, string startAddress, DataTable dataTable)
{
XSSFCellStyle headerCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
CT_Color ctColor = new CT_Color();
ctColor.SetRgb(112, 173, 71);
XSSFColor xssfColor = new XSSFColor(ctColor);
headerCellStyle.SetFillBackgroundColor(xssfColor);
headerCellStyle.SetFillForegroundColor(xssfColor);
XSSFFont hssfFont = iWorkbook.CreateFont() as XSSFFont;
hssfFont.FontHeightInPoints = 10;
hssfFont.FontName = "宋体";
hssfFont.Boldweight = 700;
headerCellStyle.SetFont(hssfFont);
XSSFCellStyle contentCellStyle = (XSSFCellStyle)iWorkbook.CreateCellStyle();
XSSFFont contentHssfFont = iWorkbook.CreateFont() as XSSFFont;
contentHssfFont.FontHeightInPoints = 10;
contentHssfFont.FontName = "宋体";
contentCellStyle.SetFont(contentHssfFont);
string rowIndexStr = string.Empty;
string cellIndexStr = string.Empty;
int cellIndex = 0;
for (int i = 0; i < startAddress.Length; i++)
{
int tempNum;
if (int.TryParse(startAddress[i].ToString(), out tempNum))
{
rowIndexStr += "" + tempNum;
}
else
{
cellIndexStr += "" + startAddress[i];
}
}
var rowIndex = Convert.ToInt32(rowIndexStr);
for (int i = cellIndexStr.Length - 1; i >= 0; i--)
{
if (i == cellIndexStr.Length - 1)
{
cellIndex += cellIndexStr[i] - 65;
}
else
{
cellIndex += (cellIndexStr[i] - 64) * 26;
}
}
cellIndex = 0;
//textBox1.Text += "\r\n 共有数据:" + _DataTable.Rows.Count;
int tempCellIndex = cellIndex;
try
{
//sheet分开包含表头
if (!ckbSheet.Checked)
{
rowIndex = sheet.LastRowNum;
if (rowIndex != 0)
{
rowIndex++;
}
}
//是否包含表头
XSSFRow excelRow;
XSSFCell excelCell;
if (ckbIsIncludeHeader.Checked && rowIndex <= 1)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (string.IsNullOrEmpty(dataColumn.ColumnName))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataColumn.ColumnName));
excelCell.CellStyle = headerCellStyle;
tempCellIndex++;
}
}
rowIndex++;
tempCellIndex = cellIndex;
}
//填充数据
foreach (DataRow dataRow in dataTable.Rows)
{
excelRow = sheet.GetRow(rowIndex) as XSSFRow ?? sheet.CreateRow(rowIndex) as XSSFRow;
excelRow.HeightInPoints = 20;
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelCell = excelRow.GetCell(tempCellIndex) as XSSFCell;
if (excelCell == null)
{
excelCell = excelRow.CreateCell(tempCellIndex) as XSSFCell;
}
if (dataRow[dataColumn] == DBNull.Value || string.IsNullOrEmpty(Convert.ToString(dataRow[dataColumn])))
{
excelCell.SetCellType(CellType.Blank);
excelCell.CellStyle = contentCellStyle;
tempCellIndex++;
continue;
}
if (dataRow[dataColumn] is decimal || dataRow[dataColumn] is int)
{
excelCell.SetCellType(CellType.Numeric);
excelCell.SetCellValue(Convert.ToDouble(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
else
{
excelCell.SetCellType(CellType.String);
excelCell.SetCellValue(Convert.ToString(dataRow[dataColumn]));
excelCell.CellStyle = contentCellStyle;
}
tempCellIndex++;
}
tempCellIndex = cellIndex;
rowIndex++;
}
}
catch (Exception ex)
{
throw new Exception("行" + rowIndex + "列" + tempCellIndex + "_" + ex.Message, ex);
}
}
/**
*
* CTRPrElt --> CTFont adapter
*/
protected static CT_Font ToCTFont(CT_RPrElt pr)
{
CT_Font ctFont = new CT_Font();
if (pr.SizeOfBArray() > 0)
{
ctFont.AddNewB().val = (pr.GetBArray(0).val);
}
if (pr.SizeOfUArray() > 0)
{
ctFont.AddNewU().val = (pr.GetUArray(0).val);
}
if (pr.SizeOfIArray() > 0)
{
ctFont.AddNewI().val = (pr.GetIArray(0).val);
}
if (pr.SizeOfColorArray() > 0)
{
CT_Color c1 = pr.GetColorArray(0);
CT_Color c2 = ctFont.AddNewColor();
if (c1.IsSetAuto())
{
c2.auto = (c1.auto);
c2.autoSpecified = true;
}
if (c1.IsSetIndexed())
{
c2.indexed = (c1.indexed);
c2.indexedSpecified = true;
}
if (c1.IsSetRgb())
{
c2.SetRgb(c1.GetRgb());
c2.rgbSpecified = true;
}
if (c1.IsSetTheme())
{
c2.theme = (c1.theme);
c2.themeSpecified = true;
}
if (c1.IsSetTint())
{
c2.tint = (c1.tint);
c2.tintSpecified = true;
}
}
if (pr.SizeOfSzArray() > 0)
{
ctFont.AddNewSz().val = (pr.GetSzArray(0).val);
}
if (pr.SizeOfRFontArray() > 0)
{
ctFont.AddNewName().val = (pr.GetRFontArray(0).val);
}
if (pr.SizeOfFamilyArray() > 0)
{
ctFont.AddNewFamily().val = (pr.GetFamilyArray(0).val);
}
if (pr.sizeOfSchemeArray() > 0)
{
ctFont.AddNewScheme().val = (pr.GetSchemeArray(0).val);
}
if (pr.sizeOfCharsetArray() > 0)
{
ctFont.AddNewCharset().val = (pr.GetCharsetArray(0).val);
}
if (pr.sizeOfCondenseArray() > 0)
{
ctFont.AddNewCondense().val = (pr.GetCondenseArray(0).val);
}
if (pr.sizeOfExtendArray() > 0)
{
ctFont.AddNewExtend().val = (pr.GetExtendArray(0).val);
}
if (pr.sizeOfVertAlignArray() > 0)
{
ctFont.AddNewVertAlign().val = (pr.GetVertAlignArray(0).val);
}
if (pr.sizeOfOutlineArray() > 0)
{
ctFont.AddNewOutline().val = (pr.GetOutlineArray(0).val);
}
if (pr.sizeOfShadowArray() > 0)
{
ctFont.AddNewShadow().val = (pr.GetShadowArray(0).val);
}
if (pr.sizeOfStrikeArray() > 0)
{
ctFont.AddNewStrike().val = (pr.GetStrikeArray(0).val);
}
return(ctFont);
}
/**
* Copy font attributes from CTFont bean into CTRPrElt bean
*/
private void SetRunAttributes(CT_Font ctFont, CT_RPrElt pr)
{
if (ctFont.SizeOfBArray() > 0)
{
pr.AddNewB().val = (ctFont.GetBArray(0).val);
}
if (ctFont.sizeOfUArray() > 0)
{
pr.AddNewU().val = (ctFont.GetUArray(0).val);
}
if (ctFont.sizeOfIArray() > 0)
{
pr.AddNewI().val = (ctFont.GetIArray(0).val);
}
if (ctFont.sizeOfColorArray() > 0)
{
CT_Color c1 = ctFont.GetColorArray(0);
CT_Color c2 = pr.AddNewColor();
if (c1.IsSetAuto())
{
c2.auto = (c1.auto);
c2.autoSpecified = true;
}
if (c1.IsSetIndexed())
{
c2.indexed = (c1.indexed);
c2.indexedSpecified = true;
}
if (c1.IsSetRgb())
{
c2.SetRgb(c1.rgb);
c2.rgbSpecified = true;
}
if (c1.IsSetTheme())
{
c2.theme = (c1.theme);
c2.themeSpecified = true;
}
if (c1.IsSetTint())
{
c2.tint = (c1.tint);
c2.tintSpecified = true;
}
}
if (ctFont.sizeOfSzArray() > 0)
{
pr.AddNewSz().val = (ctFont.GetSzArray(0).val);
}
if (ctFont.sizeOfNameArray() > 0)
{
pr.AddNewRFont().val = (ctFont.name.val);
}
if (ctFont.sizeOfFamilyArray() > 0)
{
pr.AddNewFamily().val = (ctFont.GetFamilyArray(0).val);
}
if (ctFont.sizeOfSchemeArray() > 0)
{
pr.AddNewScheme().val = (ctFont.GetSchemeArray(0).val);
}
if (ctFont.sizeOfCharsetArray() > 0)
{
pr.AddNewCharset().val = (ctFont.GetCharsetArray(0).val);
}
if (ctFont.sizeOfCondenseArray() > 0)
{
pr.AddNewCondense().val = (ctFont.GetCondenseArray(0).val);
}
if (ctFont.sizeOfExtendArray() > 0)
{
pr.AddNewExtend().val = (ctFont.GetExtendArray(0).val);
}
if (ctFont.sizeOfVertAlignArray() > 0)
{
pr.AddNewVertAlign().val = (ctFont.GetVertAlignArray(0).val);
}
if (ctFont.sizeOfOutlineArray() > 0)
{
pr.AddNewOutline().val = (ctFont.GetOutlineArray(0).val);
}
if (ctFont.sizeOfShadowArray() > 0)
{
pr.AddNewShadow().val = (ctFont.GetShadowArray(0).val);
}
if (ctFont.sizeOfStrikeArray() > 0)
{
pr.AddNewStrike().val = (ctFont.GetStrikeArray(0).val);
}
}
public XSSFColor(Color clr)
: this()
{
ctColor.SetRgb((byte)clr.R, (byte)clr.G, (byte)clr.B);
}
public XSSFColor(byte[] rgb)
: this()
{
ctColor.SetRgb(rgb);
}
private void SetRunAttributes(CT_Font ctFont, CT_RPrElt pr)
{
if (ctFont.sizeOfBArray() > 0)
{
pr.AddNewB().val = ctFont.GetBArray(0).val;
}
if (ctFont.sizeOfUArray() > 0)
{
pr.AddNewU().val = ctFont.GetUArray(0).val;
}
if (ctFont.sizeOfIArray() > 0)
{
pr.AddNewI().val = ctFont.GetIArray(0).val;
}
if (ctFont.sizeOfColorArray() > 0)
{
CT_Color colorArray = ctFont.GetColorArray(0);
CT_Color ctColor = pr.AddNewColor();
if (colorArray.IsSetAuto())
{
ctColor.auto = colorArray.auto;
ctColor.autoSpecified = true;
}
if (colorArray.IsSetIndexed())
{
ctColor.indexed = colorArray.indexed;
ctColor.indexedSpecified = true;
}
if (colorArray.IsSetRgb())
{
ctColor.SetRgb(colorArray.rgb);
ctColor.rgbSpecified = true;
}
if (colorArray.IsSetTheme())
{
ctColor.theme = colorArray.theme;
ctColor.themeSpecified = true;
}
if (colorArray.IsSetTint())
{
ctColor.tint = colorArray.tint;
ctColor.tintSpecified = true;
}
}
if (ctFont.sizeOfSzArray() > 0)
{
pr.AddNewSz().val = ctFont.GetSzArray(0).val;
}
if (ctFont.sizeOfNameArray() > 0)
{
pr.AddNewRFont().val = ctFont.GetNameArray(0).val;
}
if (ctFont.sizeOfFamilyArray() > 0)
{
pr.AddNewFamily().val = ctFont.GetFamilyArray(0).val;
}
if (ctFont.sizeOfSchemeArray() > 0)
{
pr.AddNewScheme().val = ctFont.GetSchemeArray(0).val;
}
if (ctFont.sizeOfCharsetArray() > 0)
{
pr.AddNewCharset().val = ctFont.GetCharsetArray(0).val;
}
if (ctFont.sizeOfCondenseArray() > 0)
{
pr.AddNewCondense().val = ctFont.GetCondenseArray(0).val;
}
if (ctFont.sizeOfExtendArray() > 0)
{
pr.AddNewExtend().val = ctFont.GetExtendArray(0).val;
}
if (ctFont.sizeOfVertAlignArray() > 0)
{
pr.AddNewVertAlign().val = ctFont.GetVertAlignArray(0).val;
}
if (ctFont.sizeOfOutlineArray() > 0)
{
pr.AddNewOutline().val = ctFont.GetOutlineArray(0).val;
}
if (ctFont.sizeOfShadowArray() > 0)
{
pr.AddNewShadow().val = ctFont.GetShadowArray(0).val;
}
if (ctFont.sizeOfStrikeArray() <= 0)
{
return;
}
pr.AddNewStrike().val = ctFont.GetStrikeArray(0).val;
}
public XSSFColor(System.Drawing.Color clr)
: this()
{
ctColor.SetRgb((byte)clr.R, (byte)clr.G, (byte)clr.B);
}