public static XSSFWorkbook BuildSwitchData <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames)
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(SheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
XSSFRow row_Title = (XSSFRow)sheet.CreateRow(0); //创建列头行
#region 生成列头
int ii = 0;
foreach (string key in FiedNames.Keys)
{
XSSFCell cell_Title = (XSSFCell)row_Title.CreateCell(ii); //创建单元格
//cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(key);
//sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
ii++;
}
#endregion
//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (FiedNames.Values.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});
for (int i = 0; i < list.Count; i++)
{
XSSFRow row_Content = (XSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 20;
int jj = 0;
foreach (string proName in FiedNames.Values)
{
if (dictPros.ContainsKey(proName))
{
XSSFCell cell_Conent = (XSSFCell)row_Content.CreateCell(jj); //创建单元格
//如果存在，则取出要属性对象
PropertyInfo proInfo = dictPros[proName];
//获取对应属性的值
object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
string cell_value = value == null ? "" : value.ToString();
cell_Conent.SetCellValue(cell_value);
jj++;
}
}
}
return(wb);
}
public void CreateFreezePane(int colSplit, int rowSplit)
{
_sh.CreateFreezePane(colSplit, rowSplit);
}
/// <summary>
/// export the data
/// </summary>
/// <param name="lst"></param>
/// <param name="fileName"></param>
/// <param name="lFolder"></param>
/// <param name="rFolder"></param>
/// <returns></returns>
public static bool Export(List <Level> lst, string fileName, string lFolder, string rFolder)
{
bool isSuccess = false;
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workBook.CreateSheet();
try
{
//add header style
ICellStyle cellStyle = workBook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workBook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 12;
font.FontName = "Arial Unicode MS";
cellStyle.SetFont(font);
#region header settings
IRow first = sheet.CreateRow(0);
ICell fst_cell = first.CreateCell(0);
fst_cell.SetCellValue("Quarterly Source Code Comparison and Retrofit ");
fst_cell.CellStyle = cellStyle;
IRow second = sheet.CreateRow(1);
IRow third = sheet.CreateRow(2);
third.CreateCell(0).SetCellValue("Date:");
string[,] arr = new string[2, 4] {
{ "GIT Version:", "", "Source Path(L):", lFolder }, { "Production Version:", "", "Source Path(R):", rFolder }
};
for (int i = 0; i < 2; i++)
{
IRow forth_fifth = sheet.CreateRow(i + 3);
for (int j = 0; j < 4; j++)
{
forth_fifth.CreateCell(j).SetCellValue(arr[i, j]);
}
}
IRow sixth = sheet.CreateRow(5);
IRow header = sheet.CreateRow(6);
header.HeightInPoints = 12;
List <string> headers = new List <string> {
"Production Version", "GIT Version", ""
};
ICellStyle cs = workBook.CreateCellStyle();
cs.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cs.FillPattern = FillPattern.SolidForeground;
cs.Alignment = HorizontalAlignment.Center;
cs.VerticalAlignment = VerticalAlignment.Center;
cs.SetFont(font);
cs.BorderTop = BorderStyle.Thin;
cs.BorderRight = BorderStyle.Thin;
cs.BorderBottom = BorderStyle.Thin;
cs.BorderLeft = BorderStyle.Thin;
for (int m = 0; m < headers.Count; m++)
{
CellRangeAddress region = new CellRangeAddress(6, 6, m * 3, m * 3 + 2);
sheet.AddMergedRegion(region);
for (int n = 0; n < 3; n++)
{
ICell cell = header.CreateCell(3 * m + n);
cell.SetCellValue(headers[m]);
cell.CellStyle = cs;
}
}
IRow title = sheet.CreateRow(7);
title.HeightInPoints = 12;
ICellStyle titleStyle = workBook.CreateCellStyle();
titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.Alignment = HorizontalAlignment.Left;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.SetFont(font);
titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
List <string> titles = new List <string> {
"Path", "Filename", "Size(Byte)", "Path", "FileName", "Size(Byte)", "Comparision Result", "Check-in GIT", "Need Check", "Remark"
};
for (int j = 0; j < titles.Count; j++)
{
ICell cell = title.CreateCell(j);
cell.SetCellValue(titles[j]);
cell.CellStyle = titleStyle;
}
#endregion
sheet.SetAutoFilter(new CellRangeAddress(8, 8, 0, titles.Count - 2)); //line filter
sheet.CreateFreezePane(0, 8); //line freeze
#region body
for (int i = 0; i < lst.Count; i++)
{
sheet.AutoSizeColumn(i);
IRow body = sheet.CreateRow(i + 8);
string lpath = string.Empty;
string lfile = "NA";
string lsize = "NA";
string rpath = string.Empty;
string rfile = "NA";
string rsize = "NA";
string result = "NA";
if (lst[i].LeftNode != null)
{
lpath = lst[i].LeftNode.Path;
lfile = lst[i].LeftNode.FileName;
result = lst[i].LeftNode.Result;
lsize = lst[i].LeftNode.Size;
}
else
{
lpath = lst[i].LtPath;
}
if (lst[i].RightNode != null)
{
rpath = lst[i].RightNode.Path;
rfile = lst[i].RightNode.FileName;
result = lst[i].RightNode.Result;
rsize = lst[i].RightNode.Size;
}
else
{
rpath = lst[i].RtPath;
}
switch (result)
{
case "ltonly":
result = $"Only exists in {lst[i].LeftNode.Path}";
break;
case "rtonly":
result = $"Only exists in {lst[i].RightNode.Path}";
break;
case "same":
if (lsize.Equals(rsize))
{
result = "same";
}
else
{
result = "same text but different size";
}
break;
default:
result = "Text files are different";
break;
}
body.CreateCell(0).SetCellValue(lpath);
body.CreateCell(1).SetCellValue(lfile);
body.CreateCell(2).SetCellValue(lsize);
body.CreateCell(3).SetCellValue(rpath);
body.CreateCell(4).SetCellValue(rfile);
body.CreateCell(5).SetCellValue(rsize);
body.CreateCell(6).SetCellValue(result);
}
SetColumnWidth(sheet, titles.Count);
#endregion
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
workBook.Write(fs);
workBook.Close();
}
isSuccess = true;
}
catch (Exception ex)
{
ex.Message.Logger();
workBook.Close();
}
return(isSuccess);
}
private void PrintReport()
{
var firstRowNumber = 0;
var firstDataRowNumber = firstRowNumber + _headerLength;
var headerRow = _sheet.CreateRow(_headerLength) as XSSFRow;
var column = 0;
foreach (var item in _configurationList)
{
if (!item.IsVisible)
{
continue;
}
var titleCell = headerRow.CreateCell(column) as XSSFCell;
titleCell.SetCellType(CellType.String);
titleCell.SetCellValue(item.FriendlyName);
titleCell.CellStyle = _headerStyle;
for (var index = 0; index < _model.Count; index++)
{
XSSFRow dataRow;
if (column == 0)
{
dataRow = _sheet.CreateRow(_headerLength + index + 1) as XSSFRow;
}
else
{
dataRow = _sheet.GetRow(_headerLength + index + 1) as XSSFRow;
}
var dataCell = dataRow.CreateCell(column) as XSSFCell;
dataCell.SetCellType(CellType.String);
if (item.Name.IndexOf("Date", StringComparison.InvariantCulture) > -1)
{
dataCell.CellStyle = _dateGridStyle;
}
else
{
dataCell.CellStyle = _generalGridStyle;
}
var value = GetValue(index, item);
if (value != null)
{
dataCell.SetCellValue(value);
}
}
_sheet.AutoSizeColumn(column);
if (_sheet.GetColumnWidth(column) < 3000)
{
_sheet.SetColumnWidth(column, 3000);
}
if (_sheet.GetColumnWidth(column) > 13000)
{
_sheet.SetColumnWidth(column, 13000);
}
if (_sheet.GetColumnWidth(column) < 13000 - 512)
{
_sheet.SetColumnWidth(column, _sheet.GetColumnWidth(column) + 512);
}
column++;
}
BuildCriteriaHeader();
var filterRange = new CellRangeAddress(firstDataRowNumber, _sheet.LastRowNum, headerRow.FirstCellNum,
headerRow.LastCellNum - 1);
_sheet.SetAutoFilter(filterRange);
_sheet.CreateFreezePane(0, firstDataRowNumber + 1);
_sheet.DisplayGridlines = false;
}
/**
* Parse a given excel file containing interested addresses data
* and extract needfull data (complete address, territory number
* and appartment information). A specific row formatting is applied
* depending on the rows contents.
*
* param name="sourceFile" is the location of the file to parse.
*/
public void ParseTerritoryHelperInterestedFile(String sourceFile)
{
XSSFWorkbook wb;
// Reading some configuration/initialization data...
Console.WriteLine("Reading some configuration/initialization data...");
string excelFileLocation = appSettings["source-file-path-addresses"];
string lastDataColumnsCell = appSettings["excel-last-data-column"];
string wantedColumnsListString = appSettings["excel-wanted-columns"];
// If the default source file location should be overwritten...
if (sourceFile != null)
{
// Overwrite the default source file location
excelFileLocation = sourceFile;
}
// Wait until anexcel addresses file is available at the location defined by 'excelFileLocation'
int count = 0;
while (!File.Exists(excelFileLocation) || IsFileLocked(excelFileLocation))
{
if (count == 0)
{
Console.WriteLine("Wait until an excel addresses file is available at " + excelFileLocation + "...");
}
count++;
}
using (FileStream file = new FileStream(@excelFileLocation, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(file);
}
XSSFSheet sh = (XSSFSheet)wb.GetSheetAt(0);
int rowCount = sh.LastRowNum + 1;
Console.WriteLine("Source file row count = " + rowCount);
//Console.WriteLine("Source file columns count = " + sh.GetColumnHelper().lastDataColumnsCell);
SetSheetSpecialConditonalFormatting(sh);
Console.WriteLine("Setting columns auto filters...");
// Set the columns auto filters
sh.SetAutoFilter(CellRangeAddress.ValueOf("A1" + ":" + lastDataColumnsCell + rowCount));
// Get the list of column which should remain in the resulting output.
List <int> wantedColumnsList = wantedColumnsListString.Split(',').Select(int.Parse).ToList();
int columnCount = AlphabetOrdinal(lastDataColumnsCell);
Console.WriteLine("Hiding unnecessary columns...");
// Hide all not wanted columns
for (int i = 0; i < columnCount; i++)
{
if (!wantedColumnsList.Contains(i))
{
sh.SetColumnHidden(i, true);
}
}
// Freeze first row ( headers )
sh.CreateFreezePane(0, 1);
string fileLocation = appSettings["destination-file-path-addresses"];
//string fileLocation = "C:\\Development\\c-sharp\\ExcelNPOI\\adresses-bielefeld.xlsx";
Console.WriteLine("Writing Resulting Excel File to disk: " + fileLocation);
using (var stream = new FileStream(fileLocation, FileMode.Create, FileAccess.Write))
{
wb.Write(stream);
}
//Console.WriteLine("Opening the resulting processed file...");
//Process.Start(fileLocation);
}
