public void TestCellType()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
cell.SetCellType(CellType.Blank);
Assert.AreEqual("9999-12-31 23:59:59.999", cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff"));
Assert.IsFalse(cell.BooleanCellValue);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("TRUE", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellType(CellType.Error);
Assert.AreEqual("#VALUE!", cell.ToString());
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("FALSE", cell.ToString());
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellType(CellType.String);
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
cell.SetCellType(CellType.String);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellValue((String)null);
cell.SetCellValue((IRichTextString)null);
}
private Stream RenderDataSetToExcel(DataSet ds)
{
MemoryStream ms = new MemoryStream();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet;
HSSFRow headerRow;
//for (int k = ds.Tables.Count - 1, len = 0; len <= k; k--) {
// DataTable dt = ds.Tables[k];
foreach (DataTable dt in ds.Tables)
{
sheet = (HSSFSheet)workbook.CreateSheet(dt.TableName);
headerRow = (HSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
int rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
public static void createTitle(HSSFSheet sheet, int row, List <string> titles, HSSFCellStyle style)
{
HSSFRow hsshRow = sheet.CreateRow(row) as HSSFRow;
hsshRow.Height = 500;
for (int i = 0; i < titles.Count; i++)
{
HSSFCell cell = hsshRow.CreateCell(i) as HSSFCell;
cell.CellStyle = style;
String textValue = "";
if (titles[i] != null)
{
textValue = titles[i].ToString();
}
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.SetCellValue(richString);
}
freezePane(sheet, 1, row + 1);
}
private void SaveDownloadResult(string filePath)
{
if (ConfigProperty.dictFileInfo.Count >= 1)
{
MessageHelper.MsgWait("正在保存抵扣发票下载信息，请耐心等待...");
try
{
string path = filePath + @"\抵扣发票下载结果" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("下载结果");
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue("序号");
HSSFCell cell2 = (HSSFCell)row.CreateCell(1);
cell2.SetCellValue("文件名");
HSSFCell cell3 = (HSSFCell)row.CreateCell(2);
cell3.SetCellValue("下载情况");
int rownum = 1;
foreach (KeyValuePair <string, string> pair in ConfigProperty.dictFileInfo)
{
row = (HSSFRow)sheet.CreateRow(rownum);
((HSSFCell)row.CreateCell(0)).SetCellValue((double)rownum);
((HSSFCell)row.CreateCell(1)).SetCellValue(pair.Key);
cell3 = (HSSFCell)row.CreateCell(2);
if (pair.Value == "1")
{
cell3.SetCellValue("已下载");
}
else
{
cell3.SetCellValue("未下载");
}
rownum++;
}
using (FileStream stream = new FileStream(path, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
}
catch (Exception exception)
{
MessageHelper.MsgWait();
MessageBox.Show(exception.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Hand);
this.loger.Error("抵扣发票批量下载：保存下载结果异常：" + exception.ToString());
}
MessageHelper.MsgWait();
}
}
public void SerializeToSheet(HSSFSheet sheet)
{
Dictionary <string, string> parameters = new Dictionary <string, string>();
foreach (ExportRow exportRow in _exports)
{
foreach (string parameterName in exportRow.ParameterNames)
{
parameters[parameterName] = parameterName;
}
}
string[] param = parameters.Select(pair => pair.Key).ToArray();
HSSFRow header = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < param.Length; i++)
{
HSSFCell cell = (HSSFCell)header.CreateCell(i, CellType.String);
cell.CellStyle.ShrinkToFit = true;
cell.SetCellValue(param[i]);
}
int index = 1;
foreach (ExportRow exportRow in _exports)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(index++);
for (int i = 0; i < param.Length; i++)
{
string value = exportRow.ContainsParameter(param[i])
? SerializeObject(exportRow.GetValue(param[i]))
: string.Empty;
HSSFCell cell = (HSSFCell)row.CreateCell(i, CellType.String);
cell.SetCellValue(value);
}
}
for (int i = 0; i < param.Length; i++)
{
sheet.AutoSizeColumn(i);
}
}
/// <summary>
/// 转换datatable数据为Excel表格
/// </summary>
/// <param name="dt"></param>
public void ConvertTableToExcel(DataTable dt, string tableName = null)
{
HSSFSheet sheet;
if (!string.IsNullOrEmpty(tableName))
{
sheet = (HSSFSheet)workBook2.CreateSheet(tableName);
}
else if (!string.IsNullOrEmpty(dt.TableName))
{
sheet = (HSSFSheet)workBook2.CreateSheet(dt.TableName);
}
else
{
sheet = (HSSFSheet)workBook2.CreateSheet(DateTime.Now.ToString("t"));
}
//标题行
HSSFRow title = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
title.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//正文内容
for (int i = 1; i < dt.Rows.Count; i++)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
var v = dt.Rows[i][j].ToString();
if (dt.Rows[i][j].ToString() == "")
{
break;
}
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
/// <summary>
/// 功能：将多个表的数据导出到EXECL
/// 创建人：孙佳杰 创建时间：2015.4.20
/// </summary>
/// <param name="modelList"></param>
/// <param name="execlFilePath"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool DataTableToExeclMoreSheet(List <ExeclModel> modelList, string execlFilePath, ref string error)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
using (MemoryStream ms = new MemoryStream())
{
foreach (ExeclModel model in modelList)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(model.name);
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in model.dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in model.dt.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in model.dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Position = 0;
return(FileUtil.WriteFile(execlFilePath, ms, ref error));
}
}
catch (Exception ex)
{
error = "由DataSet导出Excel(ex)" + ex.ToString();
throw;
}
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
// New files start with 4 built in fonts, and 21 built in styles
Assert.AreEqual(4, wb.NumberOfFonts);
Assert.AreEqual(21, wb.NumCellStyles);
// Create a test font and style, and use them
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.CreateCell(0).CellStyle = (s);
// Should have one more than the default of each
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
/// <summary>
/// 把DataTable转换成Excel文件流
/// </summary>
/// <param name="dataSource">DataTable数据源</param>
/// <returns></returns>
public static MemoryStream RenderDataToExcel(DataTable dataSource)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in dataSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in dataSource.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dataSource.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
//设置超链接
//HSSFCell cell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
//cell.SetCellValue("value");
//HSSFHyperlink link = new HSSFHyperlink(NPOI.SS.UserModel.HyperlinkType.URL);
//link.Address = "url";
//cell.Hyperlink = link;
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// Function to set the specified value in the cell identified by the specified row and column numbers
/// </summary>
/// <param name="rowNum">The row number of the cell</param>
/// <param name="columnNum">The column number of the cell</param>
/// <param name="value">The value to be set in the cell</param>
/// <param name="cellFormatting">The ExcelCellFormatting to be applied to the cell</param>
public void SetValue(int rowNum, int columnNum, String value,
ExcelCellFormatting cellFormatting)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(rowNum);
HSSFCell cell = (HSSFCell)row.CreateCell(columnNum);
cell.SetCellType(CellType.String);
cell.SetCellValue(value);
if (cellFormatting != null)
{
HSSFCellStyle cellStyle = ApplyCellStyle(workbook, cellFormatting);
cell.CellStyle = cellStyle;
}
WriteIntoFile(workbook);
}
private void GenerateHeader(HSSFSheet sheet, HSSFWorkbook workbook, int startRow, int cellIndex)
{
FontRecord rec = new FontRecord()
{
FontHeight = 16
};
rec.BoldWeight = short.MaxValue;
HSSFFont font = new HSSFFont(10, rec);
//font.Index = 16;
var style = workbook.CreateCellStyle();
SetCellStyle(style);
style.SetFont(font);
foreach (var header in _headers)
{
HSSFRow row = sheet.CreateRow(startRow);
var cell = row.CreateCell(cellIndex++);
cell.SetCellValue(Resource.GetString(header));
cell.CellStyle = style;
}
}
/// <summary>
/// 由DataTable导出Excel(适应于基本的模版导出，且不超过65535条)
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="modelpath">模版文件实体路径</param>
/// <param name="modelName">模版文件名称</param>
/// <param name="fileName">指定Excel工作表名称</param>
/// <param name="sheetName">作为模型的Excel</param>
/// <param name="rowindex">从第几行开始写入数据(此为行索引，若为1则从第2行开始写入数据)</param>
/// <returns>Excel工作表</returns>
public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName, int rowIndex)
{
int colIndex = 0;
FileStream file = new FileStream(modelpath + "/" + modelName, FileMode.Open, FileAccess.Read);//读入excel模板
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet(sheetName);
if (sourceTable.Rows.Count + rowIndex > 65535)
{
throw new ArgumentException("数据太多，系统尚不支持，请缩小查询范围!");
}
foreach (DataRow row in sourceTable.Rows)
{ //双循环写入sourceTable中的数据
colIndex = 0;
HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex);
foreach (DataColumn col in sourceTable.Columns)
{
xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());
colIndex++;
}
rowIndex++;
}
sheet1.ForceFormulaRecalculation = true;
//CS项目适用胡方法
//FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存
//hssfworkbook.Write(fileS);
//fileS.Close();
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
string[] sheetNames = sheetName.Split(',');
for (int i = 0; i < sheetNames.Length; i++)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return(ms);
}
/// <summary>
/// 从DataTable导出EXCEL
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public static Stream RenderDataTableToExcel(DataTable SourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in SourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in SourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 管理员导入游戏模板
/// </summary>
public void GameModel()
{
HSSFWorkbook workbook2007 = new HSSFWorkbook(); //创建xlsx工作簿
workbook2007.CreateSheet("Sheet1"); //新建1个Sheet工作表
//ISheet sheet = workbook2007.CreateSheet("Sheet1");
HSSFSheet SheetOne = (HSSFSheet)workbook2007.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
//对工作表先添加行，下标从0开始
for (int i = 0; i < 2; i++)
{
SheetOne.CreateRow(i); //为SheetOne添加2行
//IRow row = SheetOne.CreateRow(i);
}
//对每一行创建3个单元格
HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(0); //获取Sheet1工作表的首行
HSSFCell[] SheetCell = new HSSFCell[4];
for (int i = 0; i < 4; i++)
{
SheetCell[i] = (HSSFCell)SheetRow.CreateCell(i); //为第一行创建3个单元格
//ICell headcell = SheetRow.CreateCell(i);
}
//创建之后就可以赋值了
SheetCell[0].SetCellValue("game_name");
SheetCell[1].SetCellValue("platform_id");
SheetCell[2].SetCellValue("service_name");
SheetCell[3].SetCellValue("icon");
//不指定路径
MemoryStream memoryStream = new MemoryStream(); //创建内存流
workbook2007.Write(memoryStream); //npoi将创建好的工作簿写入到内存流
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "123.xls");
HttpContext.Current.Response.BinaryWrite(memoryStream.ToArray());
HttpContext.Current.Response.End();
memoryStream.Dispose();
workbook2007.Close();
}
/// <summary>
/// 將DataTable轉成Stream輸出.
/// </summary>
/// <param name="sourceTable">The source table.</param>
/// <param name="templateFileName">範本檔檔名</param>
/// <param name="startRows">指定由範本檔第幾列開始附加資料</param>
/// <returns></returns>
public static Stream RenderDataTableToExcel(DataTable sourceTable, string templateFileName, int startRows)
{
HSSFWorkbook workbook = FileToWorkBook(templateFileName);
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyle.WrapText = true;
// handling value.
int rowIndex = startRows;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(row[column].ToString());
cell.CellStyle = cellStyle;
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
workbook = null;
return(ms);
}
public void NpoiExcel(DataTable dt, string title)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
string DgvValue = dt.Rows[I][j].ToString();
row2.CreateCell(j).SetCellValue(DgvValue);
sheet.SetColumnWidth(j, 20 * 150);
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
public void TestFindComments()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
HSSFRow row = sh.CreateRow(5) as HSSFRow;
HSSFCell cell = row.CreateCell(4) as HSSFCell;
cell.CellComment = (comment);
HSSFTestModelHelper.CreateCommentShape(0, comment);
Assert.IsNotNull(sh.FindCellComment(5, 4));
Assert.IsNull(sh.FindCellComment(5, 5));
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
Assert.IsNotNull(sh.FindCellComment(5, 4));
Assert.IsNull(sh.FindCellComment(5, 5));
}
private static void CreateCells <T>(this HSSFWorkbook workbook, HSSFRow row, Dictionary <string, string> columns, T item, HSSFCellStyle cellStyle)
{
Type type = typeof(T);
PropertyInfo[] propertyInfos = type.GetProperties();
int index = 0;
foreach (KeyValuePair <string, string> entry in columns)
{
PropertyInfo info = propertyInfos.First(p => String.Equals(p.Name, entry.Key, StringComparison.CurrentCultureIgnoreCase));
if (info == null)
{
continue;
}
var cell = row.CreateCell(index);
var obj = info.GetValue(item, null);
short dateFormat = workbook.SetDateFormat();
cell.SetCellValue(obj.GetType(), obj.ToString(), cellStyle, dateFormat);
index++;
}
}
public void TestSetRemoveStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
HSSFCellStyle defaultStyle = wb.GetCellStyleAt((short)15) as HSSFCellStyle;
// Starts out with the default style
Assert.AreEqual(defaultStyle, cell.CellStyle);
// Create some styles, no change
HSSFCellStyle style1 = wb.CreateCellStyle() as HSSFCellStyle;
HSSFCellStyle style2 = wb.CreateCellStyle() as HSSFCellStyle;
style1.DataFormat = (/*setter*/ (short)2);
style2.DataFormat = (/*setter*/ (short)3);
Assert.AreEqual(defaultStyle, cell.CellStyle);
// Apply one, Changes
cell.CellStyle = (/*setter*/ style1);
Assert.AreEqual(style1, cell.CellStyle);
// Apply the other, Changes
cell.CellStyle = (/*setter*/ style2);
Assert.AreEqual(style2, cell.CellStyle);
// Remove, goes back to default
cell.CellStyle = (/*setter*/ null);
Assert.AreEqual(defaultStyle, cell.CellStyle);
// Add back, returns
cell.CellStyle = (/*setter*/ style2);
Assert.AreEqual(style2, cell.CellStyle);
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="strFileName">保存位置</param>
/// <param name="db">源DataTable</param>
public static void ExportExcel(string filePath, string fileName, DataTable db)
{
HSSFWorkbook workbook = new HSSFWorkbook();
string sheetName = "sheet1";
ISheet sheet = workbook.CreateSheet(sheetName);
HSSFRow rowHeader = (HSSFRow)sheet.CreateRow(0);//创建标题列
var ColumnsCount = 0;
foreach (DataColumn column in db.Columns)//填充列头信息
{
rowHeader.CreateCell(ColumnsCount).SetCellValue(column.ColumnName);
ColumnsCount++;
}
//填充内容
for (int i = 0; i < db.Rows.Count; i++)
{
HSSFRow dataRows = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < db.Columns.Count; j++)
{
dataRows.CreateCell(j).SetCellValue(db.Rows[i][j].ToString());
}
}
for (int i = 0; i <= db.Columns.Count; i++)//自动调整列宽
{
sheet.AutoSizeColumn(i, true);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
GC.Collect();
}
/// <summary>
/// 列头數據
/// </summary>
/// <param name="dtSource"></param>
/// <param name="headerTextDic"></param>
/// <param name="isShowTitle"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="arrColWidth"></param>
private static void SetColHeader(DataTable dtSource, Dictionary <string, string> headerTextDic, bool isShowTitle, HSSFWorkbook workbook, HSSFSheet sheet, int[] arrColWidth, HSSFCellStyle colHeaderStyle)
{
HSSFRow headerRow = null;
if (isShowTitle)
{
headerRow = sheet.CreateRow(1) as HSSFRow;
}
else
{
headerRow = sheet.CreateRow(0) as HSSFRow;
}
var index = 0;
foreach (var item in headerTextDic)
{
foreach (DataColumn column in dtSource.Columns)
{
if (column.ColumnName.ToLower() == item.Key.ToLower())
{
headerRow.CreateCell(index).SetCellValue(item.Value);
headerRow.GetCell(index).CellStyle = colHeaderStyle;
var colWidth = arrColWidth[index];
if (colWidth > 254)
{
colWidth = 254;
}
sheet.SetColumnWidth(index, (colWidth + 1) * 256);
index++;
break;
}
}
}
}
static void Main(string[] args)
{
HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿
workbook2003.CreateSheet("Sheet1"); //新建3个Sheet工作表
HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1");
SheetOne.CreateRow(7);
HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(7);
SheetRow.CreateCell(0);
var cell0 = SheetRow.GetCell(0);
cell0.SetCellValue("HelloWorld");
//HSSFCell[] SheetCell = new HSSFCell[10];
//SheetRow.CreateCell(0);
FileStream file2003 = new FileStream(@"C:\test\Excel2003.xls", FileMode.Create);
workbook2003.Write(file2003);
file2003.Close(); //关闭文件流
workbook2003.Close();
}
private string exportexceleaction(JsonArrayParse jp)
{
JsonObjectCollection collection = new JsonObjectCollection();
string flag = "0";
string pathName = "";
try
{
pathName = "工单列表" + GetDate().ToString("yyMMddHHmmss") + getRandom(4) + ".xls";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("工单列表");
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("工单编号");
headerRow.CreateCell(1).SetCellValue("工单内容");
headerRow.CreateCell(2).SetCellValue("工单日期");
headerRow.CreateCell(3).SetCellValue("工单类型");
headerRow.CreateCell(4).SetCellValue("要求服务时间");
headerRow.CreateCell(5).SetCellValue("联系人");
headerRow.CreateCell(6).SetCellValue("联系电话");
headerRow.CreateCell(7).SetCellValue("地址");
headerRow.CreateCell(8).SetCellValue("服务单号");
headerRow.CreateCell(9).SetCellValue("执行部门");
headerRow.CreateCell(10).SetCellValue("部门负责人");
headerRow.CreateCell(11).SetCellValue("执行人");
headerRow.CreateCell(12).SetCellValue("状态");
headerRow.CreateCell(13).SetCellValue("是否挂起");
headerRow.CreateCell(14).SetCellValue("是否退回");
headerRow.CreateCell(15).SetCellValue("是否申请支援");
headerRow.CreateCell(16).SetCellValue("响应时间");
headerRow.CreateCell(17).SetCellValue("预约时间");
headerRow.CreateCell(18).SetCellValue("签到时间");
headerRow.CreateCell(19).SetCellValue("执行时间");
headerRow.CreateCell(20).SetCellValue("完成时间");
headerRow.CreateCell(21).SetCellValue("消单时间");
headerRow.CreateCell(22).SetCellValue("确认消单时间");
DateTime MinOrderDateS = default(DateTime);
DateTime MaxOrderDateS = default(DateTime);
if (jp.getValue("MinOrderDate") != "")
{
MinOrderDateS = ParseDateForString(jp.getValue("MinOrderDate"));
}
if (jp.getValue("MaxOrderDate") != "")
{
MaxOrderDateS = ParseDateForString(jp.getValue("MaxOrderDate"));
}
bool?IsBackS = null;
bool?IsHangUpS = null;
if (jp.getValue("IsBackS") != "")
{
IsBackS = bool.Parse(jp.getValue("IsBackS"));
}
if (jp.getValue("IsHangUpS") != "")
{
IsHangUpS = bool.Parse(jp.getValue("IsHangUpS"));
}
string AlloUser = jp.getValue("AlloUserS");
//非管理员登录，只能看到当前用户为部门负责人的单
if (user.Entity.UserType.ToUpper() == "03" || user.Entity.UserType.ToUpper() == "07" || user.Entity.UserType.ToUpper() == "08")
{
AlloUser = user.Entity.UserNo;
}
int rowIndex = 1;
project.Business.Order.BusinessWorkOrder bc = new project.Business.Order.BusinessWorkOrder();
foreach (project.Entity.Order.EntityWorkOrder it in bc.GetWorkOrderListQuery(
"A", jp.getValue("OrderNoS"), jp.getValue("OrderNameS"), MinOrderDateS, MaxOrderDateS, jp.getValue("StatusS"),
jp.getValue("SaleNoS"), jp.getValue("RegionS"), jp.getValue("AlloDeptS"), AlloUser,
jp.getValue("CustNoS"), jp.getValue("OrderTypeS"), IsBackS, IsHangUpS, false))
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(it.OrderNo);
dataRow.CreateCell(1).SetCellValue(it.OrderName);
dataRow.CreateCell(2).SetCellValue(ParseStringForDateTime(it.OrderDate));
dataRow.CreateCell(3).SetCellValue(it.OrderTypeName);
dataRow.CreateCell(4).SetCellValue(ParseStringForDateTime(it.CustneedTime));
dataRow.CreateCell(5).SetCellValue(it.LinkMan);
dataRow.CreateCell(6).SetCellValue(it.LinkTel);
dataRow.CreateCell(7).SetCellValue(it.Addr);
dataRow.CreateCell(8).SetCellValue(it.SaleNo);
dataRow.CreateCell(9).SetCellValue(it.AlloDeptName);
dataRow.CreateCell(10).SetCellValue(it.AlloUserName);
dataRow.CreateCell(11).SetCellValue(it.PersonName);
dataRow.CreateCell(12).SetCellValue(it.StatusName);
dataRow.CreateCell(13).SetCellValue(it.IsHangUp?"是":"否");
dataRow.CreateCell(14).SetCellValue(it.IsBack?"是":"否");
dataRow.CreateCell(15).SetCellValue(it.IsApply?"是":"否");
dataRow.CreateCell(16).SetCellValue(ParseStringForDateTime(it.ResponseTime));
dataRow.CreateCell(17).SetCellValue(ParseStringForDateTime(it.AppoIntTime));
dataRow.CreateCell(18).SetCellValue(ParseStringForDateTime(it.SignTime));
dataRow.CreateCell(19).SetCellValue(ParseStringForDateTime(it.WorkTime));
dataRow.CreateCell(20).SetCellValue(ParseStringForDateTime(it.FinishTime));
dataRow.CreateCell(21).SetCellValue(ParseStringForDateTime(it.CloseTime));
dataRow.CreateCell(22).SetCellValue(ParseStringForDateTime(it.ConfirmTime));
dataRow = null;
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
headerRow = null;
sheet = null;
workbook = null;
FileStream fs = new FileStream(localpath + pathName, FileMode.OpenOrCreate);
BinaryWriter w = new BinaryWriter(fs);
w.Write(ms.ToArray());
fs.Close();
ms.Close();
ms.Dispose();
}
catch (Exception ex)
{
flag = "2";
collection.Add(new JsonStringValue("ex", ex.ToString()));
}
collection.Add(new JsonStringValue("type", "exportexcel"));
collection.Add(new JsonStringValue("flag", flag));
collection.Add(new JsonStringValue("path", pathName));
return(collection.ToString());
}
/// <summary>
/// DataTable导出到Excel的MemoryStream(.xls)
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="headRow">列头行数</param>
private static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText = "", int headRow = 1)
{
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 = "Harry"; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = "Harry"; //填加xls文件最后保存者信息
si.Comments = "Harry"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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 ExcelrowIndex = 0;
int DtRowIndex = 0;//
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (ExcelrowIndex == 65535 || ExcelrowIndex == 0)
{
if (ExcelrowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
#region 无用，准备删除
// HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
// headerRow.HeightInPoints = 25;
// HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// //headStyle.Alignment = HorizontalAlignment.Center;//水平居中
// //headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
// HSSFFont font = (HSSFFont)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();
// ExcelrowIndex++;
#endregion
HSSFCellStyle hssfcellstyle = (HSSFCellStyle)workbook.CreateCellStyle();
if (!string.IsNullOrEmpty(strHeaderText))
{
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref hssfcellstyle, ExcelrowIndex, false);
NewFoot(ref hssfcellstyle, workbook, 15);
headerRow.CreateCell(0).SetCellValue(strHeaderText);
headerRow.GetCell(0).CellStyle = hssfcellstyle;
ExcelrowIndex++;
}
}
#endregion
#region 列头及样式
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
#region 无用，准备删除
//HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//headerRow.HeightInPoints = 25;
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.WrapText = true;//自动换行
////headStyle.Alignment = HorizontalAlignment.Center;//水平居中
//headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.Boldweight = 700;
//headStyle.SetFont(font);
#endregion
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
//headerRow.Dispose();
ExcelrowIndex++;
}
//添加更多列
{
for (int i = 1; i < headRow; i++)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
foreach (DataColumn column in dtSource.Columns)
{
string drValue = row[column].ToString();
headerRow.CreateCell(column.Ordinal).SetCellValue(drValue);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, ((arrColWidth[column.Ordinal] + 1) * 256) > 10000 ? 10000 :
((arrColWidth[column.Ordinal] + 1) * 256)); //宽度10000可自定义
}
ExcelrowIndex++;
}
}
#endregion
}
#endregion
//跳过多行列头情况下已添加过的列
DtRowIndex++;
if (DtRowIndex < headRow)
{
continue;
}
#region 填充内容
#region 无用，准备删除
//HSSFRow dataRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//rowStyle.WrapText = true;//自动换行
//rowStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
#endregion
HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow dataRow = NewHSSFRow(ref sheet, workbook, ref rowStyle, ExcelrowIndex, true, 15);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)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;
}
dataRow.GetCell(column.Ordinal).CellStyle = rowStyle;
}
#endregion
ExcelrowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>导出EXCEL
/// </summary>
public void OutPutExcel(Dictionary <GoodsBaseModel, List <GoodsBaseModel> > dic)
{
var workbook = new HSSFWorkbook();
var ms = new MemoryStream();
var sheet = new HSSFSheet[1]; // 增加sheet。
#region Excel样式
//标题样式styletitle
HSSFFont fonttitle = workbook.CreateFont();
fonttitle.FontHeightInPoints = 12;
fonttitle.Color = HSSFColor.RED.index;
HSSFCellStyle styletitle = workbook.CreateCellStyle();
styletitle.BorderBottom = HSSFCellStyle.BORDER_THIN;
styletitle.BorderLeft = HSSFCellStyle.BORDER_THIN;
styletitle.BorderRight = HSSFCellStyle.BORDER_THIN;
styletitle.BorderTop = HSSFCellStyle.BORDER_THIN;
styletitle.SetFont(fonttitle);
//内容字体styleContent
HSSFFont fontcontent = workbook.CreateFont();
fontcontent.FontHeightInPoints = 9;
fontcontent.Color = HSSFColor.BLACK.index;
HSSFCellStyle styleContent = workbook.CreateCellStyle();
styleContent.BorderBottom = HSSFCellStyle.BORDER_THIN;
styleContent.BorderLeft = HSSFCellStyle.BORDER_THIN;
styleContent.BorderRight = HSSFCellStyle.BORDER_THIN;
styleContent.BorderTop = HSSFCellStyle.BORDER_THIN;
styleContent.SetFont(fontcontent);
//总计 styletotal
HSSFFont fonttotal = workbook.CreateFont();
fonttotal.FontHeightInPoints = 12;
fonttotal.Color = HSSFColor.RED.index;
fonttotal.Boldweight = 2;
HSSFCellStyle styletotal = workbook.CreateCellStyle();
styletotal.SetFont(fonttotal);
#endregion
#region [模板及sheet名字]
sheet[0] = workbook.CreateSheet("框架赠品绑定" + DateTime.Now.ToString("yyyy-MM-dd")); //添加sheet名
sheet[0].DefaultColumnWidth = 30;
sheet[0].DefaultRowHeight = 20;
HSSFRow rowtitle = sheet[0].CreateRow(0);
HSSFCell celltitie = rowtitle.CreateCell(0);
celltitie.SetCellValue("框架赠品绑定" + DateTime.Now.ToString("yyyy-MM-dd"));
HSSFCellStyle style = workbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Color = HSSFColor.BLACK.index;
font.Boldweight = 2;
style.SetFont(font);
celltitie.CellStyle = style;
sheet[0].AddMergedRegion(new Region(0, 0, 0, 7));
#endregion
#region [列名]
HSSFRow rowtitles = sheet[0].CreateRow(1);
HSSFCell ct1 = rowtitles.CreateCell(0);
HSSFCell ct2 = rowtitles.CreateCell(1);
ct1.SetCellValue("商品名名称");
ct2.SetCellValue("赠品商品名称");
ct1.CellStyle = styletitle;
ct2.CellStyle = styletitle;
#endregion
int row = 2;
foreach (var item in dic.Keys)
{
HSSFRow rowt = sheet[0].CreateRow(row);
HSSFCell c1 = rowt.CreateCell(0);
HSSFCell c2 = rowt.CreateCell(1);
c1.SetCellValue(item.GoodsName); //商品名
var giftList = dic.FirstOrDefault(ent => ent.Key.GoodsId == item.GoodsId).Value;
StringBuilder giftGoodsName = new StringBuilder();
foreach (var goodsBaseModel in giftList)
{
giftGoodsName.Append(" [ ").Append(goodsBaseModel.GoodsName).Append(" ] ");
}
c2.SetCellValue(giftGoodsName.ToString()); //赠品商品
c1.CellStyle = styleContent;
c2.CellStyle = styleContent;
c1.CellStyle.Alignment = HSSFCellStyle.ALIGN_LEFT;
row++;
}
sheet[0].DisplayGridlines = false;
workbook.Write(ms);
Response.ContentEncoding = Encoding.GetEncoding("utf-8");
Response.AddHeader("Content-Disposition",
"attachment; filename=" +
HttpUtility.UrlEncode("框架赠品绑定" + DateTime.Now.ToString("yyyyMMdd") + ".xls",
Encoding.UTF8));
Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
GC.Collect();
}
public static void ExportToFile(GridView gv, string excelName)
{
try
{
//建立 WorkBook 及試算表
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet mySheet1 = (HSSFSheet)workbook.CreateSheet(excelName);
//建立標題列 Header
HSSFRow rowHeader = (HSSFRow)mySheet1.CreateRow(0);
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
string strValue = gv.HeaderRow.Cells[i].Text;
HSSFCell cell = (HSSFCell)rowHeader.CreateCell(i);
cell.SetCellValue(HttpUtility.HtmlDecode(strValue).Trim()); //取得Gridview第i行，第一列的值
//建立新的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++)
{
HSSFRow rowItem = (HSSFRow)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(); //將 HTTP 傳輸的 HTTP 編碼字串轉換成已解碼的字串。
bool isNumeric = !value1.StartsWith("0") && int.TryParse(value1, out int intry);
HSSFCell cell = (HSSFCell)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
}
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); // 給cell style
HSSFDataFormat format = (HSSFDataFormat)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);
}
else
{
//數字格式
cell.SetCellValue(value1);
}
}
}
//匯出
workbook.Write(ms);
using (FileStream fs = new FileStream(@"C:\Users\TPE-Intern002\Desktop\Npoi20210203.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)
{ }
}
protected void ExportExpert(DataTable dtExpert)
{
string TempletFileName = Server.MapPath("~/Content/Template/专家.xls"); //模板文件
string ReportFileName = Server.MapPath("~/Content/Temp/ExcelTemp/专家用户.xls"); //导出文件
FileStream file = null;
try
{
file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
}
catch (Exception)
{
hdMsg.Value = "模板文件不存在或正在打开!";
return;
}
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
if (ws == null)//工作薄中没有工作表
{
hdMsg.Value = "工作薄中没有Sheet1工作表!";
return;
}
int count = dtExpert.Rows.Count;
if (count > 0)
{
for (int i = 0; i < count; i++)
{
int _row = i + 1; //i + 1;
HSSFRow row = (HSSFRow)ws.CreateRow(_row);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(dtExpert.Rows[i]["ExpertName"].ToString());
row.CreateCell(2).SetCellValue(dtExpert.Rows[i]["Gender"].ToString());
row.CreateCell(3).SetCellValue(dtExpert.Rows[i]["IDTypeText"].ToString());
row.CreateCell(4).SetCellValue(dtExpert.Rows[i]["IDNumber"].ToString());
row.CreateCell(5).SetCellValue(dtExpert.Rows[i]["Email"].ToString());
row.CreateCell(6).SetCellValue(dtExpert.Rows[i]["AcademicTitle"].ToString());
row.CreateCell(7).SetCellValue(dtExpert.Rows[i]["Profession"].ToString());
row.CreateCell(8).SetCellValue(dtExpert.Rows[i]["Workplace"].ToString());
row.CreateCell(9).SetCellValue(dtExpert.Rows[i]["SpecialtyNames"].ToString());
}
}
ws.ForceFormulaRecalculation = true;
//先删除上一个临时文件
if (File.Exists(ReportFileName))
{
FileInfo fi = new FileInfo(ReportFileName);
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
File.Delete(ReportFileName);
}
//创建临时文件写入数据
using (FileStream filess = File.OpenWrite(ReportFileName))
{
hssfworkbook.Write(filess);
}
System.IO.FileInfo filet = new System.IO.FileInfo(ReportFileName);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("专家用户.xls"));
Response.AddHeader("Content-Length", filet.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(filet.FullName);
Response.End();
}
/// <summary>
/// 利用office组件读取Excel表格中配件信息
/// </summary>
/// <returns></returns>
//public static DataTable ImportExcelFile(string FilesPathName)
//{
// try
// {
// string ExcelFilePath = string.Empty;//存储Excel文件路径
// string ExcelConnStr = string.Empty;//读取Excel表格字符串
// DataTable ExcelTable = null;//获取Excel表格中的数据
// string SheetName = string.Empty;//Excel中表名
// string ExcelSql = string.Empty;//查询Excel表格
// ExcelFilePath = FilesPathName;//获取选定文件的路径名
// if (Path.GetExtension(ExcelFilePath) != ".xlsx" && Path.GetExtension(ExcelFilePath) != ".xls")
// {
// MessageBoxEx.Show("请您输入Excel格式的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
// }
// else if (ExcelFilePath == string.Empty)
// {
// MessageBoxEx.Show("请您选择要导入的配件信息文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
// }
// else
// {
// //HDR=YES代表Excel第一行是标题不作为数据使用，IMEX三种模式：0代表写入模式，1代表读取模式，2代表链接模式（支持同时读取和写入）
// ExcelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
// OleDbConnection DbCon = new OleDbConnection(ExcelConnStr);//打开数据源连接
// DbCon.Open();
// ExcelTable = DbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });//获取Excel表格中的数据
// SheetName = ExcelTable.Rows[0]["table_name"].ToString();//获取Excel表名称
// ExcelSql = "select * from [" + SheetName + "]";
// OleDbDataAdapter DbAdapter = new OleDbDataAdapter(ExcelSql, DbCon);//执行数据查询
// DataSet ds = new DataSet();
// DbAdapter.Fill(ds, SheetName);//填充数据集
// DbCon.Close();//关闭数据源连接
// ExcelTable = ds.Tables[0];
// }
// return ExcelTable;
// }
// catch (Exception ex)
// {
// MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
// return null;
// }
//}
/// <summary>
/// 利用NPOI创建Excel文件
/// </summary>
/// <param name="ExcelTable">excel表格名称</param>
/// <param name="SheetName">sheet表名</param>
public static void NPOIExportExcelFile(DataTable NPOIExcelTable, string SheetName)
{
try
{
string SaveExcelName = string.Empty;//保存的Excel文件名称
SaveFileDialog SFDialog = new SaveFileDialog();
SFDialog.DefaultExt = "xls";
SFDialog.Filter = "Excel文件(*.xls)|*.xls";
SFDialog.ShowDialog();
SFDialog.CheckPathExists = true;
SFDialog.CheckFileExists = true;
SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
if (File.Exists(SaveExcelName))
{
if (IsExcelOpen(SaveExcelName))
{
File.Delete(SaveExcelName); //替换同名文件
}
else
{
return;
}
}
if (SaveExcelName.IndexOf(":") < 0)
{
return; //点击取消按钮返回
}
HSSFWorkbook WkBk = new HSSFWorkbook(); //创建工作表流
MemoryStream ms = new MemoryStream(); //创建支持内存的存储区流
HSSFSheet sht = (HSSFSheet)WkBk.CreateSheet(SheetName); //创建Excel表格
sht.CreateFreezePane(0, 1, 0, 1); //设置冻结首行
HSSFRow HeaderRow = (HSSFRow)sht.CreateRow(0); //创建标题行
int TotalCount = NPOIExcelTable.Rows.Count;
int rowRead = 0; //读取行数
ProgressBarFrm ProgBarFrm = new ProgressBarFrm();
ProgBarFrm.MaxNum = TotalCount; //获取最大记录行
ProgBarFrm.ShowDialog(); //显示进度条
for (int i = 0; i < NPOIExcelTable.Columns.Count; i++)
{
HeaderRow.CreateCell(i).SetCellValue(NPOIExcelTable.Columns[i].ColumnName.ToString()); //创建标题列
SetXlsHeaderStyle(HeaderRow, WkBk, i); //设置标题样式
sht.SetColumnWidth(i, 1000 * 5); //设置列宽
}
//创建Excel数据行项
for (int j = 0; j < NPOIExcelTable.Rows.Count; j++)
{
HSSFRow DatasRow = (HSSFRow)sht.CreateRow(j + 1); //创建数据行
for (int k = 0; k < NPOIExcelTable.Columns.Count; k++)
{ //填充Excel表数据
DatasRow.CreateCell(k).SetCellValue(NPOIExcelTable.Rows[j][k].ToString());
}
rowRead++; //自动增长行号
ProgressBarFrm.CurrentValue = rowRead; //传递当前值
Application.DoEvents(); //处理当前在消息队列中所有windows消息
}
//写入内存流数据
WkBk.Write(ms);
ms.Flush();//清空缓存
ms.Position = 0;
sht = null;
HeaderRow = null;
WkBk = null;
FileStream fs = new FileStream(SaveExcelName, FileMode.CreateNew, FileAccess.Write); //创建文件流
byte[] dataXls = ms.ToArray(); //把文件流转换为字节数组
fs.Write(dataXls, 0, dataXls.Length);
fs.Flush(); //清除缓存
fs.Close(); //关闭文件流
dataXls = null;
ms = null;
fs = null;
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
//private static WriteLog wl = new WriteLog();
#region 从datatable中将数据导出到excel
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#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
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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 HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
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 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
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
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
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;
sheet.Dispose();
workbook.Dispose();
return(ms);
}
}