public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue("Date Range");
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
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));
}
public static StyleContainer CreateStyles(HSSFWorkbook workbook)
{
var styles = new StyleContainer();
var h1Font = workbook.CreateFont();
h1Font.FontHeightInPoints = (short)24;
h1Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
var h2Font = workbook.CreateFont();
h2Font.FontHeightInPoints = (short)16;
h2Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
var h3Font = workbook.CreateFont();
h3Font.FontHeightInPoints = (short)12;
h3Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
styles.RightAligned = workbook.CreateCellStyle();
styles.RightAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
styles.Date = workbook.CreateCellStyle();
styles.Date.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
styles.Date.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
styles.Currency = workbook.CreateCellStyle();
styles.Currency.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
//styles.Currency.DataFormat = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
styles.Currency.DataFormat = (short)7;
styles.LeftAligned = workbook.CreateCellStyle();
styles.LeftAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
styles.TitleStyle = workbook.CreateCellStyle();
styles.TitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
styles.TitleStyle.SetFont(titleFont);
// Fonts are set into a style so create a new one to use.
styles.HeaderStyle = workbook.CreateCellStyle();
styles.HeaderStyle.SetFont(h1Font);
styles.HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// Fonts are set into a style so create a new one to use.
styles.Header2Style = workbook.CreateCellStyle();
styles.Header2Style.SetFont(h2Font);
// Fonts are set into a style so create a new one to use.
styles.Header3Style = workbook.CreateCellStyle();
styles.Header3Style.SetFont(h3Font);
return styles;
}
/// <summary>
/// 加边框
/// </summary>
/// <param Name="rowindex">1开始</param>
/// <param Name="cellIndex">1开始</param>
public void AddBorder( ISheet sheet, HSSFWorkbook workbook)
{
ICellStyle styel = workbook.CreateCellStyle();
styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 11;
font1.Boldweight = 600;
font1.FontName = "宋体";
styel.SetFont(font1);
for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++)
{
for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ )
{
sheet.GetRow(rowindex).RowStyle = styel;
ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex );
HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;
Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
Style.DataFormat = 0;
Style.SetFont(font1);
cell.CellStyle = Style;
}
}
}
public WorkbookExcelRender()
{
_workbook = new HSSFWorkbook();
_isDisposed = false;
OddCellStyle = _workbook.CreateCellStyle();
EvenCellStyle = _workbook.CreateCellStyle();
OddCellStyleCenterAligned = _workbook.CreateCellStyle();
EvenCellStyleCenterAligned = _workbook.CreateCellStyle();
_nextCellIndex = -1;
RTFHelper = new RTFHelper()
{
NegFont = _workbook.CreateFont(),
NormalFont = _workbook.CreateFont(),
PosFont = _workbook.CreateFont(),
NegFontCrossed = _workbook.CreateFont(),
NormalFontCrossed = _workbook.CreateFont(),
PosFontCrossed = _workbook.CreateFont(),
Pallete = _workbook.GetCustomPalette(),
RTFRenderer = new RtfTextRender()
};
InitDefaultRTFHelper();
InitDefaultCellStyles();
}
public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
//ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color=(NPOI.HSSF.Util.HSSFColor.RED.index);
fnt.Boldweight=(short)FontBoldWeight.BOLD;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle=(cs);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
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();
}
private void FillRows(HSSFWorkbook workbook, ISheet workSheet, List<Cell> cells, dynamic rows)
{
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
var currentRow = 1;
if (rows == null)
{
return;
}
foreach (var row in rows)
{
var workRow = workSheet.CreateRow(currentRow);
var currentCol = 0;
var type = row.GetType();
foreach (var cell in cells)
{
var workCell = workRow.CreateCell(currentCol);
SetCellValue(workbook, workCell, type, row, cell);
currentCol++;
}
currentRow++;
}
}
private static void SetCellValue(HSSFWorkbook workbook, ICell workCell, Type type, dynamic row, Cell cell)
{
var value = type.GetProperty(cell.Field).GetValue(row);
if (value == null)
{
return;
}
if (value is DateTime)
{
workCell.SetCellValue((DateTime)value);
}
else if (value is int)
{
workCell.SetCellValue((int)value);
}
else if (value is double)
{
workCell.SetCellValue((double)value);
}
else
{
workCell.SetCellValue(value.ToString());
}
if (!string.IsNullOrWhiteSpace(cell.Format))
{
var cellStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat(cell.Format);
workCell.CellStyle = cellStyle;
}
}
/// <summary>
/// Creates the excel workbook.
/// </summary>
/// <param name="subject">The subject.</param>
public void CreateWorkbook(string subject)
{
//Creating the excel workbook
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Creating summary information to the document
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Thoris";
//Applying summary information to the document
wb.DocumentSummaryInformation = dsi;
//Creating summary information for the data
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
//Applying summary information to the data
wb.SummaryInformation = si;
_workbook = wb;
//Creating the current styles
Font font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(font);
_headerStyle = CreateHeaderStyle(_workbook);
_dataStyle = CreateStyle(_workbook, false);
}
public void NpoiExcel(DataTable dt, string title, string s)
{
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;
string[] ss = s.Split(',');
for (int i = 0; i < ss.Length; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(ss[i]);
}
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++)
{
//double a = 0;
//string b = "";
//try
//{
// a = Convert.ToDouble(dt.Rows[I][j].ToString());
// row2.CreateCell(j).SetCellValue(a);
// sheet.SetColumnWidth(j, 20 * 150);
//}
//catch
//{
// b = dt.Rows[I][j].ToString();
// row2.CreateCell(j).SetCellValue(b);
// sheet.SetColumnWidth(j, 20 * 150);
//}
string DgvValue = dt.Rows[I][j].ToString();
if (j == 8 || j == 9)
{
row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue));
}
else
{
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();
}
//数据导出为Excel
private void btnExport_Click(object sender, EventArgs e)
{
string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers";
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
{
if (reader.HasRows)
{
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("customers");
int rowIndex = 0;//手动处理行索引
while (reader.Read())
{
int autoId = reader.GetInt32(0);
string name = reader.GetString(1);
string cellPhone = reader.GetString(2);
//数据为空,那么就不能Get..,所以要在这里判断
string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可为空
DateTime BuyDate = reader.GetDateTime(4);
string carNum = reader.GetString(5);
string bracketNum = reader.GetString(6);
IRow row = sheet.CreateRow(rowIndex);
rowIndex++;
row.CreateCell(0).SetCellValue(autoId);
row.CreateCell(1).SetCellValue(name);
row.CreateCell(2).SetCellValue(cellPhone);
//如果为空或Null,把Excel这个单元格设置为值为空
if (string.IsNullOrEmpty(landLine))
{
row.CreateCell(3).SetCellType(CellType.BLANK);
}
else
{
row.CreateCell(3).SetCellValue(landLine);
}
//============设置日期格式=======
//创建单元格样式对象
ICellStyle cellStyle = wb.CreateCellStyle();
//设置单元格样式对象属性
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
ICell BuyDateRow = row.CreateCell(4);
BuyDateRow.CellStyle = cellStyle;
BuyDateRow.SetCellValue(BuyDate);
//==============设置完成========
row.CreateCell(5).SetCellValue(carNum);
row.CreateCell(6).SetCellValue(bracketNum);
}
using (FileStream fs = File.OpenWrite("Customers.xls"))
{
wb.Write(fs);
MessageBox.Show("ok");
}
}
}
}
public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue(Title);
foreach (var org in list)
{
if (org.Value == true)
{
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
cell.SetCellValue(org.Key);
}
}
}
public void NpoiExcel(System.Data.DataTable dt, string file)
{
try
{
double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000;
int sheetCount = (int)(Math.Ceiling(sheetCountdbl));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
for (int c = 0; c < sheetCount; c++)
{
string sheetname = "Sheet" + (c + 1).ToString();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
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);
}
int js = 0;//计数
//表内容
for (int i = c * 60000; i < dt.Rows.Count; i++)
{
if (js > 59999)
{
break;
}
IRow row = sheet.CreateRow(js + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = style;
cell.SetCellValue(dt.Rows[i][j].ToString());
}
js++;
}
}
FileStream fs = File.Create(file);
book.Write(fs);
fs.Flush();
fs.Close();
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
}
public void TestSetAlignment()
{
// Create initial cell with default style
IWorkbook wkb = new HSSFWorkbook();
ISheet sheet = wkb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
// Create a new cell with default style
ICell cell2 = row.CreateCell(1);
// Init a new cell style cloned from the one of cell 0
cell2.CellStyle = wkb.CreateCellStyle();
cell2.CellStyle.CloneStyleFrom(cell.CellStyle);
// At this time cell style index should be different
Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index);
// Set an arbitraty cell style property to differentiate the two styles
cell.CellStyle.Alignment = HorizontalAlignment.Right;
// Try to make the same change so that CellUtil will get existing style
CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Right);
// Check that cell style has properly been set to HorizontalAlignment.Right
Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Right);
// Check that cell style index are the same again
Assert.AreEqual(cell.CellStyle.Index, cell2.CellStyle.Index);
// Init a new cell style cloned from the one of cell 0
cell2.CellStyle = wkb.CreateCellStyle();
cell2.CellStyle.CloneStyleFrom(cell.CellStyle);
// Set an arbitraty cell style property to differentiate the two styles
cell.CellStyle.Alignment = HorizontalAlignment.Left;
// Try to make different change so that CellUtil will get new style
CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Center);
// Check that cell style has alignement property set to HorizontalAlignment.Center
Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Center);
// Check that cell style index are different
Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index);
}
private void btnOutput_Click(object sender, EventArgs e)
{
List <MODEL.Classes> lists = cm.GetAllClass(false);
/*
* 1.创建出工作薄
* 2.为这个工作薄创建出工作表
* 3.为这个表创建出行
* 4.为这个行创建出每一列(单元格cell)--添加数据
* 5.文件的写入
*/
//创建一个工作薄
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
//创建一张工作表
ISheet sheet1 = workbook.CreateSheet("sh1");
// workbook.CreateSheet("Sh1");
///NPOI.HSSF. .SS .UserModel.HSSFSheet sheet1 = workbook.CreateSheet(); //.CreateSheet("sh1");
////workbook.CreateSheet("sh1"); // workbook.CreateSheet("sh1");
//需要导出的数据在集合中，每一个对象对应着后期表的一行数据
for (int i = 0; i < lists.Count; i++)
{
//创建行
IRow row = sheet1.CreateRow(i);
//创建第一个单元格
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(lists[i].CID);
//创建第2个单元格
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(lists[i].CName);
//创建第3个单元格
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(lists[i].CCount);
//创建第4个单元格
ICell cell4 = row.CreateCell(3);
cell4.SetCellValue(lists[i].CImg);
//创建第5个单元格
ICell cell5 = row.CreateCell(4);
cell5.SetCellValue(lists[i].CIsDel);
//创建第6个单元格
ICell cell6 = row.CreateCell(5);
//cell6.SetCellValue(lists[i].CAddTime.ToString("yyyy年MM月dd日")); //日期值会被当成double
cell6.SetCellValue(lists[i].CAddTime);
//如何修改日期类型的格式
ICellStyle cs = workbook.CreateCellStyle();
IDataFormat df = workbook.CreateDataFormat();
cs.DataFormat = df.GetFormat("yyyy年MM月dd日");
cell6.CellStyle = cs;
}
using (FileStream fs = new FileStream("aa.xls", FileMode.Create))
{
workbook.Write(fs);
MessageBox.Show("ok");
}
}
/// <summary>
/// 导出内容到excel下载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void ExportToExcel()
{
HSSFWorkbook workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
//创建一个单元格样式
var backgroundColorStyle = workbook.CreateCellStyle();
backgroundColorStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
backgroundColorStyle.FillForegroundColor = HSSFColor.BLUE.index;
//创建表头行列及赋值样式
var headTitles = new[] { "StudentCode", "ClassCode", "RoomName" };
var headRow = sheet.CreateRow(0);
for (var i = 0; i < 3; i++)
{
var cell = headRow.CreateCell(i);
if (i == 0)
cell.CellStyle = backgroundColorStyle;
cell.SetCellValue(headTitles[i]);
}
//创建数据行列
for (int i = 1; i < this._lessenList.Count + 1; i++)
{
var lesson = this._lessenList[i - 1];
var row = sheet.CreateRow(i);
if (lesson.StudentCode == "BJ02")
row.Height = 50 * 20;
//设置背景色
var cellStudentCode = row.CreateCell(0);
cellStudentCode.CellStyle = backgroundColorStyle;
cellStudentCode.SetCellValue(lesson.StudentCode);
row.CreateCell(1).SetCellValue(lesson.ClassCode);
row.CreateCell(2).SetCellValue(lesson.RoomName);
}
//数据写进内存流
MemoryStream memoryStream = new MemoryStream();
workbook.Write(memoryStream);
//销毁对象
workbook = null;
sheet = null;
headRow = null;
//将内存流以附件形式输出到客户端
var bytes = memoryStream.ToArray();
this._response.AddHeader("Content-Disposition", "attachment;filename=1.xls");
this._response.AddHeader("Content-Length", bytes.Length.ToString());
this._response.OutputStream.Write(bytes, 0, bytes.Length);
this._response.Flush();
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public ICellStyle CreateBorderCellStyle(HSSFWorkbook wb)
{
ICellStyle style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
return style;
}
protected void btnExportExcel_Click(object sender,EventArgs e)
{
HSSFWorkbook workBook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "杭州农副产品物流网络科技有限公司";
workBook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "采购配送系统配送价格单页";
workBook.SummaryInformation = si;
HSSFSheet sheet = workBook.CreateSheet("价格单页");
DataTable dt = logic.product.listWithPriceByBuyer(companyId);
int rowIndex = 0;
int colIndex = 0;
HSSFRow newRow = null;
HSSFCellStyle style = null;
HSSFCell cell=null;
foreach (DataRow row in dt.Rows)
{
newRow = sheet.CreateRow(rowIndex++);
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
cell=newRow.CreateCell(colIndex++);
cell.SetCellValue(row[col].ToString());
if (rowIndex % 2 == 1)
{
style = workBook.CreateCellStyle();
style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index;
style.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
cell.CellStyle = style;
}
}
}
string filename = ddlCompanyList.SelectedItem.Text + ".xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8)));
Response.Clear();
MemoryStream memoryStream = new MemoryStream();
workBook.Write(memoryStream);
Response.BinaryWrite(memoryStream.GetBuffer());
Response.End();
}
/// <summary>
/// 普通单元格样式 居中
/// </summary>
/// <param name="book"></param>
/// <returns>参见模型</returns>
public ICellStyle getCellStyleCenter(NPOI.HSSF.UserModel.HSSFWorkbook book)
{
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center; //水平居中
cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
cellstyle.WrapText = true; //自动换行
IFont cellfont = book.CreateFont();
cellfont.FontHeightInPoints = 11; //11号字体
cellstyle.SetFont(cellfont);
return(cellstyle);
}
/// <summary>
/// 标题单元格样式
/// </summary>
/// <param name="book"></param>
/// <returns>参见模型</returns>
public ICellStyle getCellStyleTitle(NPOI.HSSF.UserModel.HSSFWorkbook book)
{
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
IFont titlefont = book.CreateFont();
titlefont.FontHeightInPoints = 14;
titlefont.Boldweight = (short)FontBoldWeight.Bold; //字体加粗
cellstyle.SetFont(titlefont);
return(cellstyle);
}
public void SetUp()
{
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
style = wb.CreateCellStyle();
IDataFormat fmt = wb.CreateDataFormat();
style.DataFormat=(fmt.GetFormat("hh:mm:ss"));
cell11 = sheet.CreateRow(0).CreateCell(0);
form = new DataFormatter();
Evaluator = new HSSFFormulaEvaluator(wb);
}
/// <summary>
/// 设置样式
/// </summary>
private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.ICell newCell = null;
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
else if (strColDataType.Equals("System.DateTime"))
{
DateTime dateV;
DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
newCell = row2.CreateCell(j);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
newCell.CellStyle = cellStyle;
}
}
}
}
public byte[] WriteExcel(IEnumerable data, string[] columns)
{
MemoryStream output = new MemoryStream();
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.SetFont(headerFont);
headerStyle.Alignment = HorizontalAlignment.Center;
//(Optional) freeze the header row so it is not scrolled
sheet.CreateFreezePane(0, 1, 0, 1);
IEnumerator foo = data.GetEnumerator();
foo.MoveNext();
Type t = foo.Current.GetType();
IRow header = sheet.CreateRow(0);
PropertyInfo[] properties = t.GetProperties();
int colIndex = 0;
for (int i = 0; i < properties.Length; i++)
{
if (columns.Contains(properties[i].Name))
{
ICell cell = header.CreateCell(colIndex);
cell.CellStyle = headerStyle;
cell.SetCellValue(properties[i].Name);
colIndex++;
}
}
int rowIndex = 0;
foreach (object o in data)
{
colIndex = 0;
IRow row = sheet.CreateRow(rowIndex + 1);
for (int i = 0; i < properties.Length; i++)
{
if (columns.Contains(properties[i].Name))
{
row.CreateCell(colIndex).SetCellValue(properties[i].GetValue(o, null).ToString());
colIndex++;
}
}
rowIndex++;
}
workbook.Write(output);
return output.ToArray();
}
/// <summary>
/// 头部单元格样式
/// </summary>
/// <param name="book"></param>
/// <returns>参见模型</returns>
public ICellStyle getCellStyleHead(NPOI.HSSF.UserModel.HSSFWorkbook book)
{
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Center;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.WrapText = true;
IFont cellheadfont = book.CreateFont();
cellheadfont.FontHeightInPoints = 11;
cellheadfont.Boldweight = (short)FontBoldWeight.Bold; //字体加粗
cellstyle.SetFont(cellheadfont);
return(cellstyle);
}
public XlsModel(string sheetName)
{
SheetName = sheetName;
// Create a new workbook and a sheet named "User Accounts"
_workbook = new HSSFWorkbook();
_sheet = _workbook.CreateSheet(SheetName);
var boldFont = _workbook.CreateFont();
boldFont.FontHeightInPoints = 10;
boldFont.FontName = "Arial";
boldFont.Boldweight = (short) FontBoldWeight.Bold;
_dateStyle = _workbook.CreateCellStyle();
_dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd/mm/yyyy");
_nullStyle = _workbook.CreateCellStyle();
_nullStyle.FillForegroundColor = HSSFColor.Grey40Percent.Index;
_nullStyle.FillPattern = FillPattern.SolidForeground;
_headStyle = _workbook.CreateCellStyle();
_headStyle.SetFont(boldFont);
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public bool ToExcel(DataTable table)
{
FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
IWorkbook workBook = new HSSFWorkbook();
this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
ISheet sheet = workBook.CreateSheet(this._sheetName);
//处理表格标题
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(this._title);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
row.Height = 500;
ICellStyle cellStyle = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 17;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
row.Cells[0].CellStyle = cellStyle;
//处理表格列头
row = sheet.CreateRow(1);
for (int i = 0; i < table.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
//处理数据内容
for (int i = 0; i < table.Rows.Count; i++)
{
row = sheet.CreateRow(2 + i);
row.Height = 250;
for (int j = 0; j < table.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
sheet.SetColumnWidth(j, 256 * 15);
}
}
//写入数据流
workBook.Write(fs);
fs.Flush();
fs.Close();
return true;
}
public void TestColorStyleCopy()
{
HSSFWorkbook bookA = new HSSFWorkbook();
HSSFWorkbook bookB = new HSSFWorkbook();
bookA.Workbook.CustomPalette.ClearColors();
bookA.Workbook.CustomPalette.SetColor(0x8, 12, 15, 255); //0x8 is blueish
bookA.Workbook.CustomPalette.SetColor(0x9, 200, 200, 200); //0x9 is light gray
bookB.Workbook.CustomPalette.ClearColors();
bookB.Workbook.CustomPalette.SetColor(0x8, 192, 168, 0); //Throw a color into the destination book so we can see color merge working.
HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet;
ICell cell = sheetA.CreateRow(0).CreateCell(0);
cell.SetCellValue("I'm a stylish cell!");
IFont myFont = bookA.CreateFont();
myFont.FontName = "Times New Roman";
myFont.IsItalic = true;
myFont.FontHeightInPoints = 12;
myFont.Color = 0x8;
ICellStyle myStyle = bookA.CreateCellStyle();
myStyle.SetFont(myFont);
myStyle.FillForegroundColor = 0x9;
cell.CellStyle = myStyle;
HSSFSheet sheetB = bookB.CreateSheet("BookB Sheet") as HSSFSheet;
ICell beeCell = sheetB.CreateRow(0).CreateCell(0);
ICellStyle styleB = bookB.CreateCellStyle();
styleB.FillForegroundColor = 0x8;
beeCell.CellStyle = styleB;
beeCell.SetCellValue("Hello NPOI");
//Copy the sheet, and make sure the color, style, and font is correct
sheetA.CopyTo(bookB, "Copied Sheet A", true, true);
HSSFSheet theCopy = bookB.GetSheetAt(1) as HSSFSheet;
ICell copiedCell = theCopy.GetRow(0).GetCell(0);
//Check that the fill color got copied
byte[] srcColor = bookA.Workbook.CustomPalette.GetColor(0x9);
byte[] destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.FillForegroundColor);
Assert.IsTrue(srcColor[0]==destColor[0] && srcColor[1]==destColor[1] && srcColor[2]==destColor[2]);
//Check that the font color got copied
srcColor = bookA.Workbook.CustomPalette.GetColor(0x8);
destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.GetFont(bookB).Color);
Assert.IsTrue(srcColor[0] == destColor[0] && srcColor[1] == destColor[1] && srcColor[2] == destColor[2]);
//Check that the fill color of the cell originally in the destination book is still intact
srcColor = bookB.Workbook.CustomPalette.GetColor(0x8);
Assert.IsTrue(srcColor[0] == 192 && srcColor[1] == 168 && srcColor[2] == 0);
//Check that the font made it over okay
Assert.AreEqual(copiedCell.CellStyle.GetFont(bookB).FontName, myFont.FontName);
}
public byte[] Create(List<string> columns, List<List<string>> rows, HttpServerUtilityBase server)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Sheet1");
// create header style
var headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.BOLD;
var headerStyle = workbook.CreateCellStyle();
headerStyle.FillBackgroundColor = HSSFColor.GREY_40_PERCENT.index;
headerStyle.FillForegroundColor = HSSFColor.GREY_40_PERCENT.index;
headerStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headerStyle.SetFont(headerFont);
// Getting the row... 0 is the first row.
var dataRow = sheet.CreateRow(0);
for (int i = 0; i < columns.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(columns.ElementAt(i));
dataRow.GetCell(i).CellStyle = headerStyle;
}
var rowCount = 0;
foreach (var a in rows) // gets the array that represents a row
{
rowCount++;
dataRow = sheet.CreateRow(rowCount);
for (var i = 0; i < a.Count; i++) // gets the individual cell
{
dataRow.CreateCell(i).SetCellValue(a[i]);
}
}
// Forcing formula recalculation...
sheet.ForceFormulaRecalculation = true;
// adjust column widths
for (int i = 0; i < columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
return ms.ToArray();
}
}
public static ICellStyle CreateHeaderStyle(HSSFWorkbook wb, short foreColor, short backgroundColor)
{
var style = wb.CreateCellStyle();
style.FillForegroundColor = (short)17;
var font = wb.CreateFont();
font.Color = foreColor;
font.FontHeightInPoints = 12;
style.SetFont(font);
style.FillBackgroundColor = 16;
style.FillForegroundColor = backgroundColor;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
return style;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="monthFlag"></param>
public static void GetExcelReportItem(DataTable dt, string Name)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(Name);
//开始行，小标从0开始
int StartRow = 1;
//设置单元格样式
HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle();
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //DASH_DOT_DOT
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //HAIR
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //MEDIUM_DASHED
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#region 给excel数据源
//创建表头
IRow rowColumns = sheet1.CreateRow(0);
rowColumns.HeightInPoints = 30;
for (int c = 0; c < dt.Columns.Count; c++)
{
HSSFCell cell = (HSSFCell)rowColumns.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
cell.CellStyle = style;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//创建行
IRow row = sheet1.CreateRow(i + StartRow);
row.HeightInPoints = 30;
//填充数据
for (int j = 0; j < dt.Columns.Count; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j);
cell.SetCellValue(dr[j].ToString());
cell.CellStyle = style;
}
}
#endregion
ImportExcel(book, string.Format("{0}" + Name, DateTime.Now.ToString("yyyy-MM-dd")));
}
/// <summary>
/// 创建Excel标题行
/// </summary>
/// <param name="workbook">工作簿对象</param>
/// <param name="sheet">Excel单页</param>
/// <param name="strHeaderText">标题行文本</param>
/// <param name="dtSource">DataTable数据源</param>
public void CreateRowHeader(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, string strHeaderText, DataTable dtSource)
{
#region 表头及样式
{
Row headerRow = sheet.CreateRow(startRowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headStyle.WrapText = true;
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
int[] arrColWidth = GetColumnWidth(dtSource);
Row headerRow = sheet.CreateRow(startRowIndex+1);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = 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
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="dt"></param>
/// <param name="path"></param>
public static void ExportExcel(DataTable dt, string path)
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
hssfworkbook.SummaryInformation = si;
ISheet sheet1 = hssfworkbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);
IRow rowTitle = sheet1.CreateRow(0);
ICell cellTitle;
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int m = 0; m < dt.Columns.Count; m++)
{
cellTitle = rowTitle.CreateCell(m);
cellTitle.CellStyle = style;
cellTitle.SetCellValue(dt.Columns[m].ColumnName);
}
IRow row;
ICell cell;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = style;
string obj = dt.Rows[i][j].ToString();
cell.SetCellValue(obj);
}
}
}
var file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 為資料建立成NPOI 的Workbook和Sheet
/// </summary>
/// <param name="sourceTable">要匯出的內容</param>
/// <param name="workbook">The workbook.</param>
/// <param name="sheet">The sheet.</param>
/// <param name="headerRowIndex">標題欄的index起始位置</param>
/// <param name="rowIndexStart">第一筆實際資料的起始位置</param>
private void CreatWorksheetForSingleDataTable(DataTable sourceTable, ref HSSFWorkbook workbook,
ref ISheet sheet, int headerRowIndex = 0, int rowIndexStart = 1)
{
IRow headerRow = sheet.CreateRow(headerRowIndex);
// Set up the Header Coloumn cell style
var headerCellStyle = workbook.CreateCellStyle();
headerCellStyle.BorderTop = BorderStyle.Thin;
headerCellStyle.BorderBottom = BorderStyle.Thin;
headerCellStyle.Alignment = HorizontalAlignment.Center;
// Set up the Header Coloumn cell font style
var headerCellFontStyle = workbook.CreateFont();
headerCellFontStyle.Boldweight = (short)FontBoldWeight.Bold;
headerCellStyle.SetFont(headerCellFontStyle);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
var headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerCellStyle;
}
// handling value.
int rowIndex = rowIndexStart;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static byte[] TableToExcelForXLS(DataTable dt, string file)
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Data");
ICellStyle style = hssfworkbook.CreateCellStyle();
style.CloneStyleFrom(CellStyles[ExcelCellStyle.Style1]);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
cell.CellStyle = style;
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray();
return(buf);
////保存为Excel文件
//using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
//{
// fs.Write(buf, 0, buf.Length);
// fs.Flush();
//}
}
private static void CreateHeadRow(DataTable dt, HSSFWorkbook workbook, ISheet sheet)
{
ICellStyle titleStyle = workbook.CreateCellStyle();
var styleHeader = CreateHeaderStyle(workbook,
HSSFColor.WHITE.index, HSSFColor.GREEN.index);
IRow row = sheet.CreateRow(0);
ICell c1 = row.CreateCell(0);
ICell c2 = row.CreateCell(1);
ICell c3 = row.CreateCell(2);
int i = 0;
foreach (DataColumn cell in dt.Columns)
{
ICell iCell = row.CreateCell(i);
iCell.SetCellValue(cell.ColumnName);
iCell.CellStyle = styleHeader;
sheet.AutoSizeColumn(i);
i++;
}
}
private void btnExportToExcel_Click(object sender, RoutedEventArgs e)
{
SaveFileDialog sdfExport = new SaveFileDialog();
sdfExport.Filter = "Excel文件|*.xls";
if (sdfExport.ShowDialog() != true)
{
return;
}
string filename = sdfExport.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("员工数据");
IRow rowHeader = sheet.CreateRow(0);//表头行
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名");
rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号");
rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职日期");
//把查询结果导出到Excel
Employee[] employees = (Employee[])datagrid.ItemsSource;
for (int i = 0; i < employees.Length; i++)
{
Employee employee = employees[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name);
row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number);
ICellStyle styledate = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
//格式具体有哪些请看单元格右键中的格式，有说明
styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
ICell cellInDate = row.CreateCell(2, CellType.NUMERIC);
cellInDate.CellStyle = styledate;
cellInDate.SetCellValue(employee.InDate);
}
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
}
/// <summary>
/// 设置excel中单元格的样式
/// djin--2012-5-25
/// </summary>
/// <param name="hssfworkbook"></param>
/// <param name="options"></param>
/// <returns></returns>
public CellStyle setCellstyle(HSSFWorkbook hssfworkbook, string[] options)
{
CellStyle style3 = hssfworkbook.CreateCellStyle();
foreach (string s in options)
{
if (s == "Border")
{
style3.BorderBottom = CellBorderType.THIN;
style3.BorderLeft = CellBorderType.THIN;
style3.BorderRight = CellBorderType.THIN;
style3.BorderTop = CellBorderType.THIN;
}
if (s == "BorderNone")
{
style3.BorderBottom = CellBorderType.NONE;
style3.BorderLeft = CellBorderType.NONE;
style3.BorderRight = CellBorderType.NONE;
style3.BorderTop = CellBorderType.NONE;
}
if (s == "Left")
{
style3.VerticalAlignment = VerticalAlignment.CENTER;
style3.Alignment = HorizontalAlignment.LEFT;
}
if (s == "Right")
{
style3.VerticalAlignment = VerticalAlignment.CENTER;
style3.Alignment = HorizontalAlignment.RIGHT;
}
if (s == "DateTime")
{
DataFormat format = hssfworkbook.CreateDataFormat();
style3.DataFormat = format.GetFormat("yyyy-mm-dd");
}
}
Font font3 = hssfworkbook.CreateFont();
font3.FontHeightInPoints = 9;
style3.SetFont(font3);
return style3;
}
public void TestWriteSheetFont()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
".xls");
FileStream out1 = new FileStream(filepath,FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color=(NPOI.HSSF.Util.HSSFColor.Red.Index);
fnt.Boldweight= (short)FontBoldWeight.Bold;
cs.SetFont(fnt);
for (short rownum = ( short ) 0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
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));
}
private HSSFWorkbook NPOIHelper(List <ExportAllScoreViewModel> models)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
//sheet1.SetColumnWidth(0,100);
//sheet1.SetColumnWidth(1, 100);
//sheet1.SetColumnWidth(2, 100);
//sheet1.SetColumnWidth(3, 100);
//sheet1.SetColumnWidth(4, 100);
//sheet1.SetColumnWidth(5, 100);
//sheet1.SetColumnWidth(6, 100);
#region [头部设计]
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("序号");
row1.CreateCell(1).SetCellValue("作品名称");
row1.CreateCell(2).SetCellValue("作者姓名");
row1.CreateCell(3).SetCellValue("单位名称");
row1.CreateCell(4).SetCellValue("评审指标");
row1.CreateCell(8).SetCellValue("总分");
row1.CreateCell(9).SetCellValue("评审意见");
row1.Cells.ForEach(ce =>
{
ce.CellStyle = cellstyle;
});
//给sheet1添加第二行的头部标题
NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(1);
row2.CreateCell(4).SetCellValue("教学设计(25)");
row2.CreateCell(5).SetCellValue("教学行为(25)");
row2.CreateCell(6).SetCellValue("教学效果(25)");
row2.CreateCell(7).SetCellValue("创新与实用(25)");
row2.Cells.ForEach(ce =>
{
ce.CellStyle = cellstyle;
});
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0)); // 序号
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1)); // 作品名称
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 7));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 8, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 9, 9));
#endregion
var i = 0;
foreach (var item in models)
{
i++;
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(i);
rowtemp.CreateCell(1).SetCellValue(item.ResourceName);
rowtemp.CreateCell(2).SetCellValue(item.Author);
rowtemp.CreateCell(3).SetCellValue(item.AuthorCompany);
rowtemp.CreateCell(4).SetCellValue(item.Score1);
rowtemp.CreateCell(5).SetCellValue(item.Score2);
rowtemp.CreateCell(6).SetCellValue(item.Score3);
rowtemp.CreateCell(7).SetCellValue(item.Score4);
rowtemp.CreateCell(8).SetCellValue(item.Score);
rowtemp.CreateCell(9).SetCellValue(item.Comment);
}
NPOI.SS.UserModel.IRow rowtemp2 = sheet1.CreateRow(i + 3);
rowtemp2.CreateCell(1).SetCellValue("评审组:");
rowtemp2.CreateCell(2).SetCellValue(models.FirstOrDefault().GroupName);
rowtemp2.CreateCell(4).SetCellValue("评审专家:");
rowtemp2.CreateCell(8).SetCellValue("评审时间:");
return(book);
}
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList)
{
var first = orderItemList.FirstOrDefault();
//创建表格
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义样式
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 10;
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220);
// titleStyle
HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle();
//（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
titleStyle.FillForegroundColor = HSSFColor.Pink.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
titleStyle.BorderBottom = BorderStyle.Thin;
titleStyle.BorderLeft = BorderStyle.Thin;
titleStyle.BorderRight = BorderStyle.Thin;
titleStyle.BorderTop = BorderStyle.Thin;
titleStyle.SetFont(cellStyleFont);//设置字体
//headStyle
HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; //水平居中
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.SetFont(cellStyleFont);
//borderStyle
HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle();
borderStyle.BorderBottom = BorderStyle.Thin;
borderStyle.BorderLeft = BorderStyle.Thin;
borderStyle.BorderRight = BorderStyle.Thin;
borderStyle.BorderTop = BorderStyle.Thin;
#endregion
#region 表头
//设置单元格宽度
sheet.SetColumnWidth(0, 5000); //采购日期
sheet.SetColumnWidth(1, 5000); //门店名称
sheet.SetColumnWidth(2, 14000); //门店地址
sheet.SetColumnWidth(3, 4500); //采购数量
sheet.SetColumnWidth(4, 4500); //操作人
//表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(4);
row.Height = 350;
row.CreateCell(0).SetCellValue("采购日期");
row.CreateCell(1).SetCellValue("门店名称");
row.CreateCell(2).SetCellValue("门店地址");
row.CreateCell(3).SetCellValue("采购数量");
row.CreateCell(4).SetCellValue("操作人");
for (int i = 0; i < 5; i++)
{
row.Cells[i].CellStyle = titleStyle;
}
//合并单元格
CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region0);
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("冷链食品处理详情");
row0.Cells[0].CellStyle = headStyle;
var row0Cell4 = row0.CreateCell(4);
row0Cell4.CellStyle = headStyle;
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("处理商品批次码");
row1.CreateCell(1).SetCellValue(first.BatchNo.ToString());
row1.Cells[0].CellStyle = titleStyle;
IRow row2 = sheet.CreateRow(2);
row2.CreateCell(0).SetCellValue("处理日期");
row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd"));
row2.Cells[0].CellStyle = titleStyle;
IRow row3 = sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("处理人");
row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString());
row3.Cells[0].CellStyle = titleStyle;
//设置黑色边框
for (int i = 1; i < 4; i++)
{
var iRow = sheet.GetRow(i);
for (int j = 1; j < 5; j++)
{
var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j);
jCell.CellStyle = borderStyle;
}
}
#endregion
#region 循环数据
int index = 5;
foreach (var item in orderItemList)
{
IRow itemRow = sheet.CreateRow(index);
var rowCell0 = itemRow.CreateCell(0);
rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd"));
rowCell0.CellStyle = borderStyle;
var rowCell1 = itemRow.CreateCell(1);
rowCell1.SetCellValue(item.TargetStoreName.ToString());
rowCell1.CellStyle = borderStyle;
var rowCell2 = itemRow.CreateCell(2);
rowCell2.SetCellValue(item.TargetStoreAddress.ToString());
rowCell2.CellStyle = borderStyle;
var rowCell3 = itemRow.CreateCell(3);
rowCell3.SetCellValue(item.Number?.ToString());
rowCell3.CellStyle = borderStyle;
var rowCell4 = itemRow.CreateCell(4);
rowCell4.SetCellValue(item.ImportUserNames.ToString());
rowCell4.CellStyle = borderStyle;
index++;
}
#endregion
MemoryStream ms = new MemoryStream();
book.Write(ms);
return(ms);
}
void ExportAll(object filePath)
{
this.Invoke(new Action(() => { }));
lock (obj)
{
DataTable tables_1 = SqlHelper_1.GetTables();
DataTable tables_2 = SqlHelper_2.GetTables();
OrderedEnumerableRowCollection <DataRow> dataRows_1 = tables_1.AsEnumerable().OrderBy(a => a.Field <string>("name"));
OrderedEnumerableRowCollection <DataRow> dataRows_2 = tables_2.AsEnumerable().OrderBy(a => a.Field <string>("name"));
//创建Excel文件的对象
IWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
#region 设置表头样式
//创建样式对象
ICellStyle style = book.CreateCellStyle();
//创建一个字体样式对象
IFont font = book.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 16;
//水平居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection;
//将字体样式赋给样式对象
style.SetFont(font);
#endregion
#region 设置内容样式
//创建样式对象
ICellStyle style2 = book.CreateCellStyle();
//水平居中
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直居中
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection;
#endregion
foreach (DataRow row in dataRows_1)
{
string tablename = row["name"].ToString().Trim();
DataTable tableInfo_1 = SqlHelper_1.GetTableInfo(tablename);
DataTable tableInfo_2 = SqlHelper_2.GetTableInfo(tablename);
IEnumerable <DataRow> query = tableInfo_1.AsEnumerable().Except(tableInfo_2.AsEnumerable(), DataRowComparer.Default);
if (query.Count() > 0)
{
DataTable changesTable = query.CopyToDataTable();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(tablename);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
int c = 0;
foreach (DataColumn item in changesTable.Columns)
{
//设置宽度
sheet1.SetColumnWidth(c, 40 * 150);
ICell cell = row1.CreateCell(c);
cell.SetCellValue(item.Caption);
cell.CellStyle = style;
c++;
}
//将数据逐步写入sheet1各个行
int k = 0;
foreach (DataRow item in changesTable.Rows)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(k + 1);
for (int i = 0; i < changesTable.Columns.Count; i++)
{
ICell cell = rowtemp.CreateCell(i);
cell.SetCellValue(item[i].ToString());
cell.CellStyle = style2;
}
k++;
}
}
}
//写入文件
FileStream xlsfile = new FileStream(filePath.ToString(), FileMode.Create);
book.Write(xlsfile);
xlsfile.Dispose();
this.Invoke(new Action(() => { MessageBox.Show("导出成功，文件存放于桌面", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.None); }));
isFinish = true;
}
}
public static HSSFWorkbook Export <T>(List <T> Objs, string[] headerList)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Double;
style.BorderLeft = BorderStyle.Double;
style.BorderRight = BorderStyle.Double;
style.BorderTop = BorderStyle.Double;
style.BottomBorderColor = HSSFColor.Grey50Percent.Index;
style.LeftBorderColor = HSSFColor.Grey50Percent.Index;
style.RightBorderColor = HSSFColor.Grey50Percent.Index;
style.TopBorderColor = HSSFColor.Grey50Percent.Index;
IFont font = book.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "標楷體";
font.Color = HSSFColor.White.Index;
style.SetFont(font);//HEAD 样式
//row.CreateCell(0).SetCellValue(getString(syscomment, "Company") + "\n" + getString(syscomment, "Report"));
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 80 * 5;
for (int i = 0; i < headerList.Length; i++)
{
row1.CreateCell(i).SetCellValue(headerList[i]);
row1.GetCell(i).CellStyle = style;
if (i == 0)
{
sheet1.SetColumnWidth(i, 22 * 256);
}
if (i > 0)
{
sheet1.SetColumnWidth(i, 30 * 256);
}
}
int m = 0;
//将数据逐步写入sheet1各个行
foreach (T model in Objs)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(m + 1);
int j = 0;
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
rowtemp.CreateCell(j).SetCellValue(double.Parse(propertyInfo.GetValue(model, null).ToString()));
j++;
}
m++;
}
return(book);
}
public static HSSFWorkbook Export(DataTable dt, string[] headerList, string[] headercode)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = true;
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Double;
style.BorderLeft = BorderStyle.Double;
style.BorderRight = BorderStyle.Double;
style.BorderTop = BorderStyle.Double;
style.BottomBorderColor = HSSFColor.Grey50Percent.Index;
style.LeftBorderColor = HSSFColor.Grey50Percent.Index;
style.RightBorderColor = HSSFColor.Grey50Percent.Index;
style.TopBorderColor = HSSFColor.Grey50Percent.Index;
IFont font = book.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "標楷體";
font.Color = HSSFColor.Black.Index;
style.SetFont(font);//HEAD 样式
//貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 80 * 5;
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.Alignment = HorizontalAlignment.Left;
cellstyle.VerticalAlignment = VerticalAlignment.Top;
cellstyle.WrapText = true;
for (int i = 0; i < headerList.Length; i++)
{
row1.CreateCell(i).SetCellValue(headerList[i]);
row1.GetCell(i).CellStyle = style;
if (i == 0)
{
sheet1.SetColumnWidth(i, 22 * 256);
}
if (i > 0)
{
sheet1.SetColumnWidth(i, 30 * 256);
}
}
//定义第二个工作簿防止内容溢出报错问题
NPOI.SS.UserModel.ISheet sheet2 = null;
if (dt.Rows.Count > 65535)
{
sheet2 = book.CreateSheet("Sheet2");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row11 = sheet2.CreateRow(0);
row11.Height = 80 * 5;
ICellStyle cellstyle1 = book.CreateCellStyle();
cellstyle1.Alignment = HorizontalAlignment.Left;
cellstyle1.VerticalAlignment = VerticalAlignment.Top;
cellstyle1.WrapText = true;
for (int i = 0; i < headerList.Length; i++)
{
row11.CreateCell(i).SetCellValue(headerList[i]);
row11.GetCell(i).CellStyle = style;
if (i == 0)
{
sheet2.SetColumnWidth(i, 22 * 256);
}
if (i > 0)
{
sheet2.SetColumnWidth(i, 30 * 256);
}
}
}
int k = 0;
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i > 65534)
{
NPOI.SS.UserModel.IRow rowtemp = sheet2.CreateRow(k);
rowtemp.HeightInPoints = 65;
for (int j = 0; j < headerList.Length; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][headercode[j]].ToString());
rowtemp.GetCell(j).CellStyle = cellstyle;
}
k++;
}
else
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.HeightInPoints = 65;
for (int j = 0; j < headerList.Length; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][headercode[j]].ToString());
rowtemp.GetCell(j).CellStyle = cellstyle;
}
}
}
return(book);
}
/// <summary>
/// 导出问卷数据
/// </summary>
/// <param name="list"></param>
public string CreateExcel()
{
list = (from r in _db.tbl_record
join emp in _db.tbl_employeeMK on new { EId = r.eId.Value } equals new { EId = emp.eId }
join ans in
(
(from a in _db.tbl_answer
group a by new
{
a.rId
} into g
select new
{
arid = g.Key.rId.Value,
answer1 = g.Max(p => (p.qId == 1 ? p.aContent : null)),
answer2 = g.Max(p => (p.qId == 2 ? p.aContent : null)),
answer3 = g.Max(p => (p.qId == 3 ? p.aContent : null)),
answer4 = g.Max(p => (p.qId == 4 ? p.aContent : null)),
answer5 = g.Max(p => (p.qId == 5 ? p.aContent : null)),
answer6 = g.Max(p => (p.qId == 6 ? p.aContent : null)),
answer7 = g.Max(p => (p.qId == 7 ? p.aContent : null)),
answer8 = g.Max(p => (p.qId == 8 ? p.aContent : null)),
answer9 = g.Max(p => (p.qId == 9 ? p.aContent : null)),
answer10 = g.Max(p => (p.qId == 10 ? p.aContent : null)),
answer11 = g.Max(p => (p.qId == 11 ? p.aContent : null)),
answer12 = g.Max(p => (p.qId == 12 ? p.aContent : null))
}))on new { RId = r.rId } equals new { RId = ans.arid }
orderby
r.rId
select new RecordModel
{
AnswerId = r.rId,
AnswerName = emp.eName,
AnswerNumber = emp.eNumber,
AnswerPhone = emp.ePhone,
AnswerDuration = r.rDuration.Value,
AnswerTime = r.rEndtime.Value,
AnswerAnswer1 = ans.answer1,
AnswerAnswer2 = ans.answer2,
AnswerAnswer3 = ans.answer3,
AnswerAnswer4 = ans.answer4,
AnswerAnswer5 = ans.answer5,
AnswerAnswer6 = ans.answer6,
AnswerAnswer7 = ans.answer7,
AnswerAnswer8 = ans.answer8,
AnswerAnswer9 = ans.answer9,
AnswerAnswer10 = ans.answer10,
AnswerAnswer11 = ans.answer11,
AnswerAnswer12 = ans.answer12,
}
).ToList();
if (list != null && list.Count > 0) //
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("问卷数据下载");
//
NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0);
sheet.SetColumnWidth(0, 12 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 18 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 25 * 256);
ICellStyle style = book.CreateCellStyle();
HSSFColor color = new HSSFColor.BLACK();
FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;//灰色背景
HSSFColor backGround = new HSSFColor.GREY_25_PERCENT();
IFont font = ExportData.GetFontStyle(book, "宋体", color, 11);
//设置单元格的样式：水平对齐居中
style = ExportData.GetCellStyle(book, font, fillPattern, backGround, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
ICell cell1 = rowHeader.CreateCell(0);
cell1.SetCellValue("姓名");
//将新的样式赋给单元格
cell1.CellStyle = style;
ICell cell2 = rowHeader.CreateCell(1);
cell2.SetCellValue("编号");
cell2.CellStyle = style;
ICell cell3 = rowHeader.CreateCell(2);
cell3.SetCellValue("手机号码");
cell3.CellStyle = style;
ICell cell4 = rowHeader.CreateCell(3);
cell4.SetCellValue("第1题");
cell4.CellStyle = style;
ICell cell5 = rowHeader.CreateCell(4);
cell5.SetCellValue("第2题");
cell5.CellStyle = style;
ICell cell6 = rowHeader.CreateCell(5);
cell6.SetCellValue("第3题");
cell6.CellStyle = style;
ICell cell7 = rowHeader.CreateCell(6);
cell7.SetCellValue("第4题");
cell7.CellStyle = style;
ICell cell8 = rowHeader.CreateCell(7);
cell8.SetCellValue("第5题");
cell8.CellStyle = style;
ICell cell9 = rowHeader.CreateCell(8);
cell9.SetCellValue("第6题");
cell9.CellStyle = style;
ICell cell10 = rowHeader.CreateCell(9);
cell10.SetCellValue("第7题");
cell10.CellStyle = style;
ICell cell11 = rowHeader.CreateCell(10);
cell11.SetCellValue("第8题");
cell11.CellStyle = style;
ICell cell12 = rowHeader.CreateCell(11);
cell12.SetCellValue("第9题");
cell12.CellStyle = style;
ICell cell13 = rowHeader.CreateCell(12);
cell13.SetCellValue("第10题");
cell13.CellStyle = style;
ICell cell14 = rowHeader.CreateCell(13);
cell14.SetCellValue("第11题");
cell14.CellStyle = style;
ICell cell15 = rowHeader.CreateCell(14);
cell15.SetCellValue("第12题");
cell15.CellStyle = style;
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont rowfont = book.CreateFont();
rowfont.FontName = "宋体";
rowfont.FontHeightInPoints = (short)11;
rowfont.Color = color.GetIndex();
cellStyle.SetFont(rowfont);
//
NPOI.SS.UserModel.IRow row = null;
for (int i = 0; i < list.Count; i++)
{
RecordModel model = list[i];
row = sheet.CreateRow(i + 1);
ICell rowcell1 = row.CreateCell(0);
rowcell1.SetCellValue(model.AnswerName);
rowcell1.CellStyle = cellStyle;
ICell rowcell2 = row.CreateCell(1);
rowcell2.SetCellValue(model.AnswerNumber);
rowcell2.CellStyle = cellStyle;
ICell rowcell3 = row.CreateCell(2);
rowcell3.SetCellValue(model.AnswerPhone);
rowcell3.CellStyle = cellStyle;
ICell rowcell4 = row.CreateCell(3);
rowcell4.SetCellValue(model.AnswerAnswer1);
rowcell4.CellStyle = cellStyle;
ICell rowcell5 = row.CreateCell(4);
rowcell5.SetCellValue(model.AnswerAnswer2);
rowcell5.CellStyle = cellStyle;
ICell rowcell6 = row.CreateCell(5);
rowcell6.SetCellValue(model.AnswerAnswer3);
rowcell6.CellStyle = cellStyle;
ICell rowcell7 = row.CreateCell(6);
rowcell7.SetCellValue(model.AnswerAnswer4);
rowcell7.CellStyle = cellStyle;
ICell rowcell8 = row.CreateCell(7);
rowcell8.SetCellValue(model.AnswerAnswer5);
rowcell8.CellStyle = cellStyle;
ICell rowcell9 = row.CreateCell(8);
rowcell9.SetCellValue(model.AnswerAnswer6);
rowcell9.CellStyle = cellStyle;
ICell rowcell10 = row.CreateCell(9);
rowcell10.SetCellValue(model.AnswerAnswer7);
rowcell10.CellStyle = cellStyle;
ICell rowcell11 = row.CreateCell(10);
rowcell11.SetCellValue(model.AnswerAnswer8);
rowcell11.CellStyle = cellStyle;
ICell rowcell12 = row.CreateCell(11);
rowcell12.SetCellValue(model.AnswerAnswer9);
rowcell12.CellStyle = cellStyle;
ICell rowcell13 = row.CreateCell(12);
rowcell13.SetCellValue(model.AnswerAnswer10);
rowcell13.CellStyle = cellStyle;
ICell rowcell14 = row.CreateCell(13);
rowcell14.SetCellValue(model.AnswerAnswer11);
rowcell14.CellStyle = cellStyle;
ICell rowcell15 = row.CreateCell(14);
rowcell15.SetCellValue(model.AnswerAnswer12);
rowcell15.CellStyle = cellStyle;
}
//
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
//string path = @"C:\Users\DNS\Desktop\导出参会人";
//string path = System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\数据导出Excel";
// path = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\数据导出Excel";
string path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Data\\";
DirectoryInfo excelDir = new DirectoryInfo(path);
//如果路径不存在就创建
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = "mk_q" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//string strDate = DateTime.Now.ToString("yyyy-MM-dd-HH ");
path = excelDir.FullName + fileName;
if (File.Exists(path))
{
File.Delete(path);
}
using (Stream localFile = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
//ms.ToArray()转换为字节数组就是想要的图片源字节
localFile.Write(ms.ToArray(), 0, (int)ms.Length);
}
book = null;
ms.Close();
ms.Dispose();
//
string webAddress = System.Configuration.ConfigurationManager.AppSettings["mkDataPath"].ToString();
return(webAddress + fileName);
}
}
catch (Exception)
{
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导出格式错误');</script>");
return("Error1");
}
}
else
{
return("Error2");
}
}
public ExportEquipmentCategoryColumnTemplateCommand.Result Handle(ExportEquipmentCategoryColumnTemplateCommand command)
{
var category = _repository.Get <EquipmentCategory>(command.CategoryId);
var columns = category.Columns;
string fileName = $"{category.Name}设备分类模板-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称
string urlPath = "Attachments/CategoryTemplate/" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径
string directoryName = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryName))
{
if (directoryName != null)
{
Directory.CreateDirectory(directoryName);
}
}
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow rowHeader = sheet1.CreateRow(0);
rowHeader.CreateCell(0).SetCellValue("ID"); // 表头列名
rowHeader.CreateCell(1).SetCellValue("设备分类"); // 表头列名
rowHeader.CreateCell(2).SetCellValue("生产厂商");
rowHeader.CreateCell(3).SetCellValue("批次");
rowHeader.CreateCell(4).SetCellValue("产品小类");
rowHeader.CreateCell(5).SetCellValue("产品名称");
rowHeader.CreateCell(6).SetCellValue("产品编码");
rowHeader.CreateCell(7).SetCellValue("规格型号");
rowHeader.CreateCell(8).SetCellValue("材质");
rowHeader.CreateCell(9).SetCellValue("技术人员");
rowHeader.CreateCell(10).SetCellValue("物资人员");
rowHeader.CreateCell(11).SetCellValue("领料人");
rowHeader.CreateCell(12).SetCellValue("出厂日期");
rowHeader.CreateCell(13).SetCellValue("检测人员");
rowHeader.CreateCell(14).SetCellValue("检测结果");
rowHeader.CreateCell(15).SetCellValue("产品执行标准");
rowHeader.CreateCell(16).SetCellValue("安装位置");
var dateIndexes = new List <int>();
for (int i = 0; i < columns.Count; i++)
{
var cellStartIndex = i + 17;
rowHeader.CreateCell(cellStartIndex).SetCellValue(columns[i].ColumnName); // 表头列名
if (columns[i].ColumnType == EquipmentCategoryColumnType.日期.ToString())
{
dateIndexes.Add(cellStartIndex);
}
}
IRow row1 = sheet1.CreateRow(1);
row1.CreateCell(0).SetCellValue(category.Id);
row1.CreateCell(1).SetCellValue(category.Name);
foreach (var dateIndex in dateIndexes)
{
var cell = row1.CreateCell(dateIndex);
//设置单元格格式
HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
style.DataFormat = format.GetFormat("yyyy年mm月dd日");
cell.CellStyle = style;
}
// 4.生成文件
FileStream file = new FileStream(filePath, FileMode.Create);
book.Write(file);
file.Close();
return(new ExportEquipmentCategoryColumnTemplateCommand.Result
{
IsSucceed = true,
UrlPath = urlPath,
});
}
public FileResult OrderToExecl()
{
//取查询条件
var orderNo = Request.Get("OrderNo");
var checkOrderNo = string.IsNullOrWhiteSpace(orderNo);
var consignee = Request.Get("Consignee");
var checkConsignee = string.IsNullOrWhiteSpace(consignee);
var memberName = Request.Get("MemberName");
var checkMemberName = string.IsNullOrWhiteSpace(memberName);
var orderStatus = Request.Get("OrderStatus");
var checkOrderStatus = string.IsNullOrWhiteSpace(orderStatus);
var orderStatusInt = orderStatus.To <int>();
var refundStatus = Request.Get("RefundStatus");
var checkRefundStatus = string.IsNullOrWhiteSpace(refundStatus);
var refundStatusInt = refundStatus.To <int>();
var payStatus = Request.Get("PayStatus");
var checkPayStatus = string.IsNullOrWhiteSpace(payStatus);
var payStatusInt = payStatus.To <int>();
var shippingStatus = Request.Get("ShippingStatus");
var checkShippingStatus = string.IsNullOrWhiteSpace(shippingStatus);
var shippingStatusInt = shippingStatus.To <int>();
var paymentId = Request.Get("PaymentId");
var checkPaymentId = string.IsNullOrWhiteSpace(paymentId);
var createTimeBegin = Request.Get("CreateTimeBegin");
var checkCreateTimeBegin = string.IsNullOrWhiteSpace(createTimeBegin);
var createTimeBeginTime = createTimeBegin.To <DateTime>();
var createTimeEnd = Request.Get("CreateTimeEnd");
var checkCreateTimeEnd = string.IsNullOrWhiteSpace(createTimeEnd);
var createTimeEndTime = createTimeEnd.To <DateTime>();
Expression <Func <Order, bool> > expression =
l => (checkOrderNo || l.OrderNo.Contains(orderNo)) &&
(checkConsignee || l.Consignee.Contains(consignee)) &&
l.OrderStatus != OrderStatus.Deleted &&
(checkOrderStatus || (int)l.OrderStatus == orderStatusInt) &&
(checkRefundStatus || (int)l.RefundStatus == refundStatusInt) &&
(checkPayStatus || (int)l.PayStatus == payStatusInt) &&
(checkShippingStatus || (int)l.ShippingStatus == shippingStatusInt) &&
(checkCreateTimeBegin || l.CreateTime >= createTimeBeginTime) &&
(checkCreateTimeEnd || l.CreateTime <= createTimeEndTime) &&
(checkPaymentId || l.PaymentId.ToString() == paymentId) &&
(checkMemberName || l.MemberName.Contains(memberName));
var list = _orderService.GetList(expression).Select(o => new ViewModels.SimpleOderModel(o));
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
if (list.Any())
{
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("订单号");
row1.CreateCell(1).SetCellValue("商品名称");
row1.CreateCell(2).SetCellValue("规格");
row1.CreateCell(3).SetCellValue("数量");
row1.CreateCell(4).SetCellValue("下单时间");
row1.CreateCell(5).SetCellValue("收货人");
row1.CreateCell(6).SetCellValue("收货地址");
row1.CreateCell(7).SetCellValue("联系电话");
row1.CreateCell(8).SetCellValue("商品总价");
row1.CreateCell(9).SetCellValue("物流费用");
row1.CreateCell(10).SetCellValue("积分折抵");
row1.CreateCell(11).SetCellValue("应付金额");
row1.CreateCell(12).SetCellValue("订单状态");
var i = 0;
var cs = book.CreateCellStyle();
cs.WrapText = true;
foreach (var item in list)
{
string n = "";
string m = "";
string k = "";
for (int j = 0; j < item.OrderGoods.Count; j++)
{
if (j == item.OrderGoods.Count - 1)
{
n += item.OrderGoods[j].GoodsName;
m += item.OrderGoods[j].GoodsAttribute;
k += item.OrderGoods[j].Quantity + item.OrderGoods[j].Unit;
}
else
{
n += item.OrderGoods[j].GoodsName + "\n";
m += item.OrderGoods[j].GoodsAttribute + "\n";
k += item.OrderGoods[j].Quantity + item.OrderGoods[j].Unit + "\n";
}
}
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(item.OrderNo);
rowtemp.CreateCell(1).SetCellValue(n);
rowtemp.CreateCell(2).SetCellValue(m);
rowtemp.CreateCell(3).SetCellValue(k);
if (item.OrderGoods.Count > 1)
{
rowtemp.GetCell(1).CellStyle = cs;
rowtemp.GetCell(2).CellStyle = cs;
rowtemp.GetCell(3).CellStyle = cs;
}
rowtemp.CreateCell(4).SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", item.CreateTime));
rowtemp.CreateCell(5).SetCellValue(item.Consignee);
rowtemp.CreateCell(6).SetCellValue(item.RegionName + item.Address);
rowtemp.CreateCell(7).SetCellValue(item.Tel);
rowtemp.CreateCell(8).SetCellValue(item.GoodsAmount.ToString("#0.00"));
rowtemp.CreateCell(9).SetCellValue(item.ShippingFee.ToString("#0.00"));
rowtemp.CreateCell(10).SetCellValue(item.IntegralMoney.ToString("#0.00"));
rowtemp.CreateCell(11).SetCellValue(item.PayFee.ToString("#0.00"));
var statusName = item.OrderStatus.Description();
if (item.RefundStatus > 0)
{
statusName += "(" + item.RefundStatus.Description() + ")";
}
if (item.EvaluateStatus > 0)
{
statusName += "(" + item.EvaluateStatus.Description() + ")";
}
rowtemp.CreateCell(12).SetCellValue(statusName);
i++;
}
}
// 写入到客户端
var ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
var dt = DateTime.Now;
var dateTime = dt.ToString("yyMMddHHmmssfff");
var fileName = "订单列表" + dateTime + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult ExportList(AdminCredential User, int eid, int v)
{
var view = SystemSetService.View.GetViewItem(v).Data;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(view.Title);
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 430;
ICellStyle style = workbook.CreateCellStyle();
// style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
// style.FillPattern = FillPattern.SolidForeground;
//style.BorderTop = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderBottom = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
// IFont font = workbook.CreateFont(); //创建一个字体样式对象
// font.FontHeightInPoints = 10;//字体大小
// font.FontName = "宋体"; //和excel里面的字体对应
// font.Boldweight = short.MaxValue;//字体加粗
// font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
// style.SetFont(font); //将字体样式赋给样式对象
var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(view.FieldList);
int i = 0;
foreach (var item in FieldList)
{
ICell cell = row1.CreateCell(i);
cell.SetCellValue(item.Title);
cell.CellStyle = style;
i++;
}
style.Alignment = HorizontalAlignment.Left;
DataTable dt = SystemSetService.View.GetViewExportData(view.Sql, new Pagination()
{
Page = 1, PageSize = 999999, vid = v, eid = eid
}, User);
//将数据逐步写入sheet1各个行
for (int z = 0; z < dt.Rows.Count; z++)
{
var row = dt.Rows[z];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
int c = 0;
foreach (var item in FieldList)
{
var _value = row[item.Field].ToString();
//if (ConvertHelper.IsDateTime(_value))
//{
// if (ConvertHelper.ToDouble(_value, 0) == 0)
// {
// _value = ConvertHelper.ToDateTime(_value).ToString("yyyy-MM-dd HH:mm:ss");
// }
//}
var cell = rowtemp.CreateCell(c++);
cell.CellStyle = style;
cell.SetCellValue(_value);
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", view.Title + ".xls"));
}
static public int Deport(System.Windows.Forms.DataGridView mView, int startIndex)
{
try
{
IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet mSheet = mWorkbook.CreateSheet("Sheet1");
ICellStyle styleRight = mWorkbook.CreateCellStyle();
//ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
//IDataFormat format = mWorkbook.CreateDataFormat();
styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//NumberStyle.DataFormat = format.GetFormat("0.00");
ICell mCell;
int i, j;
int x = 0;
//DataGridView mView = dgvReport;
//添加标头
IRow mRow = mSheet.CreateRow(0);
for (i = startIndex; i <= mView.Columns.Count - 1; i++)
{
if (mView.Columns[i].Visible == true)
{
mCell = mRow.CreateCell(x);
x++;
mCell.SetCellValue(mView.Columns[i].HeaderText);
mCell.CellStyle = styleRight;
}
}
//添加内容
for (i = 1; i <= mView.RowCount; i++)
{
mRow = mSheet.CreateRow(i);
x = 0;
for (j = startIndex; j <= mView.Columns.Count - 1; j++)
{
if (mView.Columns[j].Visible == true)
{
mCell = mRow.CreateCell(x);
x++;
if (Classes.PubFunc.IsNumber(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)) == true)
{
mCell.SetCellValue(Convert.ToDouble(mView.Rows[i - 1].Cells[j].Value));
}
else
{
mCell.SetCellValue(Convert.ToString(mView.Rows[i - 1].Cells[j].Value));
}
mCell.CellStyle = styleRight;
}
}
}
//创建文件
SaveFileDialog mDialog = new SaveFileDialog();
mDialog.AddExtension = true;
mDialog.DefaultExt = "xls";
mDialog.Filter = "Excel Worksheets(*.xls)|*.xls";
mDialog.InitialDirectory = System.Environment.CurrentDirectory;
mDialog.FileName = "导出数据-" + DateTime.Now.ToString("yyyyMMddhhmmss");
if (mDialog.ShowDialog() == DialogResult.OK)
{
Stream mFile = mDialog.OpenFile();
mWorkbook.Write(mFile);
mFile.Close();
MessageBox.Show("保存成功！", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
return(0);
}
static public int InvoiceDeport(System.Windows.Forms.DataGridView mView)
{
try
{
IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet mSheet = mWorkbook.CreateSheet("Sheet1");
ICellStyle styleRight = mWorkbook.CreateCellStyle();
//ICellStyle NumberStyle = mWorkbook.CreateCellStyle();
//IDataFormat format = mWorkbook.CreateDataFormat();
styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
styleRight.WrapText = true;
ICell mCell;
int i;
//添加标头
IRow mRow = mSheet.CreateRow(0);
int m = 0;
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("公司名称");
mSheet.SetDefaultColumnStyle(m, styleRight);//设置样式
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("总金额");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("发票类型");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("开票日期");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("商品名称");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("单价");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("数量");
mSheet.SetDefaultColumnStyle(m, styleRight);
mCell = mRow.CreateCell(m++);
mCell.SetCellValue("金额");
mSheet.SetDefaultColumnStyle(m, styleRight);
//添加内容
int mExcelRowCount = 1;
for (i = 0; i <= mView.RowCount - 1; i++)
{
if (Convert.ToBoolean(mView.Rows[i].Cells["ColCombine"].Value) == true)
{
mRow = mSheet.CreateRow(mExcelRowCount++);
string InvoiceContent = string.Empty;
//公司名称
mCell = mRow.CreateCell(0);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["customerNameDataGridViewTextBoxColumn"].Value));
//总金额
mCell = mRow.CreateCell(1);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceMoneyDataGridViewTextBoxColumn"].Value));
//发票类型
mCell = mRow.CreateCell(2);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceTypeDataGridViewTextBoxColumn"].Value));
//开票日期
mCell = mRow.CreateCell(3);
mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceDateDataGridViewTextBoxColumn"].Value));
//开票内容
InvoiceContent = Convert.ToString(mView.Rows[i].Cells["invoiceContentDataGridViewTextBoxColumn"].Value);
string[] contents = System.Text.RegularExpressions.Regex.Split(InvoiceContent, "!#!");
for (int n = 0; n < contents.Length - 3; n += 4)
{
mCell = mRow.CreateCell(4);
mCell.SetCellValue(contents[n]);
mCell = mRow.CreateCell(5);
mCell.SetCellValue(contents[n + 1]);
mCell = mRow.CreateCell(6);
mCell.SetCellValue(contents[n + 2]);
mCell = mRow.CreateCell(7);
mCell.SetCellValue(contents[n + 3]);
if (n < contents.Length - 7)
{
mRow = mSheet.CreateRow(mExcelRowCount++);
}
}
mSheet.CreateRow(mExcelRowCount++);
}
}
//创建文件
SaveFileDialog mDialog = new SaveFileDialog();
mDialog.AddExtension = true;
mDialog.DefaultExt = "xls";
mDialog.Filter = "Excel Worksheets(*.xls)|*.xls";
mDialog.InitialDirectory = System.Environment.CurrentDirectory;
mDialog.FileName = "开票-" + DateTime.Now.ToString("yyyyMMddhhmmss");
if (mDialog.ShowDialog() == DialogResult.OK)
{
Stream mFile = mDialog.OpenFile();
mWorkbook.Write(mFile);
mFile.Close();
MessageBox.Show("保存成功！", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
return(0);
}
public /*ActionResult*/ FileContentResult About()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义表头
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)54, (byte)96, (byte)146);
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
///（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//设置黑色边框
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.Color = HSSFColor.White.Index;
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 11;
cellStyle.SetFont(cellStyleFont);
//设置单元格宽度
sheet.SetColumnWidth(0, 4000); //核销人
sheet.SetColumnWidth(1, 4000); //账号
sheet.SetColumnWidth(2, 5000); //核销时间
sheet.SetColumnWidth(3, 3500);
sheet.SetColumnWidth(4, 3500);
sheet.SetColumnWidth(5, 4000);
sheet.SetColumnWidth(6, 7000);
sheet.SetColumnWidth(7, 3500); //全面值
sheet.SetColumnWidth(8, 4500); //使用门槛
sheet.SetColumnWidth(9, 5000); //核销门店
sheet.SetColumnWidth(10, 10000); //appid
sheet.SetColumnWidth(11, 3500); //核销地址
sheet.SetColumnWidth(12, 3500); //核销id
// 第一行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 350;
row.CreateCell(0).SetCellValue("核销人");
row.CreateCell(1).SetCellValue("核销人账号");
row.CreateCell(2).SetCellValue("核销时间");
row.CreateCell(3).SetCellValue("订单金额");
row.CreateCell(4).SetCellValue("核销金额");
row.CreateCell(5).SetCellValue("核销券码");
row.CreateCell(6).SetCellValue("有效期");
row.CreateCell(7).SetCellValue("券面值");
row.CreateCell(8).SetCellValue("使用门槛");
row.CreateCell(9).SetCellValue("核销门店");
row.CreateCell(10).SetCellValue("核销门店APPID");
row.CreateCell(11).SetCellValue("核销地址");
row.CreateCell(12).SetCellValue("核销ID");
for (int i = 0; i < 13; i++)
{
//设置高度
row.Height = 400;
row.Cells[i].CellStyle = cellStyle;
}
#endregion
//合并单元格
/**
* 第一个参数：从第几行开始合并
* 第二个参数：到第几行结束合并
* 第三个参数：从第几列开始合并
* 第四个参数：到第几列结束合并
**/
CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
sheet.AddMergedRegion(region);
HSSFCellStyle cellStyleItem = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem.Alignment = HorizontalAlignment.Center; //水平居中
cellStyleItem.VerticalAlignment = VerticalAlignment.Center; //垂直居中
HSSFCellStyle cellStyleItem2 = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
for (int i = 1; i < 100; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
row2.CreateCell(0).SetCellValue("收银员小吴");
row2.CreateCell(1).SetCellValue("13587639864");
row2.CreateCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd HH:mm"));
row2.CreateCell(3).SetCellValue("1000");
row2.CreateCell(4).SetCellValue("180");
row2.CreateCell(5).SetCellValue("DJD84K6JFU");
row2.CreateCell(6).SetCellValue($"{DateTime.Now.ToString("yyyy-MM-dd")} ~ {DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}");
row2.CreateCell(7).SetCellValue("60");
row2.CreateCell(8).SetCellValue("满100元可用");
row2.CreateCell(9).SetCellValue("世纪新园中餐厅");
row2.CreateCell(10).SetCellValue(System.Guid.NewGuid().ToString());
var ignoreList = new List <int> {
5, 8, 9, 10
};
for (int j = 0; j < 11; j++)
{
if (ignoreList.Contains(j))
{
continue;
}
row2.Cells[j].CellStyle = cellStyleItem;
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//Response.ContentType = "application/octet-stream";
//Response.AppendHeader("content-disposition", $"attachment;filename={DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls;");
//Response.BinaryWrite(ms.ToArray());
//book = null;
//ms.Close();
//ms.Dispose();
//return null;
return(File(ms.ToArray(), "application/octet-stream", string.Format("冷链食品处理详情{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
public void ExportToExcel(int page, int size, int supplierType, int supplierId, string supplierName, string stockCode, string stockName, int country, int market, string enable)
{
// Get the data to report on
var masters = _service.ListCondition(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable);
var details = _service.ListConditionDetailExcel(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
currencyCellStyle.DataFormat = formatId;
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
detailCurrencySubtotalCellStyle.DataFormat = formatId;
#endregion
#endregion
#region Master sheet
var sheet = workbook.CreateSheet("Supplier");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Supplier Id");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Supplier Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Address");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("City");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Country");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Phone 1");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Phone 2");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Mobile");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Fax");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Email");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Contact");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(13);
cell.SetCellValue("Created Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(14);
cell.SetCellValue("Created By");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(15);
cell.SetCellValue("Modified Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(16);
cell.SetCellValue("Modified By");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
var no = 1;
foreach (var master in masters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(no);
row.CreateCell(1).SetCellValue(master.Id);
row.CreateCell(2).SetCellValue(master.Name);
row.CreateCell(3).SetCellValue(master.Type);
row.CreateCell(4).SetCellValue(master.Address);
row.CreateCell(5).SetCellValue(master.City);
row.CreateCell(6).SetCellValue(master.Country);
row.CreateCell(7).SetCellValue(master.Phone);
row.CreateCell(8).SetCellValue(master.Phone_2);
row.CreateCell(9).SetCellValue(master.Mobile);
row.CreateCell(10).SetCellValue(master.Fax);
row.CreateCell(11).SetCellValue(master.Email);
row.CreateCell(12).SetCellValue(master.Contact);
row.CreateCell(13).SetCellValue(master.Created_Date != null
? master.Created_Date.Value.ToString("dd/MM/yyyy")
: master.Created_Date.ToString());
row.CreateCell(14).SetCellValue(master.Created_By);
row.CreateCell(15).SetCellValue(master.Modified_Date != null
? master.Modified_Date.Value.ToString("dd/MM/yyyy")
: master.Modified_Date.ToString());
row.CreateCell(16).SetCellValue(master.Modified_By);
rowIndex++;
no++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
#region Detail sheet
sheet = workbook.CreateSheet("Product");
#region Add header labels
rowIndex = 0;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("Supplier");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Stock Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Stock Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Stock Type");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Unit");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Category");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Part No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Ral No");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("Color");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
#endregion
// Add data rows
var lastProductName = string.Empty;
// For sum in excel
// var startRowIndexForProductDetails = 1;
foreach (var detail in details)
{
// Show a summary row for each new product
var productNameToShow = string.Empty;
if (string.Compare(detail.Supplier_Name, lastProductName) != 0)
{
if (!string.IsNullOrEmpty(lastProductName))
{
// Add the subtotal row
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 3;
}
productNameToShow = detail.Supplier_Name;
lastProductName = detail.Supplier_Name;
// startRowIndexForProductDetails = rowIndex;
}
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(productNameToShow);
row.CreateCell(1).SetCellValue(detail.Stock_Code);
row.CreateCell(2).SetCellValue(detail.Stock_Name);
row.CreateCell(3).SetCellValue(detail.Type);
row.CreateCell(4).SetCellValue(detail.Unit);
row.CreateCell(5).SetCellValue(detail.Category);
row.CreateCell(6).SetCellValue(detail.Part_No);
row.CreateCell(7).SetCellValue(detail.Ral_No);
row.CreateCell(8).SetCellValue(detail.Color);
rowIndex++;
}
// Add the subtotal row for the last product
// AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle);
// rowIndex += 2;
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
// sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("Supplier-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
/// <summary>
/// 根据模板创建新的文件
/// </summary>
/// <param name="lst"></param>
private static void CreatReport(List <Model> lst)
{
#region 加载xls文件
//模板文件路径
string path = @"C:\Users\1\Desktop\日报\实收款项明细表.xls";
FileStream fs_modle;
using (fs_modle = new FileStream(path, FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook_model = new NPOI.HSSF.UserModel.HSSFWorkbook(fs_modle);
// fs_modle.Close();
ISheet sheet_model = workbook_model.GetSheetAt(0);
IRow row_hj = sheet_model.GetRow(7);//合计行
//设置单元格时
ICellStyle style = workbook_model.CreateCellStyle();
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.WrapText = true;
style.DataFormat = 4;
style.VerticalAlignment = VerticalAlignment.Center;
IRow r;
for (int i = 6; i < lst.Count + 6; i++)
{
r = sheet_model.CreateRow(i); //创建一新行
r.HeightInPoints = 25; //设置行高
for (int J = 0; J < 14; J++)
{
switch (J)
{
case 0:
r.CreateCell(J).SetCellValue(lst[i - 6].序号);
break;
case 1:
r.CreateCell(J).SetCellValue(lst[i - 6].项目名称);
break;
case 2:
r.CreateCell(J).SetCellValue(lst[i - 6].楼栋名称);
break;
case 3:
r.CreateCell(J).SetCellValue(lst[i - 6].房号);
break;
case 4:
r.CreateCell(J).SetCellValue(lst[i - 6].客户名称);
break;
case 5:
r.CreateCell(J).SetCellValue(lst[i - 6].收款日期.ToString());
break;
case 6:
r.CreateCell(J).SetCellValue(lst[i - 6].票据类型);
break;
case 7:
r.CreateCell(J).SetCellValue(lst[i - 6].票据编号);
break;
case 8:
r.CreateCell(J).SetCellValue(lst[i - 6].款项类型);
break;
case 9:
r.CreateCell(J).SetCellValue(lst[i - 6].款项名称);
break;
case 10:
r.CreateCell(J).SetCellValue(lst[i - 6].金额);
break;
case 11:
r.CreateCell(J).SetCellValue(lst[i - 6].支付方式);
break;
case 12:
r.CreateCell(J).SetCellValue(lst[i - 6].银付方式);
break;
case 13:
r.CreateCell(J).SetCellValue(lst[i - 6].摘要);
break;
default:
break;
}
}
var enu = r.GetEnumerator();
while (enu.MoveNext())
{
enu.Current.CellStyle = style;//设置风格
}
}
//创建合计行
IRow r1 = sheet_model.CreateRow(lst.Count + 6);
r1.HeightInPoints = 25;//设置行高
ICell cell;
IFont font = workbook_model.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
for (int i = 0; i < 14; i++)
{
cell = r1.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue("--");
cell.CellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle.SetFont(font);
}
double JE = lst.Sum(t => t.金额);
r1.GetCell(0).SetCellValue(" ");
r1.GetCell(1).SetCellValue("合计");
r1.GetCell(10).SetCellValue(JE);
//统计周期和统计时间
string ZQ = $"统计周期：{DateTime.Now.ToShortDateString()} 至 {DateTime.Now.ToShortDateString()}";
string DT = $"制表日期：{DateTime.Now.ToShortDateString()}";
sheet_model.GetRow(3).GetCell(0).SetCellValue(ZQ);
sheet_model.GetRow(4).GetCell(0).SetCellValue(DT);
string newFileFullPath = $@"{ Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)}";
string filePath = $"{newFileFullPath}\\日报\\日报表{DateTime.Now.ToShortDateString()}.xls";
try
{
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
{
workbook_model.Write(fs);
}
// fs.Dispose();
Console.WriteLine("文件创建成功!");
// workbook_model.Close();
lst.Clear();
}
catch (Exception)
{
throw;
}
finally
{
Console.WriteLine(File.Exists(filePath));
Process.Start(@"C:\Program Files (x86)\Kingsoft\WPS Office\11.1.0.9912\office6\wps.exe", filePath);
}
}
#endregion
}
