//----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);
}
/// <summary>
/// 初始化样式
/// </summary>
/// <param name="style"></param>
public void InitializeStyle(HSSFCellStyle style)
{
if (style == null)
{
style = (HSSFCellStyle)Hssfworkbook.CreateCellStyle();
}
}
public MemoryStream ExcelGetMemoryStream()
{
//Write the stream data of workbook to the root directory
var file = new MemoryStream();
Hssfworkbook.Write(file);
return(file);
}
/// <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));
}
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);
}
}
public void AddLogo(int rowNumber, int columnIndex)
{
var stream = new MemoryStream();
Resources.logo.Save(stream, ImageFormat.Png);
int logoId = Hssfworkbook.AddPicture(stream.ToArray(), PictureType.PNG);
Drawing patriarch = Sheet.CreateDrawingPatriarch();
// create the anchor
// var anchor = new HSSFClientAnchor();
// anchor.AnchorType = 2;
//var anchor = //new HSSFClientAnchor(0, 0, 1023, 255, columnIndex, rowNumber, columnIndex, rowNumber);
var anchor = new HSSFClientAnchor
{
Dx1 = 0,
Dx2 = 0,
Dy1 = 0,
Dy2 = 0,
AnchorType = 3,
Row1 = rowNumber,
Row2 = rowNumber,
Col1 = columnIndex,
Col2 = columnIndex
};
//load the picture and get the picture index in the workbook
Picture picture = patriarch.CreatePicture(anchor, logoId);
//patriarch.CreatePicture(anchor, logoId);
//Reset the image to the original size.
picture.Resize();
/*
* FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
* byte[] buffer = new byte[file.Length];
* file.Read(buffer, 0, (int)file.Length);
* return wb.AddPicture(buffer, HSSFWorkbook.PICTURE_TYPE_JPEG);
*/
}
public Cell AddCell(Object value, int columnIndex, Row row, TextStyle textStyle, short backgroundColor,
CellBorderType borderStyle, short borderColor)
{
Cell cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);
CellStyle cellStyle = Styels[textStyle];
if (value == null)
{
cell.SetCellValue("");
}
else if (value.GetType() == typeof(DateTime))
{
cell.SetCellValue((DateTime)value);
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
}
else if (value.GetType() == typeof(double) ||
value.GetType() == typeof(float) ||
value.GetType() == typeof(decimal))
{
cell.SetCellValue((double)value);
DataFormat format = Hssfworkbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("# ### ### ##0.00");
}
else if (value.GetType() == typeof(Int16) ||
value.GetType() == typeof(Int32) ||
value.GetType() == typeof(Int64) ||
value.GetType() == typeof(UInt16) ||
value.GetType() == typeof(UInt32) ||
value.GetType() == typeof(UInt64) ||
value.GetType() == typeof(Byte) ||
value.GetType() == typeof(SByte))
{
cell.SetCellValue((int)value);
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
}
else if (value.GetType() == typeof(bool))
{
cell.SetCellValue((bool)value);
}
else
{
cell.SetCellValue(value.ToString());
}
if (backgroundColor != short.MinValue)
{
cellStyle.FillForegroundColor = backgroundColor;
cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
}
if (borderColor != short.MinValue)
{
cellStyle.BorderBottom = borderStyle;
cellStyle.BorderLeft = borderStyle;
cellStyle.BorderRight = borderStyle;
cellStyle.BorderTop = borderStyle;
cellStyle.BottomBorderColor = borderColor;
cellStyle.LeftBorderColor = borderColor;
cellStyle.RightBorderColor = borderColor;
cellStyle.TopBorderColor = borderColor;
}
cell.CellStyle = cellStyle;
return(cell);
}
/// <summary>
/// 获取工作薄
/// </summary>
/// <param name="index">索引</param>
/// <returns>工作薄</returns>
public HSSFSheet GetSheet(int index)
{
return((HSSFSheet)Hssfworkbook.GetSheetAt(index));
}
/// <summary>
/// 获取工作薄
/// </summary>
/// <param name="sheetName">工作薄名称</param>
/// <returns>工作薄</returns>
public HSSFSheet GetSheet(string sheetName)
{
return((HSSFSheet)Hssfworkbook.GetSheet(sheetName));
}
