//----7----
private static int CreateFixSheet(ISheet fixSheet, List <MainClass> mainClasses, string sysName, string outputPath, int rowCount)
{
//Header'lar için row oluşturdum
IRow hrow = fixSheet.CreateRow(0);
string[] headers = new[] { "NE_Name", "Id", "Description", "QOS_Profile_Name", "FIX_QOS_Description", "Check_QOS" };
for (int i = 0; i < headers.Count(); i++)
{
hrow.CreateCell(i).SetCellValue(headers.ElementAt(i));
hrow.GetCell(i).CellStyle = Headerstyle;
}
foreach (var mainClass in mainClasses)
{
foreach (var subClass in mainClass.SubClasses.Where(x => x.Description.Contains("FIX")))
{
IRow subRow = fixSheet.CreateRow(rowCount++);
var subValues = new[] { sysName, subClass.Id, subClass.Description, subClass.QOS_Profile_Name, subClass.FIX_QOS_Description,
subClass.Check_QOS.ToString() };
for (int i = 0; i < subValues.Count(); i++)
{
subRow.CreateCell(i).SetCellValue(subValues.ElementAt(i));
}
}
}
using (var fs = new FileStream(outputPath, FileMode.OpenOrCreate, FileAccess.Write))
{
Hssfworkbook.Write(fs);
}
return(rowCount);
}
public MemoryStream ExcelGetMemoryStream()
{
//Write the stream data of workbook to the root directory
var file = new MemoryStream();
Hssfworkbook.Write(file);
return(file);
}
public void ExcelSaveToFile(String path)
{
//Write the stream data of workbook to the root directory
var file = new FileStream(path, FileMode.Create);
Hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 创建文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="mode">保存方式</param>
public void CreateFile(string filePath, FileMode mode)
{
FilePath = filePath;
VerifyFileName();
FileStream file = new FileStream(FilePath, mode);
Hssfworkbook.Write(file);
file.Close();
}
//----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);
}
}