public void TestURLsWithHashMark()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("59775.xlsx");
XSSFSheet sh = wb.GetSheetAt(0) as XSSFSheet;
CellAddress A2 = new CellAddress("A2");
CellAddress A3 = new CellAddress("A3");
CellAddress A4 = new CellAddress("A4");
CellAddress A7 = new CellAddress("A7");
XSSFHyperlink link = sh.GetHyperlink(A2) as XSSFHyperlink;
Assert.AreEqual("A2", link.CellRef, "address");
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("http://twitter.com/#!/apacheorg", link.Address, "link target");
link = sh.GetHyperlink(A3) as XSSFHyperlink;
Assert.AreEqual("A3", link.CellRef, "address");
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("http://www.bailii.org/databases.html#ie", link.Address, "link target");
link = sh.GetHyperlink(A4) as XSSFHyperlink;
Assert.AreEqual("A4", link.CellRef, "address");
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("https://en.wikipedia.org/wiki/Apache_POI#See_also", link.Address, "link target");
link = sh.GetHyperlink(A7) as XSSFHyperlink;
Assert.AreEqual("A7", link.CellRef, "address");
Assert.AreEqual(HyperlinkType.Document, link.Type, "link type");
Assert.AreEqual("Sheet1", link.Address, "link target");
wb.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// Sets the cell hyperlink.
/// </summary>
/// <param name="sheet">The sheet.</param>
/// <param name="location">The location.</param>
/// <param name="cellContent">Content of the cell.</param>
/// <param name="sheetName">Name of the sheet.</param>
public static void SetCellHyperlink(this ISheet sheet, Point location, string cellContent, string sheetName)
{
var link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = ("'" + sheetName + "'!A1");
sheet.GetRow(location.Y).GetCell(location.X).Hyperlink = link;
sheet.GetRow(location.Y).GetCell(location.X).SetCellValue(cellContent);
}
public static void ExportDT(DataTable dt)
{
XSSFWorkbook workbook;
using (FileStream file = new FileStream("Ш-01.07.03.03-38.xlsx", FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);
}
ISheet worksheet = workbook.GetSheet("Лист1");
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.WrapText = true;
hlink_style.SetFont(hlink_font);
ICellStyle easy_style = workbook.CreateCellStyle();
easy_style.WrapText = true;
IFont easy_font = workbook.CreateFont();
easy_font.FontHeightInPoints = 9;
easy_font.FontName = "Arial Cyr";
easy_style.SetFont(easy_font);
easy_style.Alignment = HorizontalAlignment.Center;
easy_style.VerticalAlignment = VerticalAlignment.Center;
for (int rownum = 2; rownum < dt.Rows.Count + 2; rownum++)
{
IRow row = worksheet.CreateRow(rownum);
ICell Cell_5 = row.CreateCell(4); Cell_5.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["SHFR"])); Cell_5.CellStyle = easy_style;
ICell Cell_19 = row.CreateCell(18); Cell_19.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["STAGE"])); Cell_19.CellStyle = easy_style;
ICell Cell_7 = row.CreateCell(6); Cell_7.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["OBOSDOC"])); Cell_7.CellStyle = easy_style;
ICell Cell_6 = row.CreateCell(5); Cell_6.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["NAIMPROJE"])); Cell_6.CellStyle = easy_style;
ICell Cell_8 = row.CreateCell(7); Cell_8.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["NAIMOBJ"])); Cell_8.CellStyle = easy_style;
ICell Cell_9 = row.CreateCell(8); Cell_9.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["NAIMIZOBR"])); Cell_9.CellStyle = easy_style;
ICell Cell_3 = row.CreateCell(2); Cell_3.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["DATEOFLASTWRITE"])); Cell_3.CellStyle = easy_style;
ICell Cell_20 = row.CreateCell(19); Cell_20.SetCellValue(Convert.ToString(dt.Rows[rownum - 2]["Directory"])); Cell_20.CellStyle = easy_style;
string link = Convert.ToString(dt.Rows[rownum - 2]["Directory"]);
var url = new Uri(link);
XSSFHyperlink FileLink = new XSSFHyperlink(HyperlinkType.File);
FileLink.Address = Convert.ToString(url);
Cell_20.Hyperlink = (FileLink);
Cell_20.CellStyle = (hlink_style);
}
if (!File.Exists("test_output.xlsx"))
{
File.Delete("test_output.xlsx");
}
using (FileStream file = new FileStream("test_output.xlsx", FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
workbook.Write(file);
}
}
public void Test53734()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53734.xlsx");
XSSFHyperlink link = wb.GetSheetAt(0).GetRow(0).GetCell(0).Hyperlink as XSSFHyperlink;
Assert.AreEqual("javascript:///", link.Address);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
link = wb.GetSheetAt(0).GetRow(0).GetCell(0).Hyperlink as XSSFHyperlink;
Assert.AreEqual("javascript:///", link.Address);
}
public void Test53282()
{
//since limitation in .NET Uri class, it's impossible to accept uri like mailto:[email protected]%C2%A0
//%C2%A0 is called non-breaking space, see https://en.wikipedia.org/wiki/Non-breaking_space
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53282.xlsx");
XSSFHyperlink link = wb.GetSheetAt(0).GetRow(0).GetCell(14).Hyperlink as XSSFHyperlink;
Assert.AreEqual("mailto:[email protected]%C2%A0", link.Address);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
link = wb.GetSheetAt(0).GetRow(0).GetCell(14).Hyperlink as XSSFHyperlink;
Assert.AreEqual("mailto:[email protected]%C2%A0", link.Address);
}
public void TestCopyHSSFHyperlink()
{
HSSFHyperlink hlink = new HSSFHyperlink(HyperlinkType.Url);
hlink.Address = ("http://poi.apache.org/");
hlink.FirstColumn = (3);
hlink.FirstRow = (2);
hlink.LastColumn = (5);
hlink.LastRow = (6);
hlink.Label = ("label");
XSSFHyperlink xlink = new XSSFHyperlink(hlink);
Assert.AreEqual("http://poi.apache.org/", xlink.Address);
Assert.AreEqual(new CellReference(2, 3), new CellReference(xlink.CellRef));
// Are HSSFHyperlink.label and XSSFHyperlink.tooltip the same? If so, perhaps one of these needs renamed for a consistent Hyperlink interface
// Assert.AreEqual("label", xlink.Tooltip);
}
public static IHyperlink CreateHyperlink(this ISheet sheet, Int32 x, Int32 y)
{
IHyperlink link = null;
if (sheet is XSSFSheet)
{
link = new XSSFHyperlink(HyperlinkType.Document);
}
else
{
link = new HSSFHyperlink(HyperlinkType.Document);
}
String address = $"!R{x.ToString()}C{y.ToString()}";
link.Address = $"'{ sheet.SheetName}'" + address;
return(link);
}
/// <summary>
/// 创建与指定 工作表关联的超链接
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="address">链接地址</param>
/// <returns></returns>
public static IHyperlink CreateHyperlink(this ISheet sheet, String address = null)
{
IHyperlink link = null;
if (sheet is XSSFSheet)
{
link = new XSSFHyperlink(HyperlinkType.Document);
}
else
{
link = new HSSFHyperlink(HyperlinkType.Document);
}
//'Murray工作表_2'!A1
if (address == null)
{
address = "!A1";
}
link.Address = $"'{ sheet.SheetName}'" + address;
return(link);
}
/**
* Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
* is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
* do not track the content they point to.
*
* @param shifter
*/
public override void UpdateHyperlinks(FormulaShifter shifter)
{
XSSFSheet xsheet = (XSSFSheet)sheet;
int sheetIndex = xsheet.GetWorkbook().GetSheetIndex(sheet);
List <IHyperlink> hyperlinkList = sheet.GetHyperlinkList();
foreach (IHyperlink hyperlink1 in hyperlinkList)
{
XSSFHyperlink hyperlink = hyperlink1 as XSSFHyperlink;
String cellRef = hyperlink.CellRef;
CellRangeAddress cra = CellRangeAddress.ValueOf(cellRef);
CellRangeAddress shiftedRange = ShiftRange(shifter, cra, sheetIndex);
if (shiftedRange != null && shiftedRange != cra)
{
// shiftedRange should not be null. If shiftedRange is null, that means
// that a hyperlink wasn't deleted at the beginning of shiftRows when
// identifying rows that should be removed because they will be overwritten
hyperlink.SetCellReference(shiftedRange.FormatAsString());
}
}
}
public void Test52716()
{
XSSFWorkbook wb1 = XSSFTestDataSamples.OpenSampleWorkbook("52716.xlsx");
XSSFSheet sh1 = wb1.GetSheetAt(0) as XSSFSheet;
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
XSSFSheet sh2 = wb2.GetSheetAt(0) as XSSFSheet;
Assert.AreEqual(sh1.NumberOfComments, sh2.NumberOfComments);
XSSFHyperlink l1 = sh1.GetHyperlink(0, 1) as XSSFHyperlink;
Assert.AreEqual(HyperlinkType.Document, l1.Type);
Assert.AreEqual("B1", l1.CellRef);
Assert.AreEqual("Sort on Titel", l1.Tooltip);
XSSFHyperlink l2 = sh2.GetHyperlink(0, 1) as XSSFHyperlink;
Assert.AreEqual(l1.Tooltip, l2.Tooltip);
Assert.AreEqual(HyperlinkType.Document, l2.Type);
Assert.AreEqual("B1", l2.CellRef);
}
/// <summary>
/// Book内のIndexシートの飾り付けるメソッド
/// </summary>
/// <param name="book">ExcelBookのIクラス</param>
/// <param name="styles">使用するスタイルのDictionary</param>
/// <param name="sheet">ExcelSheetのIクラス</param>
/// <returns>Bool True or False</returns>
public bool FormatSheetIdx(Dictionary <String, ICellStyle> styles, ISheet sheet)
{
//メモリ線を非表示
sheet.DisplayGridlines = false;
//TopLeft シートタイトル埋め込み
WriteCell(sheet, styles["topleft"], (0, 0), ConstHulft.SHEETNAME_IDX);
//Sheetのインデックス埋め込み
var titles = new List <(int no, string name)> {
(1, ConstHulft.SHEETNAME_SND)
, (2, ConstHulft.SHEETNAME_RCV)
, (3, ConstHulft.SHEETNAME_GRP)
, (4, ConstHulft.SHEETNAME_HST)
, (5, ConstHulft.SHEETNAME_JOB)
};
//Sheetの中身を整える
(int y, int x) = (1, 1);
foreach (var(no, name) in titles)
{
//番号埋め
WriteCell(sheet, styles["indexBox"], (y + no, x), no);
//シート名を埋めてリンクを付ける
var link = new XSSFHyperlink(HyperlinkType.Document)
{
Address = name + "!A1"
};
WriteCell(sheet, styles["indexLabel"], link, (y + no, x + 1), name);
}
//カラムの横幅Autoが好き
sheet.AutoSizeColumn(0, true);
return(true);
}
public void UpdateWriteExcelFileGP(string sheetname, ref List <int[]> redError, ref List <int[]> yellowError, ref List <int[]> blueError)
{
int red_total = redError.Count;
int yellow_total = yellowError.Count;
using (FileStream fs = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
try
{
var ext = Path.GetExtension(fileName).ToLower();
IWorkbook wk;
if (ext.Contains(".xlsx"))
{
wk = new XSSFWorkbook(fs);
}
else
{
wk = new HSSFWorkbook(fs);
}
fs.Close();
ISheet sheet = wk.GetSheet(sheetname);
IDrawing patr = sheet.CreateDrawingPatriarch();
NPOI.SS.UserModel.ICreationHelper facktory = wk.GetCreationHelper();
// 错误类型-批注
Dictionary <int, string> Dic_Comment = new Dictionary <int, string>();
Dic_Comment.Add(1, "未匹配上的省市名");
Dic_Comment.Add(2, "不支持的日期格式");
Dic_Comment.Add(3, "上报时间不能晚于当前时间");
Dic_Comment.Add(4, "当期时间与上次上报的时间格式不相同");
Dic_Comment.Add(5, "指标值包含中文");
Dic_Comment.Add(6, "指标值不是数值");
Dic_Comment.Add(7, "指标值越界");
Dic_Comment.Add(8, "带%的指标小于1");
Dic_Comment.Add(9, "分子÷分母不等于指标值");
Dic_Comment.Add(10, "无法填报");
Dictionary <int, int[]> Dic_Count = new Dictionary <int, int[]>();
Dic_Count.Add(1, new int[2] {
0, 1
});
Dic_Count.Add(2, new int[2] {
0, 1
});
Dic_Count.Add(3, new int[2] {
0, 1
});
Dic_Count.Add(4, new int[2] {
0, 1
});
Dic_Count.Add(5, new int[2] {
0, 0
});
Dic_Count.Add(6, new int[2] {
0, 1
});
Dic_Count.Add(7, new int[2] {
0, 1
});
Dic_Count.Add(8, new int[2] {
0, 0
});
Dic_Count.Add(9, new int[2] {
0, 0
});
Dic_Count.Add(10, new int[2] {
0, 2
});
// 背景色
ICellStyle yellowStyle = wk.CreateCellStyle();
yellowStyle.FillForegroundColor = HSSFColor.LightYellow.Index;
yellowStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle redStyle = wk.CreateCellStyle();
redStyle.FillForegroundColor = HSSFColor.Red.Index;
redStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle blueStyle = wk.CreateCellStyle();
blueStyle.FillForegroundColor = HSSFColor.SkyBlue.Index;
blueStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle yellowErrorStyle = null, redErrorStyle = null, blueErrorStyle = null;
/**************************填写模板中设置背景色****************************/
foreach (int[] yellow in yellowError)
{
if (sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle.GetDataFormatString().Contains("%"))
{
if (yellowErrorStyle == null)
{
yellowErrorStyle = wk.CreateCellStyle();
copyCellStyle(sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle, ref yellowErrorStyle);
yellowErrorStyle.FillPattern = FillPattern.SolidForeground;
yellowErrorStyle.FillForegroundColor = HSSFColor.LightYellow.Index;
}
sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowErrorStyle;
}
else
{
sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowStyle;
}
Dic_Count[yellow[2]][0]++;
}
foreach (int[] red in redError)
{
if (sheet.GetRow(red[0]).GetCell(red[1]).CellStyle.GetDataFormatString().Contains("%"))
{
if (redErrorStyle == null)
{
redErrorStyle = wk.CreateCellStyle();
copyCellStyle(sheet.GetRow(red[0]).GetCell(red[1]).CellStyle, ref redErrorStyle);
redErrorStyle.FillPattern = FillPattern.SolidForeground;
redErrorStyle.FillForegroundColor = HSSFColor.Red.Index;
}
sheet.GetRow(red[0]).GetCell(red[1]).CellStyle = redErrorStyle;
}
else
{
sheet.GetRow(red[0]).GetCell(red[1]).CellStyle = redStyle;
}
Dic_Count[red[2]][0]++;
}
foreach (int[] blue in blueError)
{
if (sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle.GetDataFormatString().Contains("%"))
{
if (blueErrorStyle == null)
{
blueErrorStyle = wk.CreateCellStyle();
copyCellStyle(sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle, ref blueErrorStyle);
blueErrorStyle.FillPattern = FillPattern.SolidForeground;
blueErrorStyle.FillForegroundColor = HSSFColor.SkyBlue.Index;
}
sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle = blueErrorStyle;
}
else
{
sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle = blueStyle;
}
Dic_Count[blue[2]][0]++;
}
/**************************问题数据明细****************************/
ISheet sheetLogInfo = null;
int index = wk.GetSheetIndex("问题数据明细");
if (index > -1)
{
wk.RemoveSheetAt(index);
}
sheetLogInfo = wk.CreateSheet("问题数据明细");
IRow headerRow = sheetLogInfo.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("位置");
headerRow.CreateCell(1).SetCellValue("描述");
headerRow.CreateCell(2).SetCellValue("备注");
int rowIndex = 1;
string address = null, value1 = "必改", value2 = "自查", value3 = "无法填报", sheetname2 = "#填写模板!";
foreach (int[] red in redError)
{
IRow dataRow = sheetLogInfo.CreateRow(rowIndex);
address = ExcelUtil.getExcelColumnLabel(red[1]) + "" + (red[0] + 1);
dataRow.CreateCell(0).SetCellValue(address);
dataRow.CreateCell(1).SetCellValue(Dic_Comment[red[2]]);
dataRow.CreateCell(2).SetCellValue(value1);
sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = redStyle;
IHyperlink link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = sheetname2 + address;
sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link;
rowIndex++;
}
foreach (int[] yellow in yellowError)
{
IRow dataRow = sheetLogInfo.CreateRow(rowIndex);
address = ExcelUtil.getExcelColumnLabel(yellow[1]) + "" + (yellow[0] + 1);
dataRow.CreateCell(0).SetCellValue(address);
dataRow.CreateCell(1).SetCellValue(Dic_Comment[yellow[2]]);
dataRow.CreateCell(2).SetCellValue(value2);
sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = yellowStyle;
IHyperlink link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = sheetname2 + address;
sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link;
rowIndex++;
}
foreach (int[] blue in blueError)
{
IRow dataRow = sheetLogInfo.CreateRow(rowIndex);
address = ExcelUtil.getExcelColumnLabel(blue[1]) + "" + (blue[0] + 1);
dataRow.CreateCell(0).SetCellValue(address);
dataRow.CreateCell(1).SetCellValue(Dic_Comment[blue[2]]);
dataRow.CreateCell(2).SetCellValue(value3);
sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = blueStyle;
IHyperlink link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = sheetname2 + address;
sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link;
rowIndex++;
}
/**************************问题数据统计****************************/
ISheet sheetLog = null;
index = wk.GetSheetIndex("问题数据统计");
if (index > -1)
{
wk.RemoveSheetAt(index);
}
sheetLog = wk.CreateSheet("问题数据统计");
headerRow = sheetLog.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("描述");
headerRow.CreateCell(1).SetCellValue("次数");
headerRow.CreateCell(2).SetCellValue("备注");
// handling value.
rowIndex = 1;
foreach (KeyValuePair <int, int[]> kv in Dic_Count)
{
IRow dataRow = sheetLog.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(Dic_Comment[kv.Key]);
dataRow.CreateCell(1).SetCellValue(kv.Value[0]);
dataRow.CreateCell(2).SetCellValue(kv.Value[1] == 1 ? value1 : (kv.Value[1] == 0 ? value2 : value3));
sheetLog.GetRow(rowIndex).GetCell(2).CellStyle = kv.Value[1] == 1 ? redStyle : (kv.Value[1] == 0 ? yellowStyle : blueStyle);
rowIndex++;
}
IRow totalRow = sheetLog.CreateRow(rowIndex);
totalRow.CreateCell(0).SetCellValue("必改问题合计：" + red_total);
totalRow.CreateCell(1).SetCellValue("自查问题合计：" + yellow_total);
totalRow.CreateCell(2).SetCellValue("无法填报合计：" + blueError.Count);
sheetLog.GetRow(rowIndex).GetCell(0).CellStyle = redStyle;
sheetLog.GetRow(rowIndex).GetCell(1).CellStyle = yellowStyle;
sheetLog.GetRow(rowIndex).GetCell(2).CellStyle = blueStyle;
IRow remarkRow1 = sheetLog.CreateRow(++rowIndex);
IRow remarkRow2 = sheetLog.CreateRow(++rowIndex);
IRow remarkRow3 = sheetLog.CreateRow(++rowIndex);
IRow remarkRow4 = sheetLog.CreateRow(++rowIndex);
remarkRow1.CreateCell(0).SetCellValue("注：");
remarkRow2.CreateCell(0).SetCellValue("【必改】问题数据必须修改后才能入库");
remarkRow3.CreateCell(0).SetCellValue("【自查】问题数据请自行核查数据，填报人员对数据准确性负责");
remarkRow4.CreateCell(0).SetCellValue("【无法填报】表示根据数据填报核减模版，该单元格指标为无法填报，该指标数据不入库");
//FileStream fileStream = File.OpenWrite(fileName);
FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
//FileStream fileStream = new FileStream(@"D:\\新建文件夹\\test.xlsx",FileMode.Create, FileAccess.Write);
wk.Write(fileStream);
fileStream.Close();
}
catch (Exception e)
{
FileLog.WriteLog("" + e);
}
}
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls =
{
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address = (s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink = (link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
}
private bool GenerateNewPage(IListSheet listSheet)
{
bool succeed = true;
string exportDir = this.RunPage.GetExportDir();
string pageSourceDir = this.RunPage.GetDetailSourceFileDir();
string outputTitleTextDir = Path.Combine(exportDir, "titleText");
Dictionary <string, int> subjectColumnDic = CommonUtil.InitStringIndexDic(new string[] {
"index",
"id",
"title",
"url",
"googleUrl",
"creator",
"createDate",
"messageCount",
"authorCount",
"commentPublisher",
"commentPublished"
});
string subjectFileExcelPath = Path.Combine(exportDir, this.RunPage.Project.Name + "_List.xlsx");
ExcelWriter subjectEW = new ExcelWriter(subjectFileExcelPath, "List", subjectColumnDic);
string subjectFileXmlPath = Path.Combine(exportDir, this.RunPage.Project.Name + "_List.xml");
XmlWriter subjectXW = new XmlWriter(subjectFileXmlPath, subjectColumnDic);
Dictionary <string, int> allCommentsColumnDic = new Dictionary <string, int>();
allCommentsColumnDic.Add("subjectIndex", 0);
allCommentsColumnDic.Add("googleUrl", 1);
allCommentsColumnDic.Add("creator", 2);
allCommentsColumnDic.Add("author", 3);
allCommentsColumnDic.Add("lastPostDate", 4);
string allCommentsFilePath = Path.Combine(exportDir, this.RunPage.Project.Name + "_AllComments.xlsx");
ExcelWriter allCommentsListEW = new ExcelWriter(allCommentsFilePath, "List", allCommentsColumnDic);
for (int i = 0; i < listSheet.RowCount; i++)
{
Dictionary <string, string> row = listSheet.GetRow(i);
bool giveUp = "Y".Equals(row[SysConfig.GiveUpGrabFieldName]);
if (!giveUp)
{
string index = row[SysConfig.ListPageIndexFieldName].PadLeft(4, '0');
string googleUrl = row[SysConfig.DetailPageUrlFieldName];
string title = row["title"];
string creator = row["creator"];
string createDate = row["createDate"];
string localFilePath = this.RunPage.GetFilePath(googleUrl, pageSourceDir);
TextReader tr = null;
try
{
tr = new StreamReader(localFilePath);
string webPageHtml = tr.ReadToEnd();
HtmlAgilityPack.HtmlDocument htmlDoc = new HtmlAgilityPack.HtmlDocument();
htmlDoc.LoadHtml(webPageHtml);
string messagesNumStr = htmlDoc.DocumentNode.SelectSingleNode("//body/i").InnerText;
int ofIndex = messagesNumStr.IndexOf("of");
int messagesIndex = messagesNumStr.IndexOf("messages");
string messageCount = messagesNumStr.Substring(ofIndex + 2, messagesIndex - ofIndex - 2).Trim();
List <string> authorNames = new List <string>();
HtmlNodeCollection messageNodes = htmlDoc.DocumentNode.SelectNodes("//body/table/tr");
if (messageNodes != null)
{
for (int j = 0; j < messageNodes.Count; j++)
{
HtmlNode messageNode = messageNodes[j];
HtmlNode authorNode = messageNode.SelectSingleNode("./td[2]");
HtmlNode lastPostDateNode = messageNode.SelectSingleNode("./td[3]");
if (authorNode != null)
{
string author = HttpUtility.HtmlDecode(authorNode.InnerText).Trim();
if (j == 0)
{
creator = author;
}
if (!authorNames.Contains(author))
{
authorNames.Add(author);
}
string lastPostDate = lastPostDateNode == null ? "" : lastPostDateNode.InnerText;
Dictionary <string, string> commentF2vs = new Dictionary <string, string>();
commentF2vs.Add("subjectIndex", index);
commentF2vs.Add("googleUrl", googleUrl);
commentF2vs.Add("creator", creator);
commentF2vs.Add("author", author);
commentF2vs.Add("lastPostDate", lastPostDate);
allCommentsListEW.AddRow(commentF2vs);
}
}
}
//修改html内容，增加线框
HtmlNode tableNode = htmlDoc.DocumentNode.SelectSingleNode("//body/table");
tableNode.Attributes["border"].Value = "1";
int localHtmlUrlStartIndex = googleUrl.IndexOf("/idempiere/") + "/idempiere/".Length;
string htmlLocalName = CommonUtil.ProcessFileName(googleUrl.Substring(localHtmlUrlStartIndex), "_") + ".html";
string htmlLocalUrl = Path.Combine(Path.GetDirectoryName(pageSourceDir), "export\\html\\" + htmlLocalName);
CommonUtil.CreateFileDirectory(htmlLocalUrl);
htmlDoc.Save(htmlLocalUrl);
Dictionary <string, string> f2vs = new Dictionary <string, string>();
f2vs.Add("index", index);
f2vs.Add("title", title);
f2vs.Add("googleUrl", googleUrl);
f2vs.Add("url", htmlLocalName);
f2vs.Add("creator", creator);
f2vs.Add("createDate", createDate);
f2vs.Add("messageCount", messageNodes.Count.ToString());
f2vs.Add("authorCount", authorNames.Count.ToString());
f2vs.Add("commentPublisher", "");
f2vs.Add("commentPublished", "");
f2vs.Add("id", "");
IRow newPageListRow = subjectEW.AddRow(f2vs);
f2vs["commentPublished"] = "No";
f2vs["commentPublisher"] = "sunhua";
f2vs["id"] = "sunhua" + index;
subjectXW.AddRow(f2vs);
f2vs["commentPublisher"] = "shizhengzhong";
f2vs["id"] = "shizhengzhong" + index;
subjectXW.AddRow(f2vs);
f2vs["commentPublisher"] = "liyuzhu";
f2vs["id"] = "liyuzhu" + index;
subjectXW.AddRow(f2vs);
ICell localUrlCell = subjectEW.GetCell(newPageListRow, "url", true);
IHyperlink hyperlink = new XSSFHyperlink(HyperlinkType.File);
hyperlink.Address = "html/" + htmlLocalName;
localUrlCell.Hyperlink = hyperlink;
//保存message文本
string msg = Encoding.UTF8.GetString(Encoding.Convert(Encoding.ASCII, Encoding.UTF8, Encoding.ASCII.GetBytes(title))).Trim();
if (msg.Length > 0)
{
string titleTextFilePath = Path.Combine(outputTitleTextDir, index + ".txt");
CommonUtil.CreateFileDirectory(titleTextFilePath);
TextWriter tw = null;
try
{
tw = new StreamWriter(titleTextFilePath, false, new UTF8Encoding(false));
tw.Write(msg);
tw.Flush();
}
catch (Exception ee)
{
throw ee;
}
finally
{
if (tw != null)
{
tw.Close();
tw.Dispose();
}
}
}
}
catch (Exception ex)
{
if (tr != null)
{
tr.Dispose();
tr = null;
}
this.RunPage.InvokeAppendLogText("读取出错. " + ex.Message + " LocalPath = " + localFilePath, LogLevelType.Error, true);
}
}
}
subjectXW.SaveToDisk();
subjectEW.SaveToDisk();
allCommentsListEW.SaveToDisk();
return(succeed);
}
/// <summary>
/// 根据属性类型创建对应单元格
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>字符串值</returns>
public static void CreateCell(this IRow row, int columnIndex, PropertyInfo property, object instance)
{
var val = property.GetValue(instance);
var cell = row.CreateCell(columnIndex);
if (property.PropertyType.Equals(typeof(string)))
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
else if (property.PropertyType.Equals(typeof(int)) ||
property.PropertyType.Equals(typeof(double)) ||
property.PropertyType.Equals(typeof(decimal)) ||
property.PropertyType.Equals(typeof(float)))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(val));
var attr = property.GetCustomAttributes(typeof(DisplayFormatAttribute), true).FirstOrDefault();
if (attr != null)
{
var format = ((DisplayFormatAttribute)attr).DataFormatString ?? "";
if (!format.IsNullOrEmpty())
{
IDataFormat dataFormat = cell.Sheet.Workbook.CreateDataFormat();
ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.DataFormat = dataFormat.GetFormat(format);
cell.CellStyle = style;
}
}
}
else if (property.PropertyType.Equals(typeof(DateTime)) || property.PropertyType.Equals(typeof(DateTime?)))
{
cell.SetCellType(CellType.Numeric);
var format = "yyyy-MM-dd hh:mm:ss ";
var attr = property.GetCustomAttributes(typeof(DisplayFormatAttribute), true).FirstOrDefault();
if (attr != null)
{
format = ((DisplayFormatAttribute)attr).DataFormatString ?? format;
}
IDataFormat dataFormat = cell.Sheet.Workbook.CreateDataFormat();
ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.DataFormat = dataFormat.GetFormat(format);
cell.SetCellValue(val.TryDateTime());
cell.CellStyle = style;
}
else if (property.PropertyType.Equals(typeof(bool)))
{
cell.SetCellType(CellType.Boolean);
}
else if (property.PropertyType.Equals(typeof(string)) && instance != null)
{
var value = property.GetValue(instance);
if (value != null &&
value.ToString().ToLower().StartsWith("http") ||
value.ToString().ToLower().StartsWith("https"))
{
cell.SetCellType(CellType.Blank);
IHyperlink link;
if (cell.Sheet.Workbook.GetType() == typeof(XSSFWorkbook))
{
link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = val.ToString();
}
else
{
link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = val.ToString();
}
cell.SetCellValue(val.ToString());
cell.Hyperlink = link;
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
}
public static void Write()
{// 创建工作薄
IWorkbook wb = new XSSFWorkbook();
// 创建工作表
ISheet sheet = wb.CreateSheet("Hyperlinks");
//-----文件保护
//sheet.LockFormatRows();
//sheet.LockFormatCells();
//sheet.LockFormatColumns();
//sheet.LockDeleteColumns();
//sheet.LockDeleteRows();
//sheet.LockInsertHyperlinks();
//sheet.LockInsertColumns();
//sheet.LockInsertRows();
//sheet.ProtectSheet("123456");
// 创建第一行
IRow row = sheet.CreateRow(0);
// 创建第一列
ICell cell = row.CreateCell(0);
cell.SetCellValue(123);
//创建第一列为链接
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("baidu");
//除此之外，HyperlinkType 枚举类型还可以是： HyperlinkType.File（文件路径）、HyperlinkType.Email（电子邮件地址）、HyperlinkType.Document（内部文档跳转）。
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url)
{
Address = "https://www.baidu.com"
};
cell1.Hyperlink = link;
//设置单元格字体
IFont font = wb.CreateFont();
font.Color = IndexedColors.Red.Index; //设置颜色
font.IsItalic = false; //斜体字
font.Underline = FontUnderlineType.Double; //下划线
font.IsStrikeout = false; //删除
font.IsBold = false; //粗体
font.FontHeightInPoints = 20;
// 绑定字体样式到样式对象上
ICellStyle style1 = wb.CreateCellStyle();
style1.SetFont(font);
cell1.CellStyle = style1;
//设置单元格边框样式
ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = IndexedColors.Black.Index;
style.BorderLeft = BorderStyle.DashDotDot;
style.LeftBorderColor = IndexedColors.Green.Index;
style.BorderRight = BorderStyle.Hair;
style.RightBorderColor = IndexedColors.Blue.Index;
style.BorderTop = BorderStyle.MediumDashed;
style.TopBorderColor = IndexedColors.Orange.Index;
// 设置边框对角线样式
style.BorderDiagonalLineStyle = BorderStyle.Medium; // BorderDiagonalLineStyle 属性必须在 BorderDiagonal 和 BorderDiagonalColor 之前设置
style.BorderDiagonal = BorderDiagonal.Forward;
style.BorderDiagonalColor = IndexedColors.Gold.Index;
IDataFormat format = wb.CreateDataFormat();
ICell cell3 = sheet.CreateRow(0).CreateCell(0);
// 小数保留两位 - "1.20"
cell3.SetCellValue(1.2);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cell3.CellStyle = cellStyle;
// 绑定样式
cell3.CellStyle = style;
// 设置列宽，第一个参数为第几列（从 0 开始计数），第二个参数为宽度值，注意值为 256 的倍数
sheet.SetColumnWidth(1, 100 * 256);
sheet.SetColumnWidth(2, 150 * 256);
// 设置行高，注意值为 20 的倍数
sheet.CreateRow(1).Height = 200 * 20;
sheet.CreateRow(2).Height = 300 * 20;
cell.SetCellValue(new XSSFRichTextString("This is a test of merging"));
ISheet sheet1 = wb.CreateSheet("new sheet");
ISheet sheet2 = wb.CreateSheet("second sheet");
ISheet sheet3 = wb.CreateSheet("third sheet");
ISheet sheet4 = wb.CreateSheet("fourth sheet");
// CreateFreezePane 方法参数说明：
// 第一个参数表示要冻结的列数，从 1 开始计数，如果不需要冻结设为 0。
// 第二个参数表示要冻结的行数，从 1 开始计数，如果不需要冻结设为 0。
// 第三个参数表示右边区域可见的首列序号，从 1 开始计数，如果不需要设置则设为 0。
// 第四个参数表示下边区域可见的首行序号，从 1 开始计数，如果不需要设置则设为 0。
// 冻结第一行
sheet1.CreateFreezePane(0, 1, 0, 1);
// 冻结第一列
sheet2.CreateFreezePane(1, 0, 1, 0);
// 冻结列和行（忽略右下象限的滚动位置）
sheet3.CreateFreezePane(2, 2);
// 创建一个左下角为活动象限的分割
sheet4.CreateSplitPane(2000, 2000, 0, 0, PanePosition.LowerLeft);
// 参数格式：new CellRangeAddress(起始第几行，结束第几行，起始第几列，结束第几列)
// 合并 A1 和 B1 两个单元格
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2));
// 保存文件
FileStream sw = File.Create("assert/test.xlsx");//new FileStream("G://test.xlsx", FileMode.Open, FileAccess.Write);//
wb.Write(sw);
sw.Close();
}
/// <summary>
/// 导出产品
/// </summary>
/// <param name="userModel"></param>
/// <param name="categoryId"></param>
/// <param name="keyword"></param>
/// <param name="msg"></param>
/// <param name="bt"></param>
/// <returns></returns>
public bool ExportProduct(UserModel userModel, int?categoryId, string keyword, out string msg, out byte[] bt)
{
bt = null;
var path = $"{Config.Root}/static/files/ExportProduct.xlsx";
if (!File.Exists(path))
{
msg = "配置错误";
return(false);
}
var result = GetProductList(userModel, 1, int.MaxValue, categoryId, keyword);
var list = result.data;
bt = ExcelHelper.Export(path, wb =>
{
var sheet = wb.GetSheetAt(0);
for (var i = 0; i < list.Count - 1; i++)
{
sheet.CopyRowSample(i + 1, i + 2);
}
for (var i = 0; i < list.Count; i++)
{
var item = list[i];
var row = sheet.GetRow(i + 1);
//赋值
row.Cells[0].SetCellValue(item.CategoryName);
row.Cells[1].SetCellValue(item.ProductSku);
row.Cells[2].SetCellValue(item.ProductName);
row.Cells[3].SetCellValue(item.ProductSpecification);
row.Cells[4].SetCellValue(item.ProductRemark);
row.Cells[5].SetCellValue(item.ProductWeight);
row.Cells[6].SetCellValue(item.OrderQty);
// ReSharper disable once SpecifyACultureInStringConversionExplicitly
row.Cells[7].SetCellValue(Convert.ToDouble(item.Price1));
// ReSharper disable once SpecifyACultureInStringConversionExplicitly
row.Cells[8].SetCellValue(Convert.ToDouble(item.Price10));
// ReSharper disable once SpecifyACultureInStringConversionExplicitly
row.Cells[9].SetCellValue(Convert.ToDouble(item.Price100));
if (item.ImageQty == 0)
{
row.Cells[10].SetCellValue("");
}
else
{
row.Cells[10].SetCellValue("Picture");
var hssfHyperlink = new XSSFHyperlink(HyperlinkType.Url)
{
Address = $"{Config.WebUrl}/ProductImage?id={item.ProductId}",
};
row.Cells[10].Hyperlink = hssfHyperlink;
}
}
});
msg = null;
return(true);
}
public void Serialize <T>(IEnumerable <T> dataList)
{
if (!dataList.Any())
{
return;
}
var type = typeof(T);
var worksheetAttribute =
(ExcelWorksheet)type.GetCustomAttributes().First(attr => attr.GetType() == typeof(ExcelWorksheet));
var worksheetName = worksheetAttribute.SheetName;
var worksheet = _workbook.CreateSheet(worksheetName);
var dataFormat = _workbook.CreateDataFormat();
var patriarch = worksheet.CreateDrawingPatriarch();
var headerRowStyle = _workbook.CreateCellStyle();
var font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 12;
font.Color = HSSFColor.White.Index;
headerRowStyle.SetFont(font);
headerRowStyle.Alignment = HorizontalAlignment.Center;
headerRowStyle.FillForegroundColor = HSSFColor.RoyalBlue.Index;
headerRowStyle.FillPattern = FillPattern.SolidForeground;
var dataProperties = type.GetProperties().OrderBy(prop =>
{
var columnAttribute =
(ExcelColumn)prop.GetCustomAttributes().First(attr => attr.GetType() == typeof(ExcelColumn));
return(columnAttribute.Index);
});
var rowIndex = 0;
var columnIndex = 0;
var row = worksheet.CreateRow(rowIndex++);
foreach (var dataProperty in dataProperties)
{
var columnAttribute = (ExcelColumn)dataProperty.GetCustomAttributes()
.First(attr => attr.GetType() == typeof(ExcelColumn));
var cell = row.CreateCell(columnIndex++);
cell.SetCellType(CellType.String);
cell.SetCellValue(columnAttribute.HeaderName);
cell.CellStyle = headerRowStyle;
}
worksheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dataProperties.Count() - 1));
foreach (var data in dataList)
{
row = worksheet.CreateRow(rowIndex++);
columnIndex = 0;
foreach (var dataProperty in dataProperties)
{
var columnAttribute = (ExcelColumn)dataProperty.GetCustomAttributes()
.First(attr => attr.GetType() == typeof(ExcelColumn));
var dataValue = dataProperty.GetValue(data);
var cell = row.CreateCell(columnIndex++);
var cellStyle = _workbook.CreateCellStyle();
cellStyle.DataFormat = dataFormat.GetFormat(columnAttribute.DataFormat);
cell.CellStyle = cellStyle;
if (columnAttribute.IsList)
{
var outputList = (from object v in (IEnumerable)dataValue select Convert.ToString(v)).ToList();
cell.SetCellValue(string.Join(", ", outputList));
}
else if (columnAttribute.IsImageLink)
{
using (var webClient = new WebClient())
{
try
{
var image = Image.FromStream(webClient.OpenRead(new Uri(Convert.ToString(dataValue))));
var sizeRatio = ((decimal)image.Height / image.Width);
var thumbHeight = 100;
var thumbWidth = decimal.ToInt32(sizeRatio * thumbHeight);
var thumbStream =
image.GetThumbnailImage(thumbWidth, thumbHeight, () => false, IntPtr.Zero);
var memoryStream = new MemoryStream();
thumbStream.Save(memoryStream, ImageFormat.Jpeg);
var pictureIndex = _workbook.AddPicture(memoryStream.ToArray(), PictureType.JPEG);
var anchor = new XSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, cell.RowIndex, 0, 0);
var picture = patriarch.CreatePicture(anchor, pictureIndex);
var size = picture.GetImageDimension();
row.HeightInPoints = size.Height;
picture.Resize();
anchor.Dx1 = 5;
anchor.Dy1 = 2;
}
catch (Exception ex)
{
cell.SetCellValue(ex.Message);
}
}
}
else if (columnAttribute.IsLink)
{
var hlinkStyle = _workbook.CreateCellStyle();
var hlinkFont = _workbook.CreateFont();
hlinkFont.Underline = FontUnderlineType.Single;
hlinkFont.Color = HSSFColor.Blue.Index;
hlinkStyle.SetFont(hlinkFont);
var link =
new XSSFHyperlink(HyperlinkType.Url)
{
Address = (Convert.ToString(dataValue))
};
cell.Hyperlink = (link);
cell.SetCellValue(Convert.ToString(dataValue));
cell.CellStyle = (hlinkStyle);
}
else
{
if (dataValue is bool)
{
cell.SetCellValue((bool)dataValue);
cell.SetCellType(CellType.Boolean);
}
else if (dataValue is int)
{
cell.SetCellValue((int)dataValue);
cell.SetCellType(CellType.Numeric);
}
else if (dataValue is long)
{
cell.SetCellValue((long)dataValue);
cell.SetCellType(CellType.Numeric);
}
else if (dataValue is float)
{
cell.SetCellValue((float)dataValue);
cell.SetCellType(CellType.Numeric);
}
else if (dataValue is double)
{
cell.SetCellValue((double)dataValue);
cell.SetCellType(CellType.Numeric);
}
else if (dataValue is DateTime)
{
cell.SetCellValue((DateTime)dataValue);
}
else
{
cell.SetCellValue(Convert.ToString(dataValue));
cell.SetCellType(CellType.String);
}
}
}
}
for (var i = 0; i < columnIndex; i++)
{
worksheet.AutoSizeColumn(i);
}
}
public static bool expotrDataToExcel(string TemplatePath, string exportPath)
{
try
{
XSSFWorkbook wk = null;
using (FileStream fs = File.Open(TemplatePath, FileMode.Open,
FileAccess.Read, FileShare.ReadWrite))
{
//把xls文件读入workbook变量里，之后就可以关闭了
wk = new XSSFWorkbook(fs);
fs.Close();
}
XSSFSheet sheet1 = (XSSFSheet)wk.GetSheetAt(0);
List <DtsClass> list = new List <DtsClass>();
DtsClass st1 = new DtsClass()
{
DTSId = Guid.NewGuid().ToString(), DTSNo = "123456"
};
list.Add(st1);
if (list != null)
{
int nRow = 3;
string nextFirstTxt = string.Empty;
foreach (DtsClass item in list)
{
IRow row = sheet1.CreateRow(nRow);
Type tp = item.GetType();
PropertyInfo[] piArry = tp.GetProperties();
for (int i = 1; i < piArry.Length; i++)
{
ICell cell = row.CreateCell(i - 1);
if (i == 1)
{
//创建一个超链接对象
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = "https://www.baidu.com";
cell.Hyperlink = link;
}
object filedValue = tp.GetProperty(piArry[i].Name).GetValue(item, null);
if (filedValue == null)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(filedValue.ToString());
}
}
nRow++;
}
}
//创建文件
using (FileStream files = new FileStream(exportPath, FileMode.Create))
{
wk.Write(files);
}
}
catch (Exception err)
{
return(false);
}
return(true);
}
static void Main(string[] args)
{
XSSFWorkbook workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("测试");
sheet.SetColumnWidth(0, 10 * 256); //设置宽度，超过255会抛异常
sheet.SetColumnWidth(1, 20 * 256); //设置宽度，超过255会抛异常
sheet.SetColumnWidth(2, 30 * 256); //设置宽度，超过255会抛异常
//标题单元格字体
var headerFont = (XSSFFont)workbook.CreateFont();
headerFont.Color = HSSFColor.Black.Index;
headerFont.FontHeightInPoints = 11;
headerFont.IsBold = true;
//标题单元格样式
var headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderBottom = BorderStyle.Thick;
headerStyle.SetFont(headerFont);
//创建标题行
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
#region 填充标题内容
var headerCol0 = headerRow.CreateCell(0);
headerCol0.SetCellType(CellType.String);
headerCol0.SetCellValue("序号");
headerCol0.CellStyle = headerStyle;
var headerCol1 = headerRow.CreateCell(1);
headerCol1.SetCellType(CellType.String);
headerCol1.SetCellValue("名称");
headerCol1.CellStyle = headerStyle;
var headerCol2 = headerRow.CreateCell(2);
headerCol2.SetCellType(CellType.String);
headerCol2.SetCellValue("图片");
headerCol2.CellStyle = headerStyle;
var headerCol3 = headerRow.CreateCell(3);
headerCol3.SetCellType(CellType.String);
headerCol3.SetCellValue("部门");
headerCol3.CellStyle = headerStyle;
#endregion
//数据单元格字体1
XSSFFont hyperLinkFont = (XSSFFont)workbook.CreateFont();
hyperLinkFont.Color = HSSFColor.Blue.Index;
hyperLinkFont.FontHeightInPoints = 11;
//数据单元格样式1
ICellStyle hyperLinkStyle = workbook.CreateCellStyle();
hyperLinkStyle.Alignment = HorizontalAlignment.Left;
hyperLinkStyle.VerticalAlignment = VerticalAlignment.Center;
hyperLinkStyle.FillForegroundColor = HSSFColor.Blue.Index;
hyperLinkStyle.BorderLeft = BorderStyle.Thin;
hyperLinkStyle.BorderRight = BorderStyle.Thin;
hyperLinkStyle.BorderTop = BorderStyle.Thin;
hyperLinkStyle.BorderBottom = BorderStyle.Thin;
hyperLinkStyle.SetFont(hyperLinkFont);
//数据单元格字体2
XSSFFont normalCellFont = (XSSFFont)workbook.CreateFont();
normalCellFont.Color = HSSFColor.Black.Index;
normalCellFont.FontHeightInPoints = 11;
//数据单元格样式2
var normalCellStyle = workbook.CreateCellStyle();
normalCellStyle.Alignment = HorizontalAlignment.Left;
normalCellStyle.VerticalAlignment = VerticalAlignment.Center;
normalCellStyle.BorderLeft = BorderStyle.Thin;
normalCellStyle.BorderRight = BorderStyle.Thin;
normalCellStyle.BorderTop = BorderStyle.Thin;
normalCellStyle.BorderBottom = BorderStyle.Thin;
normalCellStyle.SetFont(normalCellFont);
#region 填充数据内容
int rownum = 1;
while (rownum < 10)
{
var dataRow = sheet.CreateRow(rownum);
dataRow.HeightInPoints = 15;
var dataCol00 = dataRow.CreateCell(0);
dataCol00.SetCellType(CellType.Numeric);
dataCol00.SetCellValue(rownum);
dataCol00.CellStyle = normalCellStyle;
var dataCol01 = dataRow.CreateCell(1);
dataCol01.SetCellType(CellType.String);
dataCol01.SetCellValue($"这是内容-{rownum}");
dataCol01.CellStyle = normalCellStyle;
//超链接
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = $"{rownum}.png";
var dataCol02 = dataRow.CreateCell(2);
dataCol02.SetCellType(CellType.String);
dataCol02.SetCellValue("点击查看图片");
dataCol02.CellStyle = hyperLinkStyle; //设置样式
dataCol02.Hyperlink = link; //设置超链接
var dataCol03 = dataRow.CreateCell(3);
dataCol03.SetCellType(CellType.String);
dataCol03.SetCellValue(rownum > 4 ? "研发部" : "财务部");
dataCol03.CellStyle = normalCellStyle;
rownum++;
}
#endregion
//合并单元格测试
sheet.AddMergedRegion(new CellRangeAddress(1, 4, 3, 3)); //firstRow,lastRow,firstColumn,lastColumn
sheet.AddMergedRegion(new CellRangeAddress(5, 9, 3, 3)); //firstRow,lastRow,firstColumn,lastColumn
//写入文件
FileStream file = new FileStream(@"C:\my.xlsx", FileMode.OpenOrCreate);
workbook.Write(file);
file.Close();
}
public void Run()
{
IWorkbook workbook = new XSSFWorkbook();
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.Document);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
// font Test용 Sheet생성
ISheet sheet1 = workbook.CreateSheet("Font_Test Sheet");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "굴림체";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
/*
* BorderStryle
* */
ISheet sheet3 = workbook.CreateSheet("BorderStype");
IRow row = sheet3.CreateRow(1);
// Create a cell and put a value in it.
cell = row.CreateCell(1);
cell.SetCellValue(4);
// Style the cell with borders all around.
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = IndexedColors.Black.Index;
style.BorderLeft = BorderStyle.DashDotDot;
style.LeftBorderColor = IndexedColors.Green.Index;
style.BorderRight = BorderStyle.Hair;
style.RightBorderColor = IndexedColors.Blue.Index;
style.BorderTop = BorderStyle.MediumDashed;
style.TopBorderColor = IndexedColors.Orange.Index;
//create border diagonal
style.BorderDiagonalLineStyle = BorderStyle.Medium; //this property must be set before BorderDiagonal and BorderDiagonalColor
style.BorderDiagonal = BorderDiagonal.Forward;
style.BorderDiagonalColor = IndexedColors.Gold.Index;
cell.CellStyle = style;
// Create a cell and put a value in it.
cell2 = row.CreateCell(2);
cell2.SetCellValue(5);
style2 = workbook.CreateCellStyle();
style2.BorderDiagonalLineStyle = BorderStyle.Medium;
style2.BorderDiagonal = BorderDiagonal.Backward;
style2.BorderDiagonalColor = IndexedColors.Red.Index;
cell2.CellStyle = style2;
using (FileStream fs = File.Create(@"C:\00.Dev\temp\HyprtLink_Font.xlsx"))
{
workbook.Write(fs);
}
}
