public void ReadRowInSheet(IRow row)
{
heads = new List <ExcelHeadAttribute>();
short cellIndex = row.LastCellNum;//总共多少列
for (short i = 0; i < cellIndex; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{ //对于空列的处理
continue;
}
IRichTextString rtext = cell.RichStringCellValue;
string text = rtext.String;
//文本内容，列索引
if (!string.IsNullOrEmpty(text))
{
ExcelHeadAttribute head = new ExcelHeadAttribute()
{
ColumnIndex = i,
ColumnName = text.Trim()
};
heads.Add(head);
}
}
}
void CompareListView_Select(object obj, EventArgs e)
{//移除选择项，添加到左右两侧
ListView lst = obj as ListView;
if (lst.SelectedItems.Count == 0)
{
return;
}
ListViewItem focus = lst.FocusedItem;
int index = focus.Index;
CompareData merge = focus.Tag as CompareData;
if (leftHead.Count > index)
{ //左侧
ExcelHeadAttribute left = new ExcelHeadAttribute()
{
ColumnIndex = merge.OriginHeadIndex, ColumnName = merge.OriginHeadName
};
lstLeft.InsertRow(left);
leftHead.RemoveAt(index);
}
if (rightHead.Count > index)
{//右侧
ExcelHeadAttribute right = new ExcelHeadAttribute()
{
ColumnIndex = merge.NewHeadIndex, ColumnName = merge.NewHeadName
};
lstRight.InsertRow(right);
rightHead.RemoveAt(index);
}
//合并项
lst.Items.RemoveAt(index);
}
/// <summary>
/// 获取头部集合
/// </summary>
/// <returns></returns>
public static List <ExcelHeadDTO> GetHeads()
{
//结果集
List <ExcelHeadDTO> result = new List <ExcelHeadDTO>();
PropertyInfo[] propertyInfos = typeof(TEntity).GetProperties();
//遍历属性
foreach (PropertyInfo info in propertyInfos)
{
//获取头部特性
ExcelHeadAttribute attribute = info.GetCustomAttribute <ExcelHeadAttribute>();
if (attribute != null)
{
//头部实体赋值
ExcelHeadDTO dto = new ExcelHeadDTO();
dto.PropertyName = info.Name;
dto.HeadName = attribute.HeadName;
dto.IsSetHeadColor = attribute.IsSetHeadColor;
dto.IsLocked = attribute.IsLocked;
dto.SortValue = attribute.SortValue;
dto.ColumnType = attribute.ColumnType;
dto.IsHiddenColumn = attribute.IsHiddenColumn;
dto.ColumnWidth = attribute.ColumnWidth;
dto.Format = attribute.Format;
dto.BackgroundColor = attribute.BackgroundColor;
dto.HeaderBackgroundColor = attribute.HeaderBackgroundColor;
//获取属性
RequiredAttribute requiredAttribute = info.GetCustomAttribute <RequiredAttribute>();
List <string> disableAttrs = new RequiredAttributeValidation <TEntity>().GetDisableAttributes();
//启用必填属性
if (requiredAttribute != null && (disableAttrs == null || disableAttrs.Contains(info.Name) == false))
{
dto.IsValidationHead = true;
}
else
{
dto.IsValidationHead = false;
}
//启用必填属性
Dictionary <string, BaseAttribute> enableAttrs = new RequiredAttributeValidation <TEntity>().GetEnableAttributes();
if (enableAttrs != null && enableAttrs.Keys.Contains(info.Name) == true)
{
dto.IsValidationHead = true;
}
//添加至结果集
result.Add(dto);
}
}
int columnIndex = 0;
//返回结果集
result = result.OrderBy(it => it.SortValue).ToList();
result.ForEach(it => { it.ColumnIndex = columnIndex++; });
return(result);
}
//表头制作
public void CreateHead(IWorkbook workBook, ISheet sheet, ICellStyle cellStyleHeadTitle, ExcelHeadAttribute eha)
{
IRow RowHeadTitle = sheet.CreateRow(eha.rowIndex);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(eha.firstRow, eha.lastRow, eha.firstCol, eha.lastCol));
RowHeadTitle.CreateCell(0).SetCellValue(eha.name);
/*****单元格样式 start******/
cellStyleHeadTitle.WrapText = true;
//高度
RowHeadTitle.HeightInPoints = eha.HeightInPoints; //2 * sheet.DefaultRowHeight / 10;
//字体设置,字体要调用CreateFont()
IFont fontheadTitle = workBook.CreateFont();
fontheadTitle.FontHeightInPoints = eha.fontSize;
//HSSFColor.OliveGreen.Black.Index
fontheadTitle.Color = eha.fontColor;
cellStyleHeadTitle.SetFont(fontheadTitle);
//这里调试出来的,样式一定要给到单元格才有效
RowHeadTitle.Cells[0].CellStyle = cellStyleHeadTitle;
}
//导出
public BaseResult DownLoadBase(string name, int?_year, List <VillageWorkingGroupViewModel> RList)
{
BaseResult _br = new BaseResult();
var _filesavepath = "Files/" + RealName + "/WorkingGroup";
var _fileexits = System.Web.HttpContext.Current.Server.MapPath("~/" + _filesavepath);
if (!Directory.Exists(_fileexits))
{
Directory.CreateDirectory(_fileexits);
}
var path = System.Web.HttpContext.Current.Server.MapPath("~/" + _filesavepath + "/" + name + ".xls");
var dt = new System.Data.DataTable();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("村级防汛防台工作组");
//合并第一行单元格
//IRow RowHead = sheet.CreateRow(0);
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
//string noitce = "注意： ";
//noitce += "请不要修改<村级防汛防台工作组>表的格式，如果在系统上新增了岗位请重新下载数据模板";
//RowHead.CreateCell(0).SetCellValue(noitce);
#region 标题
var cellStyleHeadTitle = hssfworkbook.CreateCellStyle();
cellStyleHeadTitle.Alignment = HorizontalAlignment.Center; //居中显示
cellStyleHeadTitle.VerticalAlignment = VerticalAlignment.Top; //垂直居中
ExcelHeadAttribute eha = new ExcelHeadAttribute()
{
rowIndex = 0,
firstRow = 0,
lastRow = 0,
firstCol = 0,
lastCol = 5,
fontColor = 8,
fontSize = 16,
HeightInPoints = 50,
name = "村级防汛防台工作组"
};
CreateHead(hssfworkbook, sheet, cellStyleHeadTitle, eha);
#endregion
#region 提醒
var cellStyleHeadNotice = hssfworkbook.CreateCellStyle();
cellStyleHeadNotice.Alignment = HorizontalAlignment.Left; //居中显示
cellStyleHeadNotice.VerticalAlignment = VerticalAlignment.Top; //垂直居中
string noitce = "注意： ";
noitce += "请不要修改<村级防汛防台工作组>表的格式，如果在系统上新增了岗位请重新下载数据模板";
ExcelHeadAttribute ehanotice = new ExcelHeadAttribute()
{
rowIndex = 1,
firstRow = 1,
lastRow = 1,
firstCol = 0,
lastCol = 5,
fontColor = 10,
fontSize = 10,
HeightInPoints = 20,
name = noitce
};
CreateHead(hssfworkbook, sheet, cellStyleHeadNotice, ehanotice);
#endregion
//表头
IRow RowBody = sheet.CreateRow(2);
List <string> listtitle = new List <string>();
listtitle.Add("行政村名称"); listtitle.Add("岗位"); listtitle.Add("责任人姓名");
listtitle.Add("责任人职务"); listtitle.Add("责任人手机"); listtitle.Add("备注");
for (int iColumnIndex = 0; iColumnIndex < listtitle.Count; iColumnIndex++)
{
RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString());
RowBody.Cells[iColumnIndex].Row.HeightInPoints = 20;
//单元格样式
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//是否换行
cellStyle.WrapText = true;
RowBody.Cells[iColumnIndex].CellStyle = cellStyle;
//字体大小
IFont cellfont = hssfworkbook.CreateFont();
cellfont.FontHeightInPoints = 14;
cellStyle.SetFont(cellfont);
sheet.SetColumnWidth(iColumnIndex, 20 * 256);
}
/****内容****/
try
{
int i = 0;
RList.ForEach(w => {
IRow row1 = sheet.CreateRow(i + 3);
for (int j = 0; j < 6; j++)
{
ICell cell = row1.CreateCell(j);
switch (j)
{
case 0: cell.SetCellValue(RList[i].adnm.ToString()); break;
case 1: cell.SetCellValue(RList[i].Post.ToString()); break;
case 2: cell.SetCellValue(RList[i].PersonLiable.ToString()); break;
case 3: cell.SetCellValue(RList[i].Position.ToString()); break;
case 4: cell.SetCellValue(RList[i].HandPhone.ToString()); break;
case 5: cell.SetCellValue(RList[i].Remarks.ToString()); break;
}
}
i++;
});
_br.IsSuccess = true;
#region 日志
StringBuilder sb = new StringBuilder();
sb.Append("在栏目{组织责任/行政村防汛防台工作组}下,导出数据{" + RList.Count + "}条");
_ILogHelper.WriteLog(sb.ToString(), OperationTypeEnums.导出);
#endregion
}
catch (Exception ex)
{
_br.IsSuccess = false;
throw new Exception(ex.Message);
}
//转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
_br.filepath = _filesavepath + "/" + name + ".xls";
/*******************************/
return(_br);
}
private ISheet createSheet1(HSSFWorkbook workBook, string sheetName)
{
ISheet sheet = workBook.CreateSheet(sheetName);
#region 标题
var cellStyleHeadTitle = workBook.CreateCellStyle();
cellStyleHeadTitle.Alignment = HorizontalAlignment.Center; //居中显示
cellStyleHeadTitle.VerticalAlignment = VerticalAlignment.Top; //垂直居中
ExcelHeadAttribute eha = new ExcelHeadAttribute()
{
rowIndex = 0,
firstRow = 0,
lastRow = 0,
firstCol = 0,
lastCol = 5,
fontColor = 8,
fontSize = 16,
HeightInPoints = 50,
name = "村级防汛防台工作组"
};
CreateHead(workBook, sheet, cellStyleHeadTitle, eha);
#endregion
#region 提醒
var cellStyleHeadNotice = workBook.CreateCellStyle();
cellStyleHeadNotice.Alignment = HorizontalAlignment.Left; //居中显示
cellStyleHeadNotice.VerticalAlignment = VerticalAlignment.Top; //垂直居中
string noitce = "注意： ";
noitce += "1、行政村请在“行政村名录工作表”中复制，否则无法导入。";
noitce += "2、一人兼多岗，也请明确要逐条列出。 ";
noitce += "3、加*列为必填项。 ";
noitce += "4、岗位请下拉选择。 ";
noitce += "5、小灵通(格式:区号\" - \"号码)。";
ExcelHeadAttribute ehanotice = new ExcelHeadAttribute()
{
rowIndex = 1,
firstRow = 1,
lastRow = 1,
firstCol = 0,
lastCol = 5,
fontColor = 10,
fontSize = 14,
HeightInPoints = 40,
name = noitce
};
CreateHead(workBook, sheet, cellStyleHeadNotice, ehanotice);
#endregion
/*********添加表头 s********/
IRow RowBody = sheet.CreateRow(2);
List <string> listtitle = new List <string>();
listtitle.Add("*行政村"); listtitle.Add("*岗位(仅限下拉内容)"); listtitle.Add("*责任人姓名");
listtitle.Add("责任人职务"); listtitle.Add("*责任人手机"); listtitle.Add("备注");
for (int iColumnIndex = 0; iColumnIndex < listtitle.Count(); iColumnIndex++)
{
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
//if(iColumnIndex >=2 && iColumnIndex <= 4)
//{
// sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 4));
// RowBody.CreateCell(iColumnIndex).SetCellValue("责任人");
// RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString());
//}
//else
//{
// sheet.AddMergedRegion(new CellRangeAddress(1, 2, iColumnIndex, iColumnIndex));
// RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString());
//}
RowBody.CreateCell(iColumnIndex).SetCellValue(listtitle[iColumnIndex].ToString());
RowBody.Cells[iColumnIndex].Row.HeightInPoints = 20;
//单元格样式
ICellStyle cellStyle = workBook.CreateCellStyle();
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Top;
//是否换行
cellStyle.WrapText = true;
RowBody.Cells[iColumnIndex].CellStyle = cellStyle;
//字体大小
IFont cellfont = workBook.CreateFont();
cellfont.FontHeightInPoints = 14;
cellStyle.SetFont(cellfont);
sheet.SetColumnWidth(iColumnIndex, 25 * 256);
}
/*********添加表头 e********/
//设置下拉框
setSheet2(workBook, sheet);
return(sheet);
}
void ListViewRow_SelctClick(object sender, EventArgs e)
{
ListView lst = sender as ListView;
//当前选择行的序号
if (lst.SelectedItems.Count == 0)
{//点击之后会两次触发，一次是选中，还有一次是释放选中
return;
}
ListViewItem item = lst.FocusedItem;
//当前选择项的行索引
int index = item.Index;
ECompareTarget area = (ECompareTarget)lst.Tag;
rtbNote.Text += string.Format("Dirction:【{0}】 Row Index :【{1}】\r\n", area.ToString(), index);
ExcelHeadAttribute head = item.Tag as ExcelHeadAttribute;
CompareData compare = null;
bool isInsert = false;
int dataRowIndex = 0;
if (leftHead.Count == rightHead.Count ||
(leftHead.Count > rightHead.Count && area == ECompareTarget.Left) ||
(leftHead.Count < rightHead.Count && area == ECompareTarget.Right))
{ //什么情况是进行添加操作呢？
/*
* 1.左右两侧都已近补充完整
* 2.等待添加的数据来自现补充列较多的
*/
compare = new CompareData();
isInsert = true;
}
else if (area == ECompareTarget.Left)
{//此时进行的是单方补充
//当前是数据补充到缺少项中
dataRowIndex = leftHead.Count;
compare = lstCompare.Items[dataRowIndex].Tag as CompareData;
}
else
{
dataRowIndex = rightHead.Count;
compare = lstCompare.Items[dataRowIndex].Tag as CompareData;
}
switch (area)
{
case ECompareTarget.Left:
compare.OriginHeadName = head.ColumnName;
compare.OriginHeadIndex = head.ColumnIndex;
leftHead.Add(true);
break;
case ECompareTarget.Right:
compare.NewHeadName = head.ColumnName;
compare.NewHeadIndex = head.ColumnIndex;
rightHead.Add(true);
break;
}
//添加到中间区域时进行判断【组合成一项完整的匹配列】
//【左右都存在内容才是一组完整数据】
if (isInsert)
{
lstCompare.InsertRow(compare);
}
else
{//还需要对于UI进行改动
ListViewItem vc = lstCompare.Items[dataRowIndex];
//界面重绘
switch (area)
{
case ECompareTarget.Left:
foreach (var column in leftExcelData)
{
vc.SubItems[column.Key].Text = compare.GetPropertyValue(column.Key);
}
break;
case ECompareTarget.Right:
foreach (var column in rightExcelData)
{
vc.SubItems[column.Key].Text = compare.GetPropertyValue(column.Key);
}
break;
}
}
lstCompare.Refresh();
lst.Items.RemoveAt(index);
}
