/// <summary>
/// 添加工作薄
/// </summary>
/// <param name="sheetName"></param>
public HSSFSheet AddSheet(string sheetName)
{
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "sheet" + Hssfworkbook.Workbook.NumSheets;
}
return((HSSFSheet)Hssfworkbook.CreateSheet(sheetName));
}
//----1----
public static void CreateExcel(List <string> filePaths, string outputPath)
{
ISheet sheet1 = Hssfworkbook.CreateSheet("Backup_Data");
sheet1.DefaultColumnWidth = 50;
//Header Row
IRow hrow = sheet1.CreateRow(0);
var headerList = MainClass.Headers;
var subHeaderList = SubClass.Headers;
SetHeaders(hrow, headerList, subHeaderList);
var ortakIpler = new List <OrtakIp>();
// 0'da header'lar olduğundan rowCount 1 den başlattım.
var rowCount = 1;
var fixRowCount = 1;
ISheet fixSheet = Hssfworkbook.CreateSheet("Check Qos");
//----1----
fixSheet.DefaultColumnWidth = 50;
foreach (var filePath in filePaths)
{
StreamReader sw = new StreamReader(filePath);
var mainString = sw.ReadToEnd();
var parsedString = mainString.Split('#').ToList();
var rnRemoved = parsedString.Remove("\r\n");
var blackRemoved = parsedString.Remove("");
var sysName = GetFolderName(parsedString.Single(x => x.ToLower().Contains("sysname")));
sw.Close();
var mainClasses = CreateMainClassList(parsedString);
foreach (var mainClass in mainClasses)
{
ortakIpler.Add(new OrtakIp(sysName, mainClass.Id, mainClass.Ip));
rowCount = SetMainClassRow(sheet1, headerList, subHeaderList, rowCount, sysName, mainClass);
foreach (var subClass in mainClass.SubClasses)
{
ortakIpler.Add(new OrtakIp(sysName, subClass.Id, subClass.IP));
rowCount = SetSubClassRow(sheet1, headerList, rowCount, sysName, subClass);
}
using (var fs = new FileStream(outputPath, FileMode.OpenOrCreate, FileAccess.Write))
{
Hssfworkbook.Write(fs);
}
}
fixRowCount = CreateFixSheet(fixSheet, mainClasses, sysName, outputPath, fixRowCount);
}
ortakIpler = ortakIpler.Where(x => x.Ip != null).ToList();
SetOrtakIp(ortakIpler.Where(x => !x.Ip.Equals("NULL", StringComparison.CurrentCultureIgnoreCase)).ToList(), outputPath);
}
private static void CreateExcelForCommonIp(List <OrtakIp> dublicateIp, string outputPath)
{
ISheet sheet1 = Hssfworkbook.CreateSheet("Duplicate Ip");
sheet1.DefaultColumnWidth = 50;
IFont font1 = Hssfworkbook.CreateFont();
font1.Color = HSSFColor.Blue.Index;
font1.Boldweight = (short)FontBoldWeight.Bold;
font1.FontHeightInPoints = 10;
ICellStyle headerstyle = Hssfworkbook.CreateCellStyle();
headerstyle.SetFont(font1);
IRow hrow = sheet1.CreateRow(0);
var headerList = new List <string>()
{
"Name", "Id", "Ip"
};
for (int i = 0; i < headerList.Count(); i++)
{
hrow.CreateCell(i).SetCellValue(headerList.ElementAt(i) == null ? "NULL" : headerList.ElementAt(i));
hrow.GetCell(i).CellStyle = headerstyle;
}
var rowCount = 1;
for (int i = 0; i < dublicateIp.Count(); i++)
{
IRow row = sheet1.CreateRow(rowCount++);
var values = new[] { dublicateIp.ElementAt(i).Name, dublicateIp.ElementAt(i).Id, dublicateIp.ElementAt(i).Ip };
for (int j = 0; j < values.Count(); j++)
{
row.CreateCell(j).SetCellValue(values.ElementAt(j) == string.Empty ? "NULL" : values.ElementAt(j));
}
}
using (var fs = new FileStream(outputPath, FileMode.OpenOrCreate, FileAccess.Write))
{
Hssfworkbook.Write(fs);
}
}
