public void Test40520_3()
{
short font = 3;
HSSFRichTextString r = new HSSFRichTextString("f0_123456789012345678901234567890123456789012345678901234567890");
// wrong order => will format 0-6
r.ApplyFont(0, 2, font);
r.ApplyFont(5, 7, font);
r.ApplyFont(0, 2, font);
r.ApplyFont(0, 2, font);
for (int i = 0; i < 2; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 2; i < 5; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
for (int i = 5; i < 7; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 7; i < r.Length; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
}
/// <summary>
/// 对单元格赋值，设置时间下划线
/// </summary>
/// <param name="workbook">需要添加下划线的工作簿</param>
/// <param name="row">单元格行号</param>
/// <param name="row">单元格列号</param>
/// <param name="cell"></param>
/// <param name="vaule"></param>
/// <param name="startTime">起始日期</param>
/// <param name="endTime">截止日期</param>
public static void SetDateTimeUnderline(this IWorkbook workbook, int row, int cell, string vaule, DateTime startTime, DateTime endTime)
{
string startYear = startTime.Year.ToString();
string startMonth = startTime.Month.ToString();
string startDay = startTime.Day.ToString();
string endYear = endTime.Year.ToString();
string endMonth = endTime.Month.ToString();
string endDay = endTime.Day.ToString();
string strValue = $"{startYear}年{startMonth}月{startDay}日至{endYear}年{endMonth}月{endDay}日";
HSSFRichTextString richtext = new HSSFRichTextString(strValue);
ApplyFont(workbook, richtext, 11);
var font2 = SetUnderline(workbook, richtext, 11, 0, 4);
int monthEndIndex = 5 + startMonth.Length;
richtext.ApplyFont(5, monthEndIndex, font2);
richtext.ApplyFont(monthEndIndex + 1, monthEndIndex + 1 + startDay.Length, font2);
richtext.ApplyFont(monthEndIndex + 1 + startDay.Length + 2, monthEndIndex + 1 + startDay.Length + 6, font2);
int monthEndIndex2 = monthEndIndex + 1 + startDay.Length + 7;
richtext.ApplyFont(monthEndIndex2, monthEndIndex2 + endMonth.Length, font2);
richtext.ApplyFont(monthEndIndex2 + endMonth.Length + 1, monthEndIndex2 + endMonth.Length + 1 + endDay.Length, font2);
ISheet sheet = workbook.GetSheetAt(0);
sheet.GetRow(row).GetCell(cell).SetCellValue(richtext);
}
public void Test40520_2()
{
short font = 3;
HSSFRichTextString r = new HSSFRichTextString("f0_123456789012345678901234567890123456789012345678901234567890");
r.ApplyFont(0, 2, font);
for (int i = 0; i < 2; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 2; i < r.Length; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
r.ApplyFont(0, 2, font);
for (int i = 0; i < 2; i++)
{
Assert.AreEqual(font, r.GetFontAtIndex(i));
}
for (int i = 2; i < r.Length; i++)
{
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(i));
}
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet1=hssfworkbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = hssfworkbook.CreateFont();
font1.Color = HSSFColor.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = hssfworkbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = hssfworkbook.CreateFont();
font2.Color = HSSFColor.OliveGreen.Index;
font2.IsStrikeout=true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = hssfworkbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = HSSFCellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");
cell1.CellStyle = style1;
ICell cell2 = HSSFCellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好！");
cell2.CellStyle = style2;
//cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
HSSFRichTextString richtext = new HSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = hssfworkbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3=hssfworkbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = HSSFColor.Blue.Index;
font3.FontHeightInPoints=8;
richtext.ApplyFont(16, 18,font3);
cell3.SetCellValue(richtext);
WriteToFile();
}
private static void DrawSheet4(ISheet sheet4, HSSFWorkbook wb)
{
// Create the Drawing patriarch. This is the top level container for
// all shapes. This will clear out any existing shapes for that sheet.
HSSFPatriarch patriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();
// Create a couple of textboxes
HSSFTextbox textbox1 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 0, 0, (short)1, 1, (short)2, 2));
textbox1.String = new HSSFRichTextString("This is a test");
HSSFTextbox textbox2 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 900, 100, (short)3, 3, (short)3, 4));
textbox2.String = new HSSFRichTextString("Woo");
textbox2.SetFillColor(200, 0, 0);
textbox2.LineStyle = LineStyle.DotGel;
// Create third one with some fancy font styling.
HSSFTextbox textbox3 = (HSSFTextbox)patriarch.CreateTextbox(
new HSSFClientAnchor(0, 0, 900, 100, (short)4, 4, (short)5, 4 + 1));
IFont font = wb.CreateFont();
font.IsItalic = true;
font.Underline = (byte)FontUnderlineType.DOUBLE;
HSSFRichTextString str = new HSSFRichTextString("Woo!!!");
str.ApplyFont(2, 5, font);
textbox3.String = str;
textbox3.FillColor = 0x08000030;
textbox3.LineStyle = LineStyle.None; // no line around the textbox.
textbox3.IsNoFill = true; // make it transparent
}
public void SetText(CellPos pos, string text)
{
List <TextPosition> positions = new List <TextPosition>();
while (text.IndexOf("<b>") > -1)
{
int start = text.IndexOf("<b>");
text = text.Substring(0, start) + text.Substring(start + 3);
int end = text.IndexOf("</b>");
text = text.Substring(0, end) + text.Substring(end + 4);
positions.Add(new TextPosition {
start = start, end = end
});
}
HSSFRichTextString richtext = new HSSFRichTextString(text);
IFont font1 = _parent.Internal.CreateFont();
font1.Boldweight = (short)FontBoldWeight.Bold;
foreach (TextPosition position in positions)
{
richtext.ApplyFont(position.start, position.end, font1);
}
ICell cell = GetCell(pos, true);
cell.SetCellValue(richtext);
}
public void TestClearFormatting()
{
HSSFRichTextString r = new HSSFRichTextString("Testing");
Assert.AreEqual(0, r.NumFormattingRuns);
r.ApplyFont(2, 4, new HSSFFont((short)1, null));
Assert.AreEqual(2, r.NumFormattingRuns);
r.ClearFormatting();
Assert.AreEqual(0, r.NumFormattingRuns);
}
public void TestApplyFont()
{
HSSFRichTextString r = new HSSFRichTextString("Testing");
Assert.AreEqual(0, r.NumFormattingRuns);
r.ApplyFont(2, 4, new HSSFFont((short)1, null));
Assert.AreEqual(2, r.NumFormattingRuns);
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(6));
r.ApplyFont(6, 7, new HSSFFont((short)2, null));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
Assert.AreEqual(2, r.GetFontAtIndex(6));
r.ApplyFont(HSSFRichTextString.NO_FONT);
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(0));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(1));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(2));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(3));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(4));
Assert.AreEqual(HSSFRichTextString.NO_FONT, r.GetFontAtIndex(5));
r.ApplyFont(new HSSFFont((short)1, null));
Assert.AreEqual(1, r.GetFontAtIndex(0));
Assert.AreEqual(1, r.GetFontAtIndex(1));
Assert.AreEqual(1, r.GetFontAtIndex(2));
Assert.AreEqual(1, r.GetFontAtIndex(3));
Assert.AreEqual(1, r.GetFontAtIndex(4));
Assert.AreEqual(1, r.GetFontAtIndex(5));
Assert.AreEqual(1, r.GetFontAtIndex(6));
}
public void TestStore()
{
TextObjectRecord record = new TextObjectRecord();
HSSFRichTextString str = new HSSFRichTextString("AB");
str.ApplyFont(0, 2, (short)0x0018);
str.ApplyFont(2, 2, (short)0x0320);
record.HorizontalTextAlignment = (TextObjectRecord.HORIZONTAL_TEXT_ALIGNMENT_CENTERED);
record.VerticalTextAlignment = (TextObjectRecord.VERTICAL_TEXT_ALIGNMENT_JUSTIFY);
record.IsTextLocked = (true);
record.TextOrientation = (TextObjectRecord.TEXT_ORIENTATION_ROT_RIGHT);
record.Str = (str);
byte[] recordBytes = record.Serialize();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
{
Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
}
}
private static void CreateOneRowTwoColumnCellsWithColumn0FontColor(ISheet sheet, ref int rowIndex, string column0_text, string column1_text, IFont font)
{
IRow row = sheet.CreateRow(rowIndex);
ICell cell = row.CreateCell(0);
IRichTextString richString = new HSSFRichTextString(column0_text);
richString.ApplyFont(font);
cell.SetCellValue(richString);
cell = row.CreateCell(1);
cell.SetCellValue(column1_text);
rowIndex++;
}
public void TestStore()
{
TextObjectRecord record = new TextObjectRecord();
HSSFRichTextString str = new HSSFRichTextString("AB");
str.ApplyFont(0, 2, (short)0x0018);
str.ApplyFont(2, 2, (short)0x0320);
record.HorizontalTextAlignment = HorizontalAlignment.Center;
record.VerticalTextAlignment = VerticalAlignment.Justify;
record.IsTextLocked = (true);
record.TextOrientation = TextOrientation.RotRight;
record.Str = (str);
byte[] recordBytes = record.Serialize();
Assert.AreEqual(recordBytes.Length, data.Length);
for (int i = 0; i < data.Length; i++)
{
Assert.AreEqual(data[i], recordBytes[i], "At offset " + i);
}
}
public void setTitle(DateTime date)
{
string title = string.Format(" 南宁 分公司(办事处) {0} 年 {1} 月 ", date.Year, date.Month);
HSSFRichTextString Richtitle = new HSSFRichTextString(title);
IFont font = hssfworkbook.CreateFont();
font.Underline = FontUnderlineType.Single;
font.FontHeightInPoints = 16;
font.FontName = "黑体";
font.IsBold = true;
IFont font1 = hssfworkbook.CreateFont();
font1.FontHeightInPoints = 16;
font1.FontName = "黑体";
font1.IsBold = true;
Richtitle.ApplyFont(0, 6, font);
Richtitle.ApplyFont(6, 15, font1);
Richtitle.ApplyFont(15, 22, font);
Richtitle.ApplyFont(22, 24, font1);
Richtitle.ApplyFont(24, 28, font);
Richtitle.ApplyFont(28, 30, font1);
ICell cell = hssfworkbook.GetSheet("Sheet1").GetRow(1).GetCell(1);
cell.SetCellValue(Richtitle);
}
private void AnalysisCellString(HSSFCell cell)
{
string st3 = string.Empty;
HSSFRichTextString rich = (HSSFRichTextString)cell.RichStringCellValue;
int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;
if (formattingRuns == 0)
{
return;
}
IFont font2 = _workBook.GetFontAt(cell.CellStyle.FontIndex);
string st2 = rich.String.Substring(0, rich.GetIndexOfFormattingRun(0));
if (font2.Color != IndexedColors.White.Index && font2.Color != 0)
{
st3 += st2;
}
for (int i = 0; i < formattingRuns; i++)
{
int startIdx = rich.GetIndexOfFormattingRun(i);
int length;
if (i == formattingRuns - 1)
{
length = rich.Length - startIdx;
}
else
{
length = rich.GetIndexOfFormattingRun(i + 1) - startIdx;
}
string st = rich.String.Substring(startIdx, length);
short fontIndex = rich.GetFontOfFormattingRun(i);
IFont font = _workBook.GetFontAt(fontIndex);
if (font.Color != IndexedColors.White.Index && font.Color != 0)
{
font2 = font;
st3 += st;
}
}
HSSFRichTextString rich2 = new HSSFRichTextString(st3);
rich2.ApplyFont(font2);
cell.SetCellValue(rich2);
}
public void TestOptimiseFonts()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
IFont f3 = wb.CreateFont();
f3.FontHeight = ((short)33);
f3.FontName = ("Unique");
IFont f4 = wb.CreateFont();
f4.FontHeight = ((short)11);
f4.FontName = ("Testing");
IFont f5 = wb.CreateFont();
f5.FontHeight = ((short)22);
f5.FontName = ("Also Testing");
IFont f6 = wb.CreateFont();
f6.FontHeight = ((short)66);
f6.FontName = ("Also Unique");
// Use all three of the four in cell styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Assert.AreEqual(5, cs1.FontIndex);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f4);
Assert.AreEqual(8, cs2.FontIndex);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f5);
Assert.AreEqual(9, cs3.FontIndex);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f6);
Assert.AreEqual(10, cs4.FontIndex);
Assert.AreEqual(25, wb.NumCellStyles);
// And three in rich text
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.ApplyFont(0, 2, f1);
rtr1.ApplyFont(3, 4, f2);
r.CreateCell(0).SetCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.ApplyFont(0, 2, f3);
rtr2.ApplyFont(3, 5, f5);
rtr2.ApplyFont(6, 8, f6);
r.CreateCell(1).SetCellValue(rtr2);
// Check what we have now
Assert.AreEqual(10, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Optimise
HSSFOptimiser.OptimiseFonts(wb);
// Check font count
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Check font use in cell styles
Assert.AreEqual(5, cs1.FontIndex);
Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
Assert.AreEqual(8, cs4.FontIndex); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(6));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(7));
}
/// <summary>
/// 生成Excel
/// </summary>
/// <param name="sheet"></param>
/// <param name="datatable"></param>
/// <param name="num"></param>
private void CreateExcel(ISheet sheet, DataTable datatable, int num, string sheetname)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)sheet.Workbook.CreateCellStyle(); //创建列头单元格实例样式
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
cellStyle.WrapText = true;
IRow firstrow;
if (num == 0)
{
firstrow = sheet.CreateRow(0);
}
else
{
firstrow = sheet.GetRow(0);
}
ICell dFirstCell = firstrow.CreateCell(2);
dFirstCell.SetCellValue("K");
dFirstCell.CellStyle = cellStyle;
ICell firstCell = firstrow.CreateCell(num + 3);
firstCell.SetCellValue(datatable.TableName);
firstCell.CellStyle = cellStyle;
for (int i = 0; i < datatable.Rows.Count; i++)
{
IRow row;
string sampleName = datatable.Rows[i][0].ToString();
string d = datatable.Rows[i][1].ToString();
//要加一行稀释倍数K
if (num == 0)
{
row = sheet.CreateRow(i + 1);
ICell numCell = row.CreateCell(0);
numCell.SetCellValue(i + 1);
ICell nameCell = row.CreateCell(1);
nameCell.SetCellValue(sampleName);
ICell dCell = row.CreateCell(2);
dCell.SetCellValue(d);
numCell.CellStyle = cellStyle;
nameCell.CellStyle = cellStyle;
dCell.CellStyle = cellStyle;
}
else
{
row = sheet.GetRow(i + 1);
}
ICell cell = row.CreateCell(num + 3);
cell.CellStyle = cellStyle;
if (sheetname == datatable.Columns[2].ColumnName)
{
string data = datatable.Rows[i][2].ToString();
if (data != "/")
{
data = CalculateAccuracyCX(data, 3);
}
cell.SetCellValue(data);
}
else
{
string displayResult = string.Empty;
try
{
if (sampleName.Contains("平均"))
{
int rowNum = row.RowNum;
HSSFRow row1 = sheet.GetRow(rowNum - 1) as HSSFRow;
HSSFRow row2 = sheet.GetRow(rowNum - 2) as HSSFRow;
string data1 = row1.GetCell(num + 3).StringCellValue;
string data2 = row2.GetCell(num + 3).StringCellValue;
if (!data1.Contains("/") && !data2.Contains("/") && !data1.Contains("<") && !data2.Contains("<") && !data1.Contains("×") && !data2.Contains("×"))
{
decimal startData = (decimal.Parse(data1) + decimal.Parse(data2)) / 2;
//开始修约计算
displayResult = RoundCalculationResult(datatable.TableName, startData);
datatable.Rows[i][3] = displayResult;
}
else if (data1.Contains("<"))
{
datatable.Rows[i][3] = data1;
displayResult = data1;
}
else if (data2.Contains("<"))
{
datatable.Rows[i][3] = data2;
displayResult = data2;
}
else if (data1.Contains("/") || data2.Contains("/"))
{
datatable.Rows[i][3] = "/";
displayResult = "/";
}
else if (data1.Contains("×") || data2.Contains("×"))
{
decimal CC;
decimal CCC;
if (data1.Contains("×"))
{
string[] str = data1.Split("×");
CC = decimal.Parse(str[0]);
for (int j = 0; j < int.Parse(str[1].Replace("10", "")); j++)
{
CC *= 10;
}
}
else
{
CC = decimal.Parse(data1);
}
if (data2.Contains("×"))
{
string[] str = data2.Split("×");
CCC = decimal.Parse(str[0]);
for (int j = 0; j < int.Parse(str[1].Replace("10", "")); j++)
{
CCC *= 10;
}
}
else
{
CCC = decimal.Parse(data2);
}
//平均要把科学计数法变回正常数
decimal C = (CC + CCC) / 2;
displayResult = RoundCalculationResult(datatable.TableName, C);
datatable.Rows[i][3] = displayResult;
}
else
{
HSSFCellStyle cellRedStyle = CreateRedStyle((HSSFWorkbook)sheet.Workbook);
cell.CellStyle = cellRedStyle;
datatable.Rows[i][3] = "/";
displayResult = "/";
}
}
else if (datatable.Rows[i][2].ToString().Contains("/"))
{
datatable.Rows[i][3] = "/";
displayResult = "/";
}
else
{
decimal startData = decimal.Parse(datatable.Rows[i][2].ToString());
decimal voidData = decimal.Zero;
foreach (KeyValuePair <string, decimal> item in voidElementList)
{
if (item.Key == datatable.TableName)
{
voidData = item.Value;
}
}
decimal dilution = decimal.Parse(datatable.Rows[i][1].ToString());
decimal answer = dilution * (startData - voidData);
//开始修约计算
displayResult = RoundCalculationResult(datatable.TableName, answer);
datatable.Rows[i][3] = displayResult;
}
}
catch
{
string error = "在计算元素[" + datatable.TableName + "]的时候，[" + sampleName + "]的原始数据出现了错误，请联系信息化人员处理。";
MessageBox.Show(error);
}
if (displayResult.Contains("×"))
{
HSSFRichTextString rts = new HSSFRichTextString(displayResult);
var cellStyleFont = (HSSFFont)sheet.Workbook.CreateFont(); //创建字体
cellStyleFont.TypeOffset = FontSuperScript.Super; //字体上标下标
rts.ApplyFont(displayResult.Length - 1, displayResult.Length, cellStyleFont);
cell.SetCellValue(rts);
}
else
{
cell.SetCellValue(displayResult);
}
}
}
//自动调整列距
for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
if (i == 1)
{
sheet.SetColumnWidth(i, 30 * 256);
}
int width = sheet.GetColumnWidth(i);
if (width < 10 * 256)
{
sheet.SetColumnWidth(i, 10 * 256);
}
}
}
public IActionResult DownExcel(string data)
{
List <JObject> dowExcels = JsonConvert.DeserializeObject <List <JObject> >(data);
IWorkbook workbook;
workbook = new HSSFWorkbook();
//赋予表格默认样式，因为样式文件存储只能有4000个
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cellStyle.WrapText = true; //自动换行
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.FillPattern = FillPattern.NoFill; //背景色是必须要这数据
cellStyle.FillForegroundColor = IndexedColors.White.Index; //默认背景色
foreach (var item in dowExcels)
{
var columnlen = item["config"] != null ? item["config"]["columnlen"] : null;
var rowlen = item["config"] != null ? item["config"]["rowlen"] : null;
var borderInfo = item["config"] != null ? item["config"]["borderInfo"] : null;
var merge = item["config"] != null ? item["config"]["merge"] : null;
var Cjarray = item["celldata"];
//读取了模板内所有sheet内容
ISheet sheet = workbook.CreateSheet(item["name"].ToString());
//判断是否有值，并且赋予样式
if (Cjarray != null)
{
for (int i = 0; i < Cjarray.Count(); i++)
{
//判断行，存不存在，不存在创建
IRow row = sheet.GetRow(int.Parse(Cjarray[i]["r"].ToString()));
if (row == null)
{
row = sheet.CreateRow(int.Parse(Cjarray[i]["r"].ToString()));
}
var ct = Cjarray[i]["v"]["ct"];
var cct = Cjarray[i]["v"];
if (ct != null && ct["s"] != null)
{
//合并单元格的走这边
string celldatas = "";
//合并单元格时，会导致文字丢失，提前处理文字信息
for (int j = 0; j < ct["s"].Count(); j++)
{
var stv = ct["s"][j];
celldatas += stv["v"] != null ? stv["v"].ToString() : "";
}
//判断列，不存在创建
ICell Cell = row.GetCell(int.Parse(Cjarray[i]["c"].ToString()));
if (Cell == null)
{
HSSFRichTextString richtext = new HSSFRichTextString(celldatas);
Cell = row.CreateCell(int.Parse(Cjarray[i]["c"].ToString()));
for (int k = 0; k < ct["s"].Count(); k++)
{
IFont font = workbook.CreateFont();
var stv = ct["s"][k];
//文字颜色
if (stv["fc"] != null)
{
var rGB = HTMLColorTranslator.Translate(stv["fc"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
font.Color = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
else
{
font.Color = HSSFColor.Black.Index;
}
//是否加粗
if (stv["bl"] != null)
{
font.IsBold = !string.IsNullOrEmpty(stv["bl"].ToString()) && (stv["bl"].ToString() == "1" ? true : false);
font.Boldweight = stv["bl"].ToString() == "1" ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.None;
}
else
{
font.IsBold = false;
font.Boldweight = (short)FontBoldWeight.None;
}
//是否斜体
if (stv["it"] != null)
{
font.IsItalic = !string.IsNullOrEmpty(stv["it"].ToString()) && (stv["it"].ToString() == "1" ? true : false);
}
else
{
font.IsItalic = false;
}
//下划线
if (stv["un"] != null)
{
font.Underline = stv["un"].ToString() == "1" ? FontUnderlineType.Single : FontUnderlineType.None;
}
else
{
font.Underline = FontUnderlineType.None;
}
//字体
if (stv["ff"] != null)
{
font.FontName = stv["ff"].ToString();
}
//文字大小
if (stv["fs"] != null)
{
font.FontHeightInPoints = double.Parse(stv["fs"].ToString());
}
Cell.CellStyle.SetFont(font);
richtext.ApplyFont(celldatas.IndexOf(stv["v"].ToString()), celldatas.IndexOf(stv["v"].ToString()) + stv["v"].ToString().Length, font);
Cell.SetCellValue(richtext);
}
//背景颜色
if (cct["bg"] != null)
{
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.CloneStyleFrom(cellStyle);
if (cct["bg"] != null)
{
var rGB = HTMLColorTranslator.Translate(cct["bg"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
Cell.CellStyle = cellStyle1;
}
else
{
Cell.CellStyle = cellStyle;
}
}
}
else
{
//没有合并单元格的走这边
//判断列，不存在创建
ICell Cell = row.GetCell(int.Parse(Cjarray[i]["c"].ToString()));
if (Cell == null)
{
Cell = row.CreateCell(int.Parse(Cjarray[i]["c"].ToString()));
IFont font = workbook.CreateFont();
ct = Cjarray[i]["v"];
//字体颜色
if (ct["fc"] != null)
{
var rGB = HTMLColorTranslator.Translate(ct["fc"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
font.Color = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
else
{
font.Color = HSSFColor.Black.Index;
}
//是否加粗
if (ct["bl"] != null)
{
font.IsBold = !string.IsNullOrEmpty(ct["bl"].ToString()) && (ct["bl"].ToString() == "1" ? true : false);
font.Boldweight = ct["bl"].ToString() == "1" ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.None;
}
else
{
font.IsBold = false;
font.Boldweight = (short)FontBoldWeight.None;
}
//斜体
if (ct["it"] != null)
{
font.IsItalic = !string.IsNullOrEmpty(ct["it"].ToString()) && (ct["it"].ToString() == "1" ? true : false);
}
else
{
font.IsItalic = false;
}
//下划线
if (ct["un"] != null)
{
font.Underline = ct["un"].ToString() == "1" ? FontUnderlineType.Single : FontUnderlineType.None;
}
else
{
font.Underline = FontUnderlineType.None;
}
//字体
if (ct["ff"] != null)
{
font.FontName = ct["ff"].ToString();
}
//文字大小
if (ct["fs"] != null)
{
font.FontHeightInPoints = double.Parse(ct["fs"].ToString());
}
Cell.CellStyle.SetFont(font);
//判断背景色
if (ct["bg"] != null)
{
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.CloneStyleFrom(cellStyle);
if (ct["bg"] != null)
{
var rGB = HTMLColorTranslator.Translate(ct["bg"].ToString());
var color = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).Indexed;
}
Cell.CellStyle = cellStyle1;
}
else
{
Cell.CellStyle = cellStyle;
}
Cell.SetCellValue(ct["v"] != null ? ct["v"].ToString() : "");
}
}
}
sheet.ForceFormulaRecalculation = true;
}
//判断是否要设置列宽度
if (columnlen != null)
{
foreach (var cols in columnlen)
{
var p = cols as JProperty;
sheet.SetColumnWidth(int.Parse(p.Name), int.Parse(p.Value.ToString()) * 38);
}
}
//判断是否要设置行高度
if (rowlen != null)
{
foreach (var rows in rowlen)
{
var p = rows as JProperty;
sheet.GetRow(int.Parse(p.Name)).HeightInPoints = float.Parse(p.Value.ToString());
}
}
//判断是否要加边框
if (borderInfo != null)
{
for (int i = 0; i < borderInfo.Count(); i++)
{
var bordervalue = borderInfo[i]["value"];
if (bordervalue != null)
{
var rowindex = bordervalue["row_index"];
var colindex = bordervalue["col_index"];
var l = bordervalue["l"];
var r = bordervalue["r"];
var t = bordervalue["t"];
var b = bordervalue["b"];
if (rowindex != null)
{
IRow rows = sheet.GetRow(int.Parse(bordervalue["row_index"].ToString()));
if (colindex != null)
{
ICell cell = rows.GetCell(int.Parse(bordervalue["col_index"].ToString()));
if (b != null)
{
cell.CellStyle.BorderBottom = ExcelHepler.GetBorderStyle(int.Parse(b["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(b["color"].ToString());
var bcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.BottomBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(bcolor.R, bcolor.G, bcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
if (t != null)
{
cell.CellStyle.BorderTop = ExcelHepler.GetBorderStyle(int.Parse(t["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(t["color"].ToString());
var tcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.TopBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(tcolor.R, tcolor.G, tcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
if (l != null)
{
cell.CellStyle.BorderLeft = ExcelHepler.GetBorderStyle(int.Parse(l["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(l["color"].ToString());
var lcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.LeftBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(lcolor.R, lcolor.G, lcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
if (r != null)
{
cell.CellStyle.BorderRight = ExcelHepler.GetBorderStyle(int.Parse(r["style"].ToString()));
var rGB = HTMLColorTranslator.Translate(r["color"].ToString());
var rcolor = Color.FromArgb(rGB.R, rGB.G, rGB.B);
cell.CellStyle.RightBorderColor = ((HSSFWorkbook)workbook).GetCustomPalette().FindSimilarColor(rcolor.R, rcolor.G, rcolor.B).Indexed;
}
else
{
cell.CellStyle.BorderBottom = BorderStyle.None;
cell.CellStyle.BottomBorderColor = HSSFColor.COLOR_NORMAL;
}
}
}
}
}
}
//判断是否要合并单元格
if (merge != null)
{
foreach (var imerge in merge)
{
var firstmer = imerge.First();
int r = int.Parse(firstmer["r"].ToString()); //主单元格的行号,开始行号
int rs = int.Parse(firstmer["rs"].ToString()); //合并单元格占的行数,合并多少行
int c = int.Parse(firstmer["c"].ToString()); //主单元格的列号,开始列号
int cs = int.Parse(firstmer["cs"].ToString()); //合并单元格占的列数,合并多少列
CellRangeAddress region = new CellRangeAddress(r, r + rs - 1, c, c + cs - 1);
sheet.AddMergedRegion(region);
}
}
}
var dir = Path.Combine(Directory.GetCurrentDirectory(), @"wwwroot\Files");
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
var fileId = Guid.NewGuid();
var fileName = $"{fileId}.xls";
FileStream stream = new FileStream(Path.Combine(dir, fileName), FileMode.OpenOrCreate);
workbook.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
workbook.Close();
stream.Close();
return(Ok(fileId));
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("ICell comments in POI HSSF");
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
IDrawing patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
//Create a cell in row 3
ICell cell1 = sheet.CreateRow(3).CreateCell(1);
cell1.SetCellValue(new HSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
IComment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));
// set text in the comment
comment1.String = (new HSSFRichTextString("We can set comments in POI"));
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.Author = ("Apache Software Foundation");
// The first way to assign comment to a cell is via HSSFCell.SetCellComment method
cell1.CellComment = (comment1);
//Create another cell in row 6
ICell cell2 = sheet.CreateRow(6).CreateCell(1);
cell2.SetCellValue(36.6);
HSSFComment comment2 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));
//modify background color of the comment
comment2.SetFillColor(204, 236, 255);
HSSFRichTextString str = new HSSFRichTextString("Normal body temperature");
//apply custom font to the text in the comment
IFont font = hssfworkbook.CreateFont();
font.FontName = ("Arial");
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = HSSFColor.Red.Index;
str.ApplyFont(font);
comment2.String = str;
comment2.Visible = true; //by default comments are hidden. This one is always visible.
comment2.Author = "Bill Gates";
/**
* The second way to assign comment to a cell is to implicitly specify its row and column.
* Note, it is possible to set row and column of a non-existing cell.
* It works, the commnet is visible.
*/
comment2.Row = 6;
comment2.Column = 1;
WriteToFile();
}
/// <summary>
/// 构建一个Sheet
/// </summary>
/// <param name="sheet"></param>
public void CreteSheet(NPOIExcelSheet sheet)
{
ISheet curSheet = _book.CreateSheet(sheet.SheetName);
//表格
int rowIndex = 0;
if (sheet.Rows != null && sheet.Rows.Count > 0)
{
foreach (var row in sheet.Rows)
{
IRow curRow = curSheet.CreateRow(rowIndex);
int cellIndex = 0;
foreach (var cell in row.Cells)
{
ICell curCell = curRow.CreateCell(cellIndex, cell.Type);
cell.Value = cell.Value ?? "";
if (cell.Style != null)
{
curCell.CellStyle = cell.Style;
}
//富文本设置
if (cell.RichTextSettings != null && cell.RichTextSettings.Any())
{
if (_excelType == NPOIExcelType.XLSX)
{
//SXSSFWorkbook
XSSFRichTextString xSSF = new XSSFRichTextString(cell.Value);
cell.RichTextSettings.ForEach(setting => { xSSF.ApplyFont(setting.Start, setting.End, setting.Font); });
curCell.SetCellValue(xSSF);
}
else
{
HSSFRichTextString hSSF = new HSSFRichTextString(cell.Value);
cell.RichTextSettings.ForEach(setting => { hSSF.ApplyFont(setting.Start, setting.End, setting.Font); });
curCell.SetCellValue(hSSF);
}
}
else
{
curCell.SetCellValue(cell.Value);
}
//自动宽度
if (cell.Width >= 0)
{
if (cell.Width == 0)
{
AutoWidth(curSheet, cellIndex);
}
else
{
curSheet.SetColumnWidth(cellIndex, (int)cell.Width * 256);
}
}
cellIndex++;
}
curRow.Height = (short)Convert.ToInt32(row.Height * 20);
//合并单元格策略
if (row.Regions.Any())
{
int maxRow = 0;
foreach (var region in row.Regions)
{
//if (maxRow < rowIndex + region.RowCount - 1)
//{
// maxRow = rowIndex + region.RowCount - 1;
//}
curSheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + region.RowCount - 1, region.StartCol, region.EndCol));
}
// rowIndex += maxRow > 0 ? maxRow : 1;
}
rowIndex++;
rowIndex += row.MaginButton;
}
}
//图表
if (sheet.Chart != null && sheet.Chart.Count > 0)
{
int startRow = rowIndex + 1;
foreach (var curChart in sheet.Chart)
{
int endRow = startRow + curChart.MarginBottom;
CreateChart(curChart, curSheet, startRow, endRow);
startRow = endRow + 1;
}
}
}
public void DrawString(String str, int x, int y)
{
if (string.IsNullOrEmpty(str))
return;
using (Font excelFont = new Font(font.Name.Equals("SansSerif") ? "Arial" : font.Name, (int)(font.Size / verticalPixelsPerPoint), font.Style))
{
FontDetails d = StaticFontMetrics.GetFontDetails(excelFont);
int width = (int)((d.GetStringWidth(str) * 8) + 12);
int height = (int)((font.Size / verticalPixelsPerPoint) + 6) * 2;
y -= Convert.ToInt32((font.Size / verticalPixelsPerPoint) + 2 * verticalPixelsPerPoint); // we want to Draw the shape from the top-left
HSSFTextbox textbox = escherGroup.CreateTextbox(new HSSFChildAnchor(x, y, x + width, y + height));
textbox.IsNoFill = (true);
textbox.LineStyle = LineStyle.None;
HSSFRichTextString s = new HSSFRichTextString(str);
HSSFFont hssfFont = MatchFont(excelFont);
s.ApplyFont(hssfFont);
textbox.String = (s);
}
}