public void Test58778()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
ICell cell = wb1.CreateSheet("bug58778").CreateRow(0).CreateCell(0);
cell.SetCellValue(5.25);
ICellStyle style = wb1.CreateCellStyle();
XSSFDataFormat dataFormat = wb1.CreateDataFormat() as XSSFDataFormat;
short poundFmtIdx = 6;
dataFormat.PutFormat(poundFmtIdx, poundFmt);
style.DataFormat = (poundFmtIdx);
cell.CellStyle = style;
// Cell should appear as "<poundsymbol>5"
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutCloseAndReadBack(wb1);
cell = wb2.GetSheet("bug58778").GetRow(0).GetCell(0);
Assert.AreEqual(5.25, cell.NumericCellValue, 0);
style = cell.CellStyle;
Assert.AreEqual(poundFmt, style.GetDataFormatString());
Assert.AreEqual(poundFmtIdx, style.DataFormat);
// manually check the file to make sure the cell is rendered as "<poundsymbol>5"
// Verified with LibreOffice 4.2.8.2 on 2015-12-28
wb2.Close();
wb1.Close();
}
public void OutExcelData()
{
string ExportFileName = "SystemData.xlsx"; string ExportFileTitle = "Data";
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
XSSFWorkbook NpoiWB = new XSSFWorkbook();
XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat();
xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0"));
XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true;
cellStyleFontColor.SetFont(font1);
ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle);
List <string> listColumn = new List <string>()
{
"SystemClass", "systemValue", "SystemTitle", "SystemNotation", "SystemRemark", "SystemStatus"
};
IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40;
for (int i = 0; i < listColumn.Count; i++)
{
ICell xCellT = xRowT.CreateCell(i);
xCellT.SetCellValue(listColumn[i]);
}
List <listSystemDetail> systemDetailList = new List <listSystemDetail>();
systemDetailList = sdModels.reListSystemDetail();
for (int i = 0; i < systemDetailList.Count; i++)
{
listSystemDetail dr = systemDetailList[i];
List <string> listData = new List <string>();
listData.Add(dr.lSystemClass.ToString());
listData.Add(dr.lSystemValue.ToString());
listData.Add(dr.lSystemTitle.ToString());
listData.Add(dr.lSystemNotation.ToString());
listData.Add(dr.lSystemRemark.ToString());
listData.Add(dr.lSystemStatus.ToString());
IRow xRowD = xSheet.CreateRow(i + 1);
xRowD.HeightInPoints = 40;
for (int b = 0; b < listData.Count; b++)
{
ICell xCellData = xRowD.CreateCell(b);
xCellData.SetCellValue(listData[b]);
}
}
MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS);
Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + "");
Response.BinaryWrite(MS.ToArray());
NpoiWB = null; MS.Close(); MS.Dispose();
Response.Flush();
Response.End();
}
/// <summary>
/// 构造方法
/// </summary>
/// <param name="s">文件</param>
public PowerExcel(Stream s)
{
try
{
_workbook = new XSSFWorkbook(s);
_sheet = _workbook.GetSheetAt(0);
_df = new XSSFDataFormat(_workbook.GetStylesSource());
}
catch (Exception) //检测模板异常
{
throw new Exception("模板不正确");
}
}
internal short GetBuiltIndDataFormat(string dataFormat)
{
short result;
switch (this.ExcelVersion)
{
case ExcelVersion.XLS:
result = HSSFDataFormat.GetBuiltinFormat(dataFormat);
break;
case ExcelVersion.XLSX:
result = new XSSFDataFormat(new StylesTable()).GetFormat(dataFormat);
break;
default:
throw new Exception(ErrorMessage.Excel_BadVersion);
}
return(result);
}
/// <summary>
/// 构造方法 强行切换二开文件
/// </summary>
/// <param name="filePath">模板文件路径</param>
public PowerExcel(string filePath)
{
//如果存在二开文件，切换成二开模式
//todo: 这里还应该去获取是否是二开模式，暂时不知道怎么去获取，就先这样判断
var xFileName = filePath.Replace(Path.GetFileName(filePath), "x_" + Path.GetFileName(filePath));
if (File.Exists(xFileName))
{
filePath = xFileName;
}
if (File.Exists(filePath))
{
File.SetAttributes(filePath, FileAttributes.Normal);
}
var fs = new FileStream(filePath, FileMode.Open);
_workbook = new XSSFWorkbook(fs);
_sheet = _workbook.GetSheetAt(0);
_df = new XSSFDataFormat(_workbook.GetStylesSource());
}
/// <summary>
/// 导出期初模板
/// </summary>
/// <param name="dtSource"></param>
/// <param name="exportTemplateFilePath"></param>
/// <param name="fillRow"></param>
/// <param name="replaceCells"></param>
/// <returns></returns>
private static MemoryStream ExportOpeningTemplate(DataTable dtSource, string[] dropDowndtSource, string exportTemplateFilePath, int fillRow, int dropDownFillStartCell, int dropDownFillEndCell)
{
try
{
//打开Excle模板文件
IWorkbook workbook = null;
using (FileStream fileOne = new FileStream(exportTemplateFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fileOne); //获取第一个工作表
}
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); //获取第一个sheet
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper
.CreateExplicitListConstraint(dropDowndtSource);
CellRangeAddressList addressList = new CellRangeAddressList(1, dtSource.Rows.Count, dropDownFillStartCell, dropDownFillEndCell);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
//格式日期
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//格式数字
XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
//单元格样式
ICellStyle style = workbook.CreateCellStyle();
//style.BorderBottom = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderTop = BorderStyle.Thin;
int rowIndex = fillRow;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
//sheet.ShiftRows(rowIndex, sheet.LastRowNum, 1, true, false);
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
Double doubV = 0;
Double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
newCell.CellStyle = decimelStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
newCell.CellStyle = style;
}
#endregion 填充内容
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
catch (Exception ex)
{
throw ex;
}
}
private static MemoryStream ExportExcel2007(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
//格式日期
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//格式数字
XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
// 取得列宽
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;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
if (rowIndex == 1048576 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion 表头及样式
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] > 255 ? 254 : arrColWidth[column.Ordinal] + 1) * 256);
}
//sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
}
rowIndex = 2;
#endregion 列头及样式
rowIndex = 2;
}
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
Double doubV = 0;
Double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
newCell.CellStyle = decimelStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion 填充内容
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
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;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
if (rowIndex == 0)
{
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
rowIndex = 1;
}
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
public static MemoryStream GetDataTableToMemory(DataTable rowData, IEnumerable <string> colNames, bool v)
{
XSSFWorkbook workbook = new XSSFWorkbook();
string TableName = rowData.TableName;
if (string.IsNullOrEmpty(TableName))
{
TableName = "Sheet1";
}
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//dateStyle.FillBackgroundColor = IndexedColors.Blue.Index;
//dateStyle.FillPattern = FillPattern.Bricks;
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//设置样式---
//设置单元格上下左右边框线
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 = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//cellStyle.DataFormat = 194;
//cellStyle.FillForegroundColor = IndexedColors.Pink.Index;
//cellStyle.FillPattern = FillPattern.SolidForeground;
int rowIndex = 0;
XSSFRow eheaderRow = (XSSFRow)sheet.CreateRow(0);
List <string> lcolName = new List <string>();
foreach (var item in colNames)
{
lcolName.Add(item);
}
for (int i = 0; i < lcolName.Count; i++)
{
sheet.SetColumnWidth(i, 14 * 256);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
eheaderRow.CreateCell(i).SetCellValue(lcolName[i]);
eheaderRow.GetCell(i).CellStyle = headStyle;
}
rowIndex = 1;
foreach (DataRow row in rowData.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in rowData.Columns)
{
if (column.ColumnName == "CARTON_GROSS_WEIGTH")
{
string ssw = row[column].ToString();
}
string ss = row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
newCell.CellStyle = cellStyle;
}
#endregion
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream
{
AllowClose = false
};
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
//throw new NotImplementedException();
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream DatagridviewToExcel(DataGridView myDgv, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
try
{
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
columnIndex++;
}
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
columnIndex++;
}
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void OutExcelData()
{
string ExportFileName = "EmpData.xlsx"; string ExportFileTitle = "Data"; Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
XSSFWorkbook NpoiWB = new XSSFWorkbook();
XSSFCellStyle xCellStyle = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFDataFormat NpoiFormat = (XSSFDataFormat)NpoiWB.CreateDataFormat();
xCellStyle.SetDataFormat(NpoiFormat.GetFormat("[DbNum2][$-804]0"));
XSSFCellStyle cellStyleFontColor = (XSSFCellStyle)NpoiWB.CreateCellStyle();
XSSFFont font1 = (XSSFFont)NpoiWB.CreateFont(); font1.Color = (short)10; font1.IsBold = true;
cellStyleFontColor.SetFont(font1);
/// 進行產生Excel檔案流程
ISheet xSheet = NpoiWB.CreateSheet(ExportFileTitle);
List <string> listColumn = edModel.listEmployeeColumn;
/// 建立標題列
IRow xRowT = xSheet.CreateRow(0); xRowT.HeightInPoints = 40;
for (int i = 0; i < listColumn.Count; i++)
{
ICell xCellT = xRowT.CreateCell(i); xCellT.SetCellValue(listColumn[i]);
}
/// 讀取資料庫資料
List <listEmployeeDetail> ListEmpData = new List <listEmployeeDetail>();
ListEmpData = edModel.ReListEmployeeDetail();
if (ListEmpData != null && ListEmpData.Count > 0)
{
for (int i = 0; i < ListEmpData.Count; i++)
{
listEmployeeDetail item = ListEmpData[i];
List <string> list = new List <string>();
list.Add(item.lEmpIndex.ToString());
list.Add(item.lEmpName.ToString());
list.Add(item.lEmpSex.ToString());
list.Add(item.lEmpEmail.ToString());
list.Add(item.lEmpAddress.ToString());
list.Add(item.lEmpMobile.ToString());
list.Add(item.lEmpPhone.ToString());
list.Add(item.lEmpNotation.ToString());
list.Add(item.lEmpRemark.ToString());
list.Add(item.lEmpStatus.ToString());
list.Add(item.lEmpJoinDate.ToString());
list.Add(item.lEmpLeaveDate.ToString());
IRow xRowD = xSheet.CreateRow(i + 1); xRowD.HeightInPoints = 30;
for (int b = 0; b < list.Count; b++)
{
ICell xCellData = xRowD.CreateCell(b); xCellData.SetCellValue(list[b]);
}
}
}
MemoryStream MS = new MemoryStream(); NpoiWB.Write(MS);
Response.AddHeader("Content-Disposition", "attachment; filename=" + ExportFileName + "");
Response.BinaryWrite(MS.ToArray());
// ----------------------------------------------------------------------------------------------
// 釋放記憶體參數
NpoiWB = null; MS.Close(); MS.Dispose();
Response.Flush(); Response.End();
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
//XSSFWorkbook workbook = new XSSFWorkbook();(.xls用该格式限制列为256列）
//(.xlsx 16384列)
string sheetname = dtSource.TableName;
XSSFSheet sheet = workbook.CreateSheet(sheetname) as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
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;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)//超过6355就新建一个表
{
sheet = workbook.CreateSheet(sheetname) as XSSFSheet;
}
#region 表头及样式
//{
// XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
// headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// HSSFFont font = workbook.CreateFont() as HSSFFont;
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
//}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.SetFillForegroundColor(new XSSFColor(new byte[] { 153, 204, 204 }));
headStyle.FillPattern = FillPattern.SolidForeground;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontName = "宋体";
font.FontHeightInPoints = 11;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 320);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
XSSFCellStyle rowStyle = workbook.CreateCellStyle() as XSSFCellStyle;
rowStyle.Alignment = HorizontalAlignment.Center;
XSSFFont cellfont = workbook.CreateFont() as XSSFFont;
cellfont.FontName = "宋体";
cellfont.FontHeightInPoints = 11;
rowStyle.SetFont(cellfont);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
newCell.CellStyle = rowStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
// ms.Flush();
//ms.Position = 0;
// workbook.Dispose();
return(ms);
}
}
/// <summary>
/// 导出Xlsx
/// </summary>
/// <param name="localFilePath">文件保存路径</param>
/// <param name="dtSource">数据源</param>
public static void ExportXlsx(string localFilePath, System.Data.DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
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;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (XSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
/// <summary>
///
/// </summary>
/// <param name="error"></param>
/// <param name="strFileName"></param>
/// <param name="dsSource"></param>
/// <param name="pwd"></param>
/// <param name="rowIndex"></param>
/// <param name="wait"></param>
/// <param name="isAppend"></param>
/// <param name="dateFormat"></param>
public static void DataTableToExcel(ref string error, string strFileName, DataSet dsSource, string pwd, ref int rowIndex, WaitDialogForm wait = null, bool isAppend = false, string dateFormat = "yyyy-MM-dd")
{
try {
error = string.Empty;
FileStream fs = null;
XSSFWorkbook workbook = null;
if (!isAppend)
{
fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write);
workbook = new XSSFWorkbook();
}
else
{
fs = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
workbook = new XSSFWorkbook(fs);//将文件读到内存，在内存中操作excel
}
fs.Close();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Xw";
// workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "xw"; //填加xls文件作者信息
si.ApplicationName = "xw"; //填加xls文件创建程序信息
si.LastAuthor = "xw"; //填加xls文件最后保存者信息
si.Comments = "xw"; //填加xls文件作者信息
si.Title = "xw"; //填加xls文件标题信息
si.Subject = "xw"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
// workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(dateFormat);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFCellStyle rowStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont fontRow = (XSSFFont)workbook.CreateFont();
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 9;
font.FontName = "微软雅黑";
//font.Boldweight = 700;
headStyle.SetFont(font);
fontRow.FontHeightInPoints = 9;
fontRow.FontName = "微软雅黑";
rowStyle.SetFont(fontRow);
for (int k = 0; k < dsSource.Tables.Count; k++)
{
var dtSource = dsSource.Tables[k];
XSSFSheet sheet = null;
if (!isAppend)
{
rowIndex = 0;
sheet = (XSSFSheet)workbook.CreateSheet();
if (!string.IsNullOrEmpty(pwd))
{
sheet.ProtectSheet(pwd);
}
workbook.SetSheetName(k, dtSource.TableName);
}
else
{
sheet = (XSSFSheet)workbook.GetSheet(dtSource.TableName);
if (sheet == null)
{
isAppend = false;
rowIndex = 0;
sheet = (XSSFSheet)workbook.CreateSheet();
if (!string.IsNullOrEmpty(pwd))
{
sheet.ProtectSheet(pwd);
}
workbook.SetSheetName(k, dtSource.TableName);
}
}
if (wait != null)
{
wait.SetCaption(string.Format("正在创建第 {0} / {1} Excel的Sheet中", (k + 1), dsSource.Tables.Count));//"（" + (k + 1) + " /" + dsSource.Tables.Count + "）Excel的Sheet中...");
}
//取得列宽
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;
}
}
}
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (!isAppend)
{
//if (rowIndex != 0) {
//sheet = (XSSFSheet)workbook.CreateSheet();
//if (!string.IsNullOrEmpty(pwd)) {
// sheet.ProtectSheet(pwd);
//}
//}
#region 表头及样式
// if (!string.IsNullOrEmpty(strHeaderText)) {
// rowIndex++;
// XSSFRow headerRow = (XSSFRow)sheet.CreateRow(rowIndex);
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// // headStyle.Alignment = CellHorizontalAlignment.CENTER;
// XSSFFont font = (XSSFFont)workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
// }
#endregion
#region 列头及样式
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//headerRow.GetCell(column.Ordinal).CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
isAppend = true;
rowIndex = 1;
}
#endregion
if (wait != null)
{
wait.SetCaption(string.Format("正在将数据写入Excel第 {0} / {1} 行中...", rowIndex, dtSource.Rows.Count));//"正在写入第（" + (k + 1) + "/" + dsSource.Tables.Count + "个ExcelSheet表格中的第 " + rowIndex + "/" + dtSource.Rows.Count + " 行数据...");
}
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = rowStyle;
//newCell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//newCell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
var obj = row[column];
if (obj == null)
{
continue;
}
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double tempVal = 0;
var isflg = double.TryParse(drValue, out tempVal);
if (isflg)
{
newCell.SetCellValue(tempVal);
newCell.SetCellType(CellType.Numeric);
}
else
{
newCell.SetCellValue(drValue);
newCell.SetCellType(CellType.String);
}
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
isflg = System.DateTime.TryParse(drValue, out dateV);
if (isflg)
{
newCell.SetCellValue(drValue);
}
else
{
newCell.SetCellValue(string.Empty);
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.UInt16": //整型
case "System.UInt32":
case "System.UInt64":
int intV = 0;
isflg = int.TryParse(drValue, out intV);
if (isflg)
{
newCell.SetCellValue(intV);
newCell.SetCellType(CellType.Numeric);
}
break;
case "System.Single": //浮点型
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
isflg = double.TryParse(drValue, out doubV);
if (isflg)
{
newCell.SetCellValue(doubV);
newCell.SetCellType(CellType.Numeric);
}
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
newCell.SetCellType(CellType.String);
break;
default:
tempVal = 0;
isflg = double.TryParse(drValue, out tempVal);
if (isflg)
{
newCell.SetCellValue(tempVal);
newCell.SetCellType(CellType.Numeric);
}
else
{
newCell.SetCellValue(drValue);
newCell.SetCellType(CellType.String);
}
break;
}
}
#endregion
rowIndex++;
}
if (sheet != null)
{
sheet.ForceFormulaRecalculation = true;
}
}
//将内存数据写到文件
using (FileStream fs1 = File.OpenWrite(strFileName)) {
workbook.Write(fs1);
workbook.Close();
}
} catch (Exception ex) {
error = $"导出数据出错{ex.Message}";
}
}
private XSSFCellStyle PhaseCellStyle(CellStyle paramCellStyle)
{
XSSFCellStyle style = null;
//try
//{
style = (XSSFCellStyle)_workbook.CreateCellStyle();
XSSFFont font = (XSSFFont)_workbook.CreateFont();
font.FontName = paramCellStyle.FontName.ToString();
font.FontHeightInPoints = paramCellStyle.FontHeightInPoints;
if (paramCellStyle.FontBoldFlag)
{
font.Boldweight = 700;
}
style.SetFont(font);
//设置水平位置
switch (paramCellStyle.HorizontalAlignment.ToString())
{
case "Center":
style.Alignment = HorizontalAlignment.Center;
break;
case "Left":
style.Alignment = HorizontalAlignment.Left;
break;
case "Right":
style.Alignment = HorizontalAlignment.Right;
break;
default:
style.Alignment = HorizontalAlignment.Center;
break;
}
//设置垂直位置
switch (paramCellStyle.VerticalAlignment.ToString())
{
case "Center":
style.VerticalAlignment = VerticalAlignment.Center;
break;
case "Top":
style.VerticalAlignment = VerticalAlignment.Top;
break;
case "Botton":
style.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
style.VerticalAlignment = VerticalAlignment.Center;
break;
}
//设置表格线样式宽度
switch (paramCellStyle.BorderStyle.ToString())
{
case "Thin":
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
break;
default:
break;
}
//设置表格线颜色
switch (paramCellStyle.BorderStyle.ToString())
{
case "Black":
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
break;
default:
break;
}
//设置单元格是否换行
#region 设置为文本
style.WrapText = paramCellStyle.WrapText;
XSSFDataFormat format = (XSSFDataFormat)_workbook.GetCreationHelper().CreateDataFormat();
short index = format.GetFormat("@");
style.DataFormat = index;
#endregion
//}
//catch(Exception ex)
//{
// string ss = ex.Message;
// return null;
//}
return(style);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static void ExportDT(DataTable dtSource, string strHeaderText, string sheetName, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
else
{
sheet = workbook.CreateSheet(sheetName) as XSSFSheet;
}
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.UTF8.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.UTF8.GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <returns>Excel工作表</returns>
public static MemoryStream ExportToExcel(DataTable sourceTable, string sheetName = "对账单明细表")
{
if (sourceTable.Rows.Count <= 0)
{
return(null);
}
IWorkbook workbook = new XSSFWorkbook();
// ICellStyle cellStyle = GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sheetName);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//取得列宽
int[] arrColWidth = new int[sourceTable.Columns.Count];
foreach (DataColumn item in sourceTable.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < sourceTable.Rows.Count; i++)
{
for (int j = 0; j < sourceTable.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(sourceTable.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
if (intTemp > 30)
{
break;
}
arrColWidth[j] = intTemp;
}
}
}
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// handling value.
int rowIndex = 1;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
string drValue = row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
//newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal,CellType.String);
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
//ms.Flush();
//ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)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 = System.DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
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;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (XSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(1);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
public static void ExportToFile(GridView gv, string excelName)//匯出Excel
{
try
{
//建立WorkBook及試算表
XSSFWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
XSSFSheet mySheet1 = (XSSFSheet)workbook.CreateSheet(excelName);
//建立標題列Header
XSSFRow rowHeader = (XSSFRow)mySheet1.CreateRow(0);
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
string strValue = gv.HeaderRow.Cells[i].Text;
XSSFCell cell = (XSSFCell)rowHeader.CreateCell(i);
cell.SetCellValue(HttpUtility.HtmlDecode(strValue).Trim());
//建立新的CellStyle
ICellStyle CellsStyle = workbook.CreateCellStyle();
//建立字型
IFont StyleFont = workbook.CreateFont();
//設定文字字型
StyleFont.FontName = "微軟正黑體";
//設定文字大小
StyleFont.FontHeightInPoints = 12; //設定文字大小為10pt
CellsStyle.SetFont(StyleFont);
cell.CellStyle = CellsStyle;
}
//建立內容列 DataRow
for (int i = 0; i < gv.Rows.Count; i++)
{
XSSFRow rowItem = (XSSFRow)mySheet1.CreateRow(i + 1);
for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
{
Label lb = null; // 因為GridView中有TemplateField，所以要將Label.Text讀出來
if (gv.Rows[i].Cells[j].Controls.Count > 1)
{
lb = gv.Rows[i].Cells[j].Controls[1] as Label;
}
string value1 = (lb != null) ? HttpUtility.HtmlDecode(lb.Text) : HttpUtility.HtmlDecode(gv.Rows[i].Cells[j].Text).Trim();
int intry = 0;
bool isNumeric = !value1.StartsWith("0") && int.TryParse(value1, out intry);
XSSFCell cell = (XSSFCell)rowItem.CreateCell(j);
if (string.IsNullOrEmpty(value1.Trim()))
{
//空白
cell.SetCellValue(Convert.ToString(""));
}
else if (!isNumeric)
{
if (value1.Length > 10)
{
//文字格式
mySheet1.SetColumnWidth(j, 50 * 256); //欄位寬度設為50
}
else if (value1.Length > 3)
{
//文字格式
mySheet1.SetColumnWidth(j, 30 * 256); //欄位寬度設為30
}
else
{
//文字格式
mySheet1.SetColumnWidth(j, 15 * 256); //欄位寬度設為15
}
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); // 給cell style
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("@"); // 文字格式
//建立字型
IFont StyleFont = workbook.CreateFont();
//設定文字字型
StyleFont.FontName = "微軟正黑體";
//設定文字大小
StyleFont.FontHeightInPoints = 12; //設定文字大小為12pt
cellStyle.SetFont(StyleFont);
//cellStyle.WrapText = true; //文字自動換列
cell.CellStyle = cellStyle;
cell.SetCellValue(value1);
}
{
cell.SetCellValue(value1);
}
}
}
//匯出
workbook.Write(ms);
//此為匯出副檔名xlsx
//方法一
/*
* HttpContext.Current.Response.Clear();
* HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
* HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "AAA.xlsx"));
* HttpContext.Current.Response.BinaryWrite(ms.ToArray());
* HttpContext.Current.Response.Flush();
* HttpContext.Current.Response.End();*/
//方法二
using (FileStream fs = new FileStream(@"C:\Users\TPE-Intern001\Desktop\20210205.xls", FileMode.Create, FileAccess.Write, FileShare.None, 4096, true))//写入指定的文件
{
byte[] b = ms.ToArray();
fs.Write(b, 0, b.Length);
ms.Close();
fs.Flush();
fs.Close();
}
//釋放資源
workbook = null;
ms.Close();
ms.Dispose();
}
catch (Exception)
{ }
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream RenderToExcel(DataTable table, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
/// <summary>
/// DataTable导出到Excel的MemoryStream，2007格式
/// </summary>
/// <param name="dtSource">源DataTable</param>
public static MemoryStream ExportDT2007(DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
{
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "http://www.huobanplus.com";
//workbook.DocumentSummaryInformation = dsi;
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "杭州火图科技"; //填加xls文件作者信息
//si.ApplicationName = "伙伴商城"; //填加xls文件创建程序信息
//si.LastAuthor = "voidarea"; //填加xls文件最后保存者信息
//si.Comments = ""; //填加xls文件作者信息
//si.Title = ""; //填加xls文件标题信息
//si.Subject = ""; //填加文件主题信息
//si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
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;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (arrColWidth[column.Ordinal] > 255)
{
arrColWidth[column.Ordinal] = 254;
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
//rowIndex = 2;
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
return(ms);
}
}
public static bool DataSetToExcel(DataSet ds, string Path)
{
bool result = false;
FileStream fs = null;
XSSFWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
////自定义表头
string name = ds.Tables[i].TableName;
//for (var j = 0; j < dataList[name].ToArray().Count(); j++)
//{
// headerRow.CreateCell(j).SetCellValue(dataList[name].ToArray()[j]);
// headerRow.GetCell(j).CellStyle = headStyle;
//}
}
#endregion
rowIndex = 1;
}
#endregion
foreach (DataRow row in ds.Tables[i].Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in ds.Tables[i].Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
}
#endregion
rowIndex++;
}
}
using (fs = File.OpenWrite(Path))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return(result);
}
public static bool DataSetToExcel(DataSet ds, string Path, string strTitle)
{
try
{
string[] title = strTitle.Split(',');
bool result = false;
FileStream fs = null;
XSSFWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
string TableName = ds.Tables[i].TableName;
if (string.IsNullOrEmpty(TableName))
{
TableName = "Sheet1";
}
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//dateStyle.FillBackgroundColor = IndexedColors.Blue.Index;
//dateStyle.FillPattern = FillPattern.Bricks;
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//设置样式---
//设置单元格上下左右边框线
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 = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//cellStyle.DataFormat = 194;
//cellStyle.FillForegroundColor = IndexedColors.Pink.Index;
//cellStyle.FillPattern = FillPattern.SolidForeground;
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
XSSFRow eheaderRow = (XSSFRow)sheet.CreateRow(0);
//headerRow.Height = 1 * 256;
//for (int i = 0; i < columnNum; i++) //写入字段
// datas[0, i] = title[j];
for (int j = 0; j < title.Length; j++)
{
sheet.SetColumnWidth(j, 14 * 256);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//文字水平和垂直对齐方式
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
eheaderRow.CreateCell(j).SetCellValue(title[j]);
eheaderRow.GetCell(j).CellStyle = headStyle;
}
#endregion
rowIndex = 1;
}
#endregion
foreach (DataRow row in ds.Tables[i].Rows)
{
//string ss = row[0].ToString();
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in ds.Tables[i].Columns)
{
if (column.ColumnName == "CARTON_GROSS_WEIGTH")
{
string ssw = row[column].ToString();
}
string ss = row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
//if (type == "System.Decimal")
//{
// newCell.SetCellValue(double.Parse(row[column].ToString()));
//}
//else
//{
newCell.SetCellValue(GetValue(row[column].ToString(), type));
//}
newCell.CellStyle = cellStyle;
}
#endregion
rowIndex++;
}
}
using (fs = File.OpenWrite(Path))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return(result);
}
catch (Exception)
{
throw;
}
}
