/// <summary>
/// 设置四边边框样式
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="borderStyle"></param>
/// <param name="wb"></param>
public static void SetCellBorderAll(int row, int col, NPOI.SS.UserModel.BorderStyle borderStyle, ref HSSFWorkbook wb)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
cellStyle.BorderBottom = borderStyle;
cellStyle.BorderTop = borderStyle;
cellStyle.BorderLeft = borderStyle;
cellStyle.BorderRight = borderStyle;
t_cell.CellStyle = cellStyle;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
cellStyle.BorderBottom = borderStyle;
cellStyle.BorderTop = borderStyle;
cellStyle.BorderLeft = borderStyle;
cellStyle.BorderRight = borderStyle;
t_cell.CellStyle = cellStyle;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
cellStyle.BorderBottom = borderStyle;
cellStyle.BorderTop = borderStyle;
cellStyle.BorderLeft = borderStyle;
cellStyle.BorderRight = borderStyle;
t_cell.CellStyle = cellStyle;
}
}
}
public static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary <Int32, HSSFCellStyle> styleMap, Dictionary <short, short> paletteMap, Boolean keepFormulas)
{
if (styleMap != null)
{
if (oldCell.CellStyle != null)
{
if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook)
{
newCell.CellStyle = oldCell.CellStyle;
}
else
{
int styleHashCode = oldCell.CellStyle.GetHashCode();
if (styleMap.ContainsKey(styleHashCode))
{
newCell.CellStyle = styleMap[styleHashCode];
}
else
{
HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually
newCell.CellStyle = newCellStyle;
//Clone of cell style always clones the font. This makes my life easier
IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook);
if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color))
{
theFont.Color = paletteMap[theFont.Color]; //Remap font color
}
styleMap.Add(styleHashCode, newCellStyle);
}
}
}
else
{
newCell.CellStyle = null;
}
}
switch (oldCell.CellType)
{
case CellType.String:
HSSFRichTextString rts = oldCell.RichStringCellValue as HSSFRichTextString;
newCell.SetCellValue(rts);
if (rts != null)
{
for (int j = 0; j < rts.NumFormattingRuns; j++)
{
short fontIndex = rts.GetFontOfFormattingRun(j);
int startIndex = rts.GetIndexOfFormattingRun(j);
int endIndex = 0;
if (j + 1 == rts.NumFormattingRuns)
{
endIndex = rts.Length;
}
else
{
endIndex = rts.GetIndexOfFormattingRun(j + 1);
}
FontRecord fr = newCell.BoundWorkbook.CreateNewFont();
fr.CloneStyleFrom(oldCell.BoundWorkbook.GetFontRecordAt(fontIndex));
HSSFFont font = new HSSFFont((short)(newCell.BoundWorkbook.GetFontIndex(fr)), fr);
newCell.RichStringCellValue.ApplyFont(startIndex, endIndex, font);
}
}
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
if (keepFormulas)
{
newCell.SetCellType(CellType.Formula);
newCell.CellFormula = oldCell.CellFormula;
}
else
{
try
{
newCell.SetCellType(CellType.Numeric);
newCell.SetCellValue(oldCell.NumericCellValue);
}
catch (Exception ex)
{
newCell.SetCellType(CellType.String);
newCell.SetCellValue(oldCell.ToString());
}
}
break;
default:
break;
}
}
private void execute()
{
try
{
HSSFWorkbook inputBook = new HSSFWorkbook(new FileStream(INPUT_URL, FileMode.Open)); //input不可外流，放在GitHub外層
HSSFSheet inputSheet = (HSSFSheet)inputBook.GetSheetAt(0); //目前只取第一分頁sheet0
//提早取得名稱
//取得資料
int iNumRow = inputSheet.LastRowNum;
for (int i = 0; i < iNumRow; ++i)
{
//保留標題列略過
if (i < this.keepRow)
{
continue;
}
HSSFWorkbook outputBook = new HSSFWorkbook();
HSSFSheet outputSheet = (HSSFSheet)outputBook.CreateSheet("sheet1"); // 在 Excel 工作簿中建立工作表，名稱為 Sheet1
HSSFRow iCurRow = (HSSFRow)inputSheet.GetRow(i); //取得第N列資料
//標題列數(注意:最後一條保留列為資料標題)
int titleCellCount = 0;
int ci = 0;
//先寫入保留標題列---------------------------------------------------------------------------------------
for (int ki = 0; ki < this.keepRow; ++ki)
{
//查詢保留列
HSSFRow iTitleRow = (HSSFRow)inputSheet.GetRow(ki);
//保留列有多少欄?
titleCellCount = iTitleRow.LastCellNum;
//建立保留列
HSSFRow oTitleRow = (HSSFRow)outputSheet.CreateRow(ki);
//設定同等列高
oTitleRow.Height = iTitleRow.Height;
for (ci = 0; ci < titleCellCount; ++ci)
{
//input.J copy to output.J
ICell inTItleCell = iTitleRow.GetCell(ci);
if (inTItleCell != null)
{
ICell oTitleCell = oTitleRow.CreateCell(ci);
oTitleCell.SetCellValue(inTItleCell.StringCellValue);
//複製欄格式
HSSFCellStyle newTitleCellStyle = (HSSFCellStyle)outputBook.CreateCellStyle();
newTitleCellStyle.CloneStyleFrom(inTItleCell.CellStyle);
oTitleCell.CellStyle = newTitleCellStyle;
}
}
}
//寫入資料---------------------------------------------------------------------------------------
//建立員工資料列
string name = "temp";
string apartment = "temp";
HSSFRow oDataRow = (HSSFRow)outputSheet.CreateRow(this.keepRow);
oDataRow.Height = iCurRow.Height;
for (ci = 0; ci < titleCellCount; ++ci)
{
ICell inDataCell = iCurRow.GetCell(ci);
if (inDataCell == null)
{
continue;
}
//取得名字
if (ci == this.nameCell)
{
name = inDataCell.StringCellValue;
}
//取得部門
if (ci == this.nameCell + 1)
{
apartment = inDataCell.StringCellValue;
}
ICell oDataCell = oDataRow.CreateCell(ci);
if (this.config.needToSkipCol(name, ci + 1))
{
outputSheet.SetColumnHidden(ci, true);
}
//到職日特別處理
if (ci == 0)
{
var value = inDataCell.DateCellValue;
oDataCell.SetCellValue(value.ToShortDateString());//轉換為2014/12/19
}
//數字
else if (inDataCell.CellType == NPOI.SS.UserModel.CellType.Numeric || inDataCell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
var value = inDataCell.NumericCellValue;
oDataCell.SetCellValue(value);
}
else
{
oDataCell.SetCellValue(inDataCell.StringCellValue);
}
HSSFCellStyle newCellStyle = (HSSFCellStyle)outputBook.CreateCellStyle();
newCellStyle.CloneStyleFrom(inDataCell.CellStyle);
oDataCell.CellStyle = newCellStyle;
outputSheet.AutoSizeColumn(ci);
//oDataCell.CellStyle.WrapText = true;
}
//刪除-------------------------------------------------------------------
for (int ri = 0; ri <= outputSheet.LastRowNum; ++ri)
{
HSSFRow eachRow = (HSSFRow)outputSheet.GetRow(ri);//取得第N列資料
titleCellCount = eachRow.LastCellNum;
for (ci = titleCellCount - 1; ci > -1; --ci)
{
if (this.config.needToSkipCol(name, ci + 1))
{
ICell eachCell = eachRow.GetCell(ci);
if (eachCell == null)
{
eachCell = eachRow.CreateCell(ci);
}
eachRow.RemoveCell(eachCell);
}
}
}
//output不可外流，放在GitHub外層
string fileName = OUTPUT_URL;
fileName = fileName.Replace("#1", apartment);
fileName = fileName.Replace("#2", name);
Directory.CreateDirectory(Path.GetDirectoryName(fileName));
FileStream outputStream = new FileStream(fileName, FileMode.Create);
outputBook.Write(outputStream);
outputBook.WriteProtectWorkbook("123", "456");
outputStream.Close();
}
MessageBox.Show("薪資拆分成功!");
System.Windows.Forms.Application.Exit();//自動關閉APP
}
catch (Exception error)
{
MessageBox.Show("異常錯誤 " + error.Message);
}
}
private FileContentResult SendToExcel2(FormCollection coll)
{
DateTime firstDay = DateTime.Parse(coll["txtDateFrom"]);
DateTime lastDay = DateTime.Parse(coll["txtDateTo"]);
string User_CAI = coll["ddlUsers"];
ReportPackage rp = timedb.GetReportByUser(User_CAI, firstDay, lastDay);
// FileStream fs = new FileStream(Server.MapPath("~/Content/Template.xls"), FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
HSSFWorkbook wb = new HSSFWorkbook();
// Getting the worksheet by its name...
HSSFSheet sheet = wb.CreateSheet(User_CAI + "_" + coll["txtDateFrom"] + "_" + coll["txtDateTo"]);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 30 * 256);
HSSFFont font1 = wb.CreateFont();
font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font1.Color = HSSFColor.BLACK.index;
font1.FontHeightInPoints = 10;
HSSFFont font2 = wb.CreateFont();
font2.Color = HSSFColor.BLACK.index;
font2.FontHeightInPoints = 8;
HSSFFont sacofont = wb.CreateFont();
sacofont.Color = HSSFColor.WHITE.index;
sacofont.FontHeightInPoints = 8;
HSSFFont font2Bold = wb.CreateFont();
font2Bold = font2;
font2Bold.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
HSSFFont font_Red_Bold = wb.CreateFont();
font_Red_Bold.Color = HSSFColor.RED.index;
font_Red_Bold.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font_Red_Bold.FontHeightInPoints = 10;
HSSFCellStyle globalstyle = wb.CreateCellStyle();
globalstyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
globalstyle.BottomBorderColor = HSSFColor.BLACK.index;
globalstyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
globalstyle.LeftBorderColor = HSSFColor.BLACK.index;
globalstyle.BorderRight = HSSFCellStyle.BORDER_THIN;
globalstyle.RightBorderColor = HSSFColor.BLACK.index;
globalstyle.BorderTop = HSSFCellStyle.BORDER_THIN;
globalstyle.TopBorderColor = HSSFColor.BLACK.index;
HSSFCellStyle headerstyle = wb.CreateCellStyle();
headerstyle.CloneStyleFrom(globalstyle);
headerstyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
headerstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headerstyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
headerstyle.SetFont(font1);
HSSFCellStyle dayStyle = wb.CreateCellStyle();
dayStyle.CloneStyleFrom(headerstyle);
dayStyle.SetFont(font1);
HSSFCellStyle datastyle = wb.CreateCellStyle();
datastyle.CloneStyleFrom(globalstyle);
datastyle.SetFont(font2);
HSSFCellStyle footerStyle = wb.CreateCellStyle();
footerStyle.CloneStyleFrom(headerstyle);
footerStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
footerStyle.SetFont(font2);
HSSFCellStyle ErrorStyle = wb.CreateCellStyle();
ErrorStyle.CloneStyleFrom(headerstyle);
ErrorStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
ErrorStyle.SetFont(font_Red_Bold);
HSSFCellStyle SacofooterStyle = wb.CreateCellStyle();
SacofooterStyle.CloneStyleFrom(globalstyle);
SacofooterStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;
SacofooterStyle.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
SacofooterStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
SacofooterStyle.SetFont(sacofont);
//Header
HSSFRow row = sheet.CreateRow(0);
row.CreateCell(0).CellStyle = headerstyle;
row.GetCell(0).SetCellValue("Status");
row.CreateCell(1).CellStyle = headerstyle;
row.GetCell(1).SetCellValue("Prj No.");
row.CreateCell(2).CellStyle = headerstyle;
row.GetCell(2).SetCellValue("SAP");
row.CreateCell(3).CellStyle = headerstyle;
row.GetCell(3).SetCellValue("Cost Code");
row.CreateCell(4).CellStyle = headerstyle;
row.GetCell(4).SetCellValue("Project Name");
int colcount = 5;
for (DateTime dt = rp.DateFrom; dt < rp.DateTo.AddDays(1); dt = dt.AddDays(1))
{
row.CreateCell(colcount).CellStyle = dayStyle;
row.GetCell(colcount).CellStyle.WrapText = true;
row.GetCell(colcount).SetCellValue(new DateTime(dt.Year, dt.Month, dt.Day).ToString("ddd") + "\n" + new DateTime(dt.Year, dt.Month, dt.Day).ToString("dd") + "\n" + new DateTime(dt.Year, dt.Month, dt.Day).ToString("MMM"));
colcount++;
}
row.CreateCell(colcount).CellStyle = headerstyle;
row.GetCell(colcount).SetCellValue("Total");
//Details
int rowcount = 1;
foreach (var ts in rp.TimeSheets)
{
sheet.CreateRow(rowcount);
sheet.GetRow(rowcount).CreateCell(0).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(0).SetCellValue(ts.Status);
sheet.GetRow(rowcount).CreateCell(1).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(1).SetCellValue(ts.Project_Number.ToString());
sheet.GetRow(rowcount).CreateCell(2).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(2).SetCellValue(ts.SAP);
sheet.GetRow(rowcount).CreateCell(3).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(3).SetCellValue(ts.WBS);
sheet.GetRow(rowcount).CreateCell(4).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(4).CellStyle.WrapText = true;
sheet.GetRow(rowcount).GetCell(4).SetCellValue(ts.Project_Name);
colcount = 5;
foreach (var d in ts.Days)
{
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = datastyle;
if (d.HoursWorked.HasValue)
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue((double)d.HoursWorked.Value);
}
else
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty);
}
colcount++;
}
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = datastyle;
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue((double)ts.Total);
rowcount++;
}
//Footer
//Insert blank spaces
sheet.CreateRow(rowcount);
sheet.GetRow(rowcount).CreateCell(0).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(1).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(2).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(3).CellStyle = footerStyle;
sheet.GetRow(rowcount).CreateCell(4).CellStyle = footerStyle;
colcount = 5; //Start at column 5 inserting values
int loopindex = 0;
foreach (var d in rp.DayTotals)
{
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = footerStyle;
if (d.HoursWorked.HasValue && (decimal)d.HoursWorked.Value > 0)
{
loopindex = d.Number - 1;
if (rp.DayTotals[loopindex].HoursWorked.Value > rp.DaySacoTotals[loopindex].HoursWorked.Value)
{
sheet.GetRow(rowcount).GetCell(colcount).CellStyle = ErrorStyle;
}
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)d.HoursWorked.Value).ToString("#0.00"));
}
else
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty);
}
colcount++;
}
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = footerStyle;
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)rp.GrandTotal).ToString("#0.00"));
rowcount++;
//SACO Footer
//Insert blank spaces
sheet.CreateRow(rowcount);
sheet.GetRow(rowcount).CreateCell(0).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).GetCell(0).SetCellValue("Saco");
sheet.GetRow(rowcount).CreateCell(1).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).CreateCell(2).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).CreateCell(3).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).CreateCell(4).CellStyle = SacofooterStyle;
colcount = 5; //Start at column 5 inserting values
foreach (var d in rp.DaySacoTotals)
{
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = SacofooterStyle;
if (d.HoursWorked.HasValue && (decimal)d.HoursWorked.Value > 0)
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)d.HoursWorked.Value).ToString("#0.00"));
}
else
{
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(string.Empty);
}
colcount++;
}
sheet.GetRow(rowcount).CreateCell(colcount).CellStyle = SacofooterStyle;
sheet.GetRow(rowcount).GetCell(colcount).SetCellValue(((decimal)rp.DaySacoTotals.Sum(t => t.HoursWorked)).ToString("#0.00"));
MemoryStream ms = new MemoryStream();
// Writing the workbook content to the FileStream...
wb.Write(ms);
// Sending the server processed data back to the user computer...
return(File(ms.ToArray(), "application/vnd.ms-excel", coll["ddlUsers"] + "_" + coll["txtDateFrom"] + "_" + coll["txtDateTo"] + "_TimeSheetReport.xls"));
}
private void RenderExcel(DataTable tabTable, DataTable colTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFFont myFont = (HSSFFont)workbook.CreateFont();
myFont.FontName = "Times New Roman";
myFont.FontHeightInPoints = 12;
HSSFCellStyle itemStyle = (HSSFCellStyle)workbook.CreateCellStyle();
itemStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
itemStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
itemStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
itemStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
itemStyle.SetFont(myFont);
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.CloneStyleFrom(itemStyle);
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
for (int i = 0; i < tabTable.Rows.Count; i++)
{
ISheet newSheet = workbook.CreateSheet(tabTable.Rows[i]["table_name"].ToString());
IRow xlsRow = null;
ICell xlsCell = null;
xlsRow = newSheet.CreateRow(0);
xlsCell = xlsRow.CreateCell(0);
xlsCell.SetCellValue("欄位名稱");
xlsCell.CellStyle = headerStyle;
xlsCell = xlsRow.CreateCell(1);
xlsCell.SetCellValue("格式");
xlsCell.CellStyle = headerStyle;
xlsCell = xlsRow.CreateCell(2);
xlsCell.SetCellValue("有效值");
xlsCell.CellStyle = headerStyle;
xlsCell = xlsRow.CreateCell(3);
xlsCell.SetCellValue("欄位說明");
xlsCell.CellStyle = headerStyle;
DataRow[] drs = colTable.Select("table_name = '" + tabTable.Rows[i]["table_name"].ToString() + "'", "ordinal_position Asc");
for (int j = 0; j < drs.Length; j++)
{
xlsRow = newSheet.CreateRow(j + 1);
xlsCell = xlsRow.CreateCell(0);
xlsCell.SetCellValue(drs[j]["column_name"].ToString());
xlsCell.CellStyle = itemStyle;
xlsCell = xlsRow.CreateCell(1);
xlsCell.SetCellValue(drs[j]["DataType"].ToString());
xlsCell.CellStyle = itemStyle;
xlsCell = xlsRow.CreateCell(2);
xlsCell.SetCellValue(drs[j]["NullAble"].ToString());
xlsCell.CellStyle = itemStyle;
xlsCell = xlsRow.CreateCell(3);
xlsCell.SetCellValue(drs[j]["Description"].ToString());
xlsCell.CellStyle = itemStyle;
}
newSheet.AutoSizeColumn(0);
newSheet.AutoSizeColumn(1);
newSheet.AutoSizeColumn(2);
newSheet.AutoSizeColumn(3);
}
using (FileStream file = new FileStream(GetFileFullPath(".xls"), FileMode.Create))
{
workbook.Write(file);
file.Close();
}
}
/// <summary>
/// 设置单元格是否居中对齐，默认居左和底部
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="vertical">是否垂直居中</param>
/// <param name="horizontal">是否水平居中</param>
/// <param name="wb"></param>
public static void SetCellAlignmentCenter(int row, int col, bool vertical, bool horizontal, ref HSSFWorkbook wb)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
}
}
/// <summary>
/// 设置单元格的格式，如日期、小数
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="format"></param>
/// <param name="wb"></param>
public static void SetCellFormat(int row, int col, CellFormat format, ref HSSFWorkbook wb)
{
HSSFCellStyle Style = (HSSFCellStyle)wb.CreateCellStyle();
IDataFormat dataFormat = wb.CreateDataFormat();
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
}
}
void ExportToExcelDeliveryPlanNumber(string FileName, List <DeilveryPlanItem> dataSource)
{
if (dataSource.IsNotEmptySet())
{
var v1 = from a in dataSource
where a.DutySendDate.Date.Subtract(DateTime.Now.Date).TotalDays >= 0
orderby a.Type
group a by a.TypeName into g
select g.Key;
var v2 = from a in dataSource
where a.DutySendDate.Date.Subtract(DateTime.Now.Date).TotalDays >= 0
group a by a.DutySendDate.Date into g
orderby g.Key
select g.Key;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet st = (HSSFSheet)wb.CreateSheet();
st.DefaultColumnWidth = 10;
HSSFCellStyle cellNomalStyle = (HSSFCellStyle)wb.CreateCellStyle(), cellIntStyle = (HSSFCellStyle)wb.CreateCellStyle(), cellDateStyle = (HSSFCellStyle)wb.CreateCellStyle();
//HSSFPalette palette = wb.GetCustomPalette();
cellNomalStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellNomalStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellNomalStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellNomalStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellNomalStyle.VerticalAlignment = VerticalAlignment.Center;
cellNomalStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.General;
cellNomalStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellNomalStyle.WrapText = true;
cellIntStyle.CloneStyleFrom(cellNomalStyle);
cellIntStyle.DataFormat = wb.CreateDataFormat().GetFormat(@"#,###,###");
cellDateStyle.CloneStyleFrom(cellNomalStyle);
cellDateStyle.DataFormat = wb.CreateDataFormat().GetFormat(@"yyyy/MM/dd");
HSSFFont blodfont1 = (HSSFFont)wb.CreateFont();
blodfont1.Boldweight = (short)FontBoldWeight.Bold;
blodfont1.Color = HSSFColor.Turquoise.Index;
HSSFFont blodfont2 = (HSSFFont)wb.CreateFont();
blodfont2.Boldweight = (short)FontBoldWeight.Bold;
blodfont2.Color = HSSFColor.DarkRed.Index;
HSSFRow rGridCaption = (HSSFRow)st.CreateRow(0);
ICell xCellCaption1 = rGridCaption.CreateCell(0);
xCellCaption1.SetCellValue(LCStr("时间："));
xCellCaption1.CellStyle = cellNomalStyle;
int u = 0; int r = 0;
foreach (var item in v1)
{
r++;
HSSFRow rGridRow = (HSSFRow)st.CreateRow(r);
ICell xCellCaption = rGridRow.CreateCell(0);
xCellCaption.SetCellValue(item);
xCellCaption.CellStyle = cellNomalStyle;
u = 0;
foreach (var v2Item in v2)
{
var vv = from a in dataSource
where a.DutySendDate.Date.Subtract(DateTime.Now.Date).TotalDays >= 0 &&
a.TypeName == item && a.DutySendDate.Date == v2Item.Date
select a.PNumb;
double vx = vv.Sum();
u++;
ICell xCell = rGridRow.CreateCell(u);
if (vx == 0)
{
xCell.SetCellValue("");
}
else
{
xCell.SetCellValue(string.Format("{0:#,##0.0}K", vx / 1000.00));
}
xCell.CellStyle = cellNomalStyle;
}
ICell xCellSum = rGridRow.CreateCell(u + 1);
var vvr = from a in dataSource
where a.DutySendDate.Date.Subtract(DateTime.Now.Date).TotalDays >= 0 && a.TypeName == item
select a.PNumb;
double vxr = vvr.Sum();
if (vxr == 0)
{
xCellSum.SetCellValue("");
}
else
{
xCellSum.SetCellValue(string.Format("{0:#,##0.0}K", vxr / 1000.00));
}
xCellSum.CellStyle = cellNomalStyle;
}
HSSFRow rSumRow = (HSSFRow)st.CreateRow(r + 1);
u = 0;
foreach (var v2Item in v2)
{
u++;
ICell xCellCaption = rGridCaption.CreateCell(u);
xCellCaption.SetCellValue(v2Item);
xCellCaption.CellStyle = cellDateStyle;
var vv = from a in dataSource
where a.DutySendDate.Date.Subtract(DateTime.Now.Date).TotalDays >= 0 && a.DutySendDate.Date == v2Item.Date
select a.PNumb;
double vx = vv.Sum();
ICell xCelSum = rSumRow.CreateCell(u);
if (vx == 0)
{
xCelSum.SetCellValue("");
}
else
{
xCelSum.SetCellValue(string.Format("{0:#,##0.0}K", vx / 1000.00));
}
xCelSum.CellStyle = cellDateStyle;
}
ICell xCellCaption2 = rGridCaption.CreateCell(u + 1);
xCellCaption2.SetCellValue(LCStr("合计"));
xCellCaption2.CellStyle = cellDateStyle;
ICell xCellCaption3 = rSumRow.CreateCell(0);
xCellCaption3.SetCellValue(LCStr("合计"));
xCellCaption3.CellStyle = cellDateStyle;
var vvlast = from a in dataSource
where a.DutySendDate.Date.Subtract(DateTime.Now.Date).TotalDays >= 0
select a.PNumb;
ICell xCellCaption4 = rSumRow.CreateCell(u + 1);
double vxLast = vvlast.Sum();
if (vxLast == 0)
{
xCellCaption4.SetCellValue("");
}
else
{
xCellCaption4.SetCellValue(string.Format("{0:#,##0.0}K", vxLast / 1000.00));
}
xCellCaption4.CellStyle = cellDateStyle;
string filename = FileName;
if (File.Exists(filename))
{
File.Delete(filename);
}
using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
}
private void CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
{
// Get the source / new row
HSSFRow newRow = (HSSFRow)worksheet.GetRow(destinationRowNum);
HSSFRow sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
}
else
{
newRow = (HSSFRow)worksheet.CreateRow(destinationRowNum);
}
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
HSSFCell oldCell = (HSSFCell)sourceRow.GetCell(i);
HSSFCell newCell = (HSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
HSSFCellStyle newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);;
newCell.CellStyle = newCellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
//// Set the cell data value
//switch (oldCell.CellType)
//{
// case HSSFCellType.BLANK:
// newCell.SetCellValue(oldCell.StringCellValue);
// break;
// case HSSFCellType.BOOLEAN:
// newCell.SetCellValue(oldCell.BooleanCellValue);
// break;
// case HSSFCellType.ERROR:
// newCell.SetCellErrorValue(oldCell.ErrorCellValue);
// break;
// case HSSFCellType.FORMULA:
// newCell.SetCellFormula(oldCell.CellFormula);
// break;
// case HSSFCellType.NUMERIC:
// newCell.SetCellValue(oldCell.NumericCellValue);
// break;
// case HSSFCellType.STRING:
// newCell.SetCellValue(oldCell.RichStringCellValue);
// break;
// case HSSFCellType.Unknown:
// newCell.SetCellValue(oldCell.StringCellValue);
// break;
//}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < worksheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.FirstRow -
cellRangeAddress.LastRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
}
}
public static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary <Int32, HSSFCellStyle> styleMap, Dictionary <short, short> paletteMap, Boolean keepFormulas)
{
if (styleMap != null)
{
if (oldCell.CellStyle != null)
{
if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook)
{
newCell.CellStyle = oldCell.CellStyle;
}
else
{
int styleHashCode = oldCell.CellStyle.GetHashCode();
if (styleMap.ContainsKey(styleHashCode))
{
newCell.CellStyle = styleMap[styleHashCode];
}
else
{
HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually
newCell.CellStyle = newCellStyle;
//Clone of cell style always clones the font. This makes my life easier
IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook);
if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color))
{
theFont.Color = paletteMap[theFont.Color]; //Remap font color
}
styleMap.Add(styleHashCode, newCellStyle);
}
}
}
else
{
newCell.CellStyle = null;
}
}
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
if (keepFormulas)
{
newCell.SetCellType(CellType.Formula);
newCell.CellFormula = oldCell.CellFormula;
}
else
{
try
{
newCell.SetCellType(CellType.Numeric);
newCell.SetCellValue(oldCell.NumericCellValue);
}
catch (Exception ex)
{
newCell.SetCellType(CellType.String);
newCell.SetCellValue(oldCell.ToString());
}
}
break;
default:
break;
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText = "")
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 全局属性
//int maxRowIndex = 65535;
int lastRowNum = 0;
int colMaxWidth = 255;//超过255会报错
//表格统一样式
HSSFCellStyle allCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
allCellStyle.BorderTop = CellBorderType.THIN;
allCellStyle.BorderBottom = CellBorderType.THIN;
allCellStyle.BorderLeft = CellBorderType.THIN;
allCellStyle.BorderRight = CellBorderType.THIN;
//时间样式
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
dateStyle.CloneStyleFrom(allCellStyle);
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//表头、列头样式
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.CloneStyleFrom(allCellStyle);
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.Boldweight = 700;
font.FontHeightInPoints = 15;
headStyle.SetFont(font);
// 列头样式
HSSFCellStyle columnHeadStyle = (HSSFCellStyle)workbook.CreateCellStyle();
columnHeadStyle.CloneStyleFrom(allCellStyle);
columnHeadStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font2 = (HSSFFont)workbook.CreateFont();
font2.Boldweight = 700;
columnHeadStyle.SetFont(font2);
#endregion
#region 新建表，设置表头、整体样式
//表头
if (strHeaderText != "")
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(lastRowNum);
lastRowNum++;
headerRow.HeightInPoints = 20;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
headerRow.GetCell(0).CellStyle = headStyle;
headerRow.GetCell(0).SetCellValue(strHeaderText);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp > colMaxWidth ? colMaxWidth : intTemp;
}
}
}
//设置列宽、填充列头
HSSFRow columRow = (HSSFRow)sheet.CreateRow(lastRowNum);
lastRowNum++;
foreach (DataColumn column in dtSource.Columns)
{
//songliangliang 2014.7.1
//原 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
HSSFCell newCell = (HSSFCell)columRow.CreateCell(column.Ordinal);
newCell.CellStyle = columnHeadStyle;
ConfigCell(ref newCell, column.ColumnName);
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal]) * 256);
}
#endregion
#region 填充表格内容
//int rowIndex = sheet.LastRowNum;
foreach (DataRow row in dtSource.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(lastRowNum);
lastRowNum++;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
ConfigCell(ref newCell, row[column], dateStyle);
newCell.CellStyle = allCellStyle;
}
//rowIndex++;
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public static HSSFWorkbook NoTemplateExport(ColumnDefineCollection columnDefines, DataTable table)
{
//创建工作簿
HSSFWorkbook book = new HSSFWorkbook();
//增加标题Style
HSSFCellStyle styleHeader = (HSSFCellStyle)book.CreateCellStyle();
styleHeader.FillForegroundColor = HSSFColor.BLUE.index;
styleHeader.FillPattern = FillPatternType.SOLID_FOREGROUND;
//设置Font
HSSFFont fontHeader = (HSSFFont)book.CreateFont();
fontHeader.FontName = "Tahoma";
fontHeader.FontHeight = 200;
fontHeader.Color = HSSFColor.WHITE.index;
styleHeader.SetFont(fontHeader);
//设置缺省Style
HSSFCellStyle styleDefault = (HSSFCellStyle)book.CreateCellStyle();
HSSFFont fontDefault = (HSSFFont)book.CreateFont();
styleDefault.SetFont(fontDefault);
fontDefault.FontName = "Tahoma";
fontDefault.FontHeight = 200;
//创建Sheet
HSSFRow row;
HSSFCell cell;
HSSFSheet sheet = (HSSFSheet)book.CreateSheet("Sheet1");
//创建标题列
row = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < columnDefines.Count; i++)
{
ColumnDefine columnDefine = columnDefines[i];
//创建cell
cell = (HSSFCell)row.CreateCell(i, CellType.STRING);
//应用style
HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle();
style.CloneStyleFrom(styleHeader);
style.Alignment = columnDefine.Align;
cell.CellStyle = style;
//设置值
cell.SetCellValue(columnDefine.Text);
sheet.SetColumnWidth(i, YZExcelHelper.PixelToExcel(columnDefine.Width));
}
foreach (DataRow dataRow in table.Rows)
{
row = (HSSFRow)sheet.CreateRow(sheet.LastRowNum + 1);
for (int i = 0; i < columnDefines.Count; i++)
{
ColumnDefine columnDefine = columnDefines[i];
object objValue = dataRow[columnDefine.ColumnName];
//SQL Server数据库中monery4位小数点处理
if (objValue is decimal)
{
objValue = (decimal)Decimal.ToDouble((decimal)objValue);
}
//创建cell
cell = (HSSFCell)row.CreateCell(i, CellType.STRING);
//应用style
if (columnDefine.Style == null)
{
columnDefine.Style = (HSSFCellStyle)book.CreateCellStyle();
columnDefine.Style.CloneStyleFrom(styleDefault);
columnDefine.Style.Alignment = columnDefine.Align;
}
cell.CellStyle = columnDefine.Style;
//设置值
TypeCode typeCode = Type.GetTypeCode(objValue == null ? typeof(string) : objValue.GetType());
switch (typeCode)
{
case TypeCode.Boolean:
cell.SetCellValue(Convert.ToBoolean(objValue));
break;
case TypeCode.DateTime:
DateTime date = (DateTime)objValue;
string strValue = date == DateTime.MinValue ? "" : YZStringHelper.DateToStringL(date);
cell.SetCellValue(strValue);
break;
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.Single:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
case TypeCode.SByte:
case TypeCode.Byte:
cell.SetCellValue(Convert.ToDouble(objValue));
break;
default:
cell.SetCellValue(Convert.ToString(objValue));
break;
}
}
}
return(book);
}
