public HSSFWorkbook GenerateExcelToStream(JArray param)
{
//Excel
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sheet1 = (HSSFSheet)wb1.CreateSheet("Sheet1");
HSSFRow row;
int dataStrIdx = 3;
try
{
IFont fTitle = wb1.CreateFont();
fTitle.Boldweight = (short)FontBoldWeight.Bold;
fTitle.FontHeightInPoints = 16;
IFont fHeader = wb1.CreateFont();
fHeader.FontHeightInPoints = 10;
ICellStyle sTitle = wb1.CreateCellStyle();
sTitle.SetFont(fTitle);
sTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
sTitle.VerticalAlignment = VerticalAlignment.Center;
ICellStyle sHeader = wb1.CreateCellStyle();
sHeader.SetFont(fHeader);
sHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
sHeader.VerticalAlignment = VerticalAlignment.Bottom;
sHeader.WrapText = true;
sHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
sHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
sHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
sHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle sDL = wb1.CreateCellStyle();
sDL.SetFont(fHeader);
sDL.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
sDL.VerticalAlignment = VerticalAlignment.Center;
sDL.WrapText = true;
sDL.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
sDL.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
sDL.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
sDL.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle sDC = wb1.CreateCellStyle();
sDC.SetFont(fHeader);
sDC.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
sDC.VerticalAlignment = VerticalAlignment.Center;
sDC.WrapText = true;
sDC.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
sDC.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
sDC.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
sDC.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle sDR = wb1.CreateCellStyle();
sDR.SetFont(fHeader);
sDR.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
sDR.VerticalAlignment = VerticalAlignment.Center;
sDR.WrapText = true;
sDR.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
sDR.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
sDR.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
sDR.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
for (int i = 0; i < columnCnt; i++)
{
sheet1.SetColumnWidth(i, logBookHeaderSizes[i]);
}
/*
* IDataFormat dataFormatCustom = workbook.CreateDataFormat();
* cell.CellStyle = styles["cell"];
* cell.CellStyle.DataFormat = dataFormatCustom.GetFormat("yyyyMMdd HH:mm:ss");
*/
row = (HSSFRow)sheet1.CreateRow(0);
row.Height = 800;
row.CreateCell(0);
row.Cells[0].CellStyle = sTitle;
row.Cells[0].SetCellValue("Transaction LogBook");
row = null;
row = (HSSFRow)sheet1.CreateRow(1);
row.Height = 300;
row = null;
row = (HSSFRow)sheet1.CreateRow(2);
row.Height = 1400;
for (int c = 0; c < columnCnt; c++)
{
ICell nCell = row.CreateCell(c);
nCell.SetCellValue(logBookHeaderTexts[c]);
nCell.CellStyle = sHeader;
}
for (int r = 0; r < param.Count; r++)
{
JObject json = (JObject)param[r];
row = null;
row = (HSSFRow)sheet1.CreateRow(dataStrIdx + r);
for (int c = 0; c < columnCnt; c++)
{
row.CreateCell(c);
switch (c)
{
case 0:
row.Cells[c].CellStyle = sDC;
row.Cells[c].SetCellValue(Util.Utils.FormatDate(json["INVOICE_DATE"].ToString(), 2));
break;
case 1:
row.Cells[c].CellStyle = sDL;
row.Cells[c].SetCellValue(json["RCT_NO"].ToString());
break;
case 2:
row.Cells[c].CellStyle = sDR;
row.Cells[c].SetCellValue(double.Parse(json["GST_AMT"].ToString()));
break;
case 3:
row.Cells[c].CellStyle = sDL;
row.Cells[c].SetCellValue(Util.Utils.FormatDocId(json["DOC_ID"].ToString()));
break;
case 4:
row.Cells[c].CellStyle = sDC;
row.Cells[c].SetCellValue(Util.Utils.FormatDate(json["ISSUE_DATE"].ToString(), 2));
break;
case 5:
row.Cells[c].CellStyle = sDC;
row.Cells[c].SetCellValue(Util.Utils.FormatTime(json["ISSUE_TIME"].ToString(), "12"));
break;
case 6:
row.Cells[c].CellStyle = sDC;
row.Cells[c].SetCellValue(json["PASSPORT_NO"].ToString());
break;
case 7:
row.Cells[c].CellStyle = sDL;
row.Cells[c].SetCellValue(json["STAFF_NAME"].ToString());
break;
case 8:
row.Cells[c].CellStyle = sDL;
break;
case 9:
row.Cells[c].CellStyle = sDC;
string status = json["STATUS_DESC"].ToString();
if (status.Equals("Voided"))
{
row.Cells[c].SetCellValue(status + " (" + Util.Utils.FormatDate(json["VOID_DATE"].ToString(), 2) + ")");
}
else
{
row.Cells[c].SetCellValue(status);
}
break;
case 10:
row.Cells[c].CellStyle = sDC;
row.Cells[c].SetCellValue((json["SEAL_BAG_YN"].ToString() == "Y" ? "Yes" : "No"));
break;
case 11:
row.Cells[c].CellStyle = sDC;
row.Cells[c].SetCellValue((json["PACKED_YN"].ToString() == "Y" ? "Yes" : "No"));
break;
case 12:
row.Cells[c].CellStyle = sDL;
row.Cells[c].SetCellValue(json["SEAL_BAG_NO"].ToString());
break;
}
}
}
return(wb1);
}
catch (Exception ex)
{
Constants.LOGGER_MAIN.Error(ex.Message);
throw ex;
}
}
/// <summary>
/// 实现导出功能
/// </summary>
public string Export()
{
string flag = string.Empty;
#region 变量声明
string[] tableTitle = this._header.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
// 表头数组
string[] newHeaders = null;
// 数据字段
string[] files = this._filed.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
// 数据字段
string[] file = null;
// 临时数组
string[] temp = null;
// 临时表头
string tempHeader = string.Empty;
// 工作薄名称
string[] sheetNames = this._sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
// 表头名称
string[] titles = this._title == null ? null : this._title.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
// 获取行数
int rows = GetRowCount(this._header);
// 列数计数器
int cols = 0;
// 列头跨行数
int rowSpans = 0;
// 列头跨列数
int colSpans = GetColCount(this._header);
// HSSFSheet 对象
HSSFSheet sheet = null;
// IRow 对象
IRow row = null;
// 表头行添加
int trow = (string.IsNullOrEmpty(this._title) ? 0 : 1);
DataTable dt;
#endregion
#region 单元格样式
ICellStyle style = _workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center; //居中
style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
style.WrapText = true; //自动换行
// 边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
// 字体
IFont font = _workbook.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "宋体";
style.SetFont(font);
ICellStyle titleType = _workbook.CreateCellStyle();
titleType.Alignment = HorizontalAlignment.Center; //居中
titleType.VerticalAlignment = VerticalAlignment.Center; //垂直居中
titleType.WrapText = true; //自动换行
// 边框
titleType.BorderBottom = BorderStyle.Thin;
titleType.BorderLeft = BorderStyle.Thin;
titleType.BorderRight = BorderStyle.Thin;
titleType.BorderTop = BorderStyle.Thin;
IFont font2 = _workbook.CreateFont();
font2.FontHeightInPoints = 14;
font2.FontName = "宋体";
font2.Boldweight = (short)FontBoldWeight.Bold;
titleType.SetFont(font2);
#endregion
// 表格绘制
for (int k = 0; k < sheetNames.Length; k++)
{
#region 表头绘制
newHeaders = tableTitle[k].Split(new char[] { '#' }, StringSplitOptions.RemoveEmptyEntries);
sheet = (HSSFSheet)_workbook.CreateSheet(sheetNames[k]);
for (int m = 0; m < rows + trow; m++) // 创建行
{
if (m == 0 && trow > 0)
{
row = sheet.CreateRow(0);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, colSpans - 1);
sheet.AddMergedRegion(region);
row.CreateCell(0).SetCellValue(titles[k]);
row.GetCell(0).CellStyle = titleType;
row.Height = 20 * 20;
continue;
}
cols = 0;
for (int i = 0; i < newHeaders.Length; i++) // 创建列
{
tempHeader = newHeaders[i];
// 获取列头跨行数
rowSpans = GetRowSpan(tempHeader, rows);
// 获取列头跨列数
colSpans = GetColSpan(tempHeader);
// 如果表头还可以划分
temp = tempHeader.Split(new char[] { ' ' });
if (temp.Length == rows)
{
tempHeader = temp[m - trow];
}
else
{
tempHeader = temp[0];
}
if (1 == rowSpans)
{
// 获取行
row = sheet.GetRow(m);
if (row == null)
{
row = sheet.CreateRow(m);
}
// 未跨列
if (1 == colSpans)
{
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
}
else // 跨列
{
temp = tempHeader.Split(new char[] { ',' });
if (temp.Length > 1)
{
for (int j = 0; j < temp.Length; j++)
{
row.CreateCell(j + cols).SetCellValue(temp[j]);
row.GetCell(j + cols).CellStyle = style;
}
}
else
{
// 创建范围
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(m, m, cols, cols + colSpans - 1);
sheet.AddMergedRegion(region);
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
}
cols += colSpans - 1;
}
}
else if (rowSpans > 1 && m < 2)
{
// 获取行
row = sheet.GetRow(m);
if (row == null)
{
row = sheet.CreateRow(m);
}
// 未跨列
if (1 == colSpans)
{
// 创建范围
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(m, rowSpans - 1 + trow, cols, cols);
sheet.AddMergedRegion(region);
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
}
else
{
// 创建范围
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
CellRangeAddress region = new CellRangeAddress(m, rowSpans - 1 + trow, cols, cols + colSpans - 1);
sheet.AddMergedRegion(region);
row.CreateCell(cols).SetCellValue(tempHeader);
row.GetCell(cols).CellStyle = style;
cols += colSpans - 1;
}
}
// 列计数器
cols += 1;
}
}
#endregion
#region 数据源
int rowIndex = rows + trow;
foreach (DataRow dr in this._dsSource.Tables[k].Rows)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in this._dsSource.Tables[k].Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
string drValue = dr[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
//newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
#endregion
}
#region 数据导出
// WEB导出
if (1 == this._exportMode)
{
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
//设置下载的Excel文件名
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", this._fileName));
using (MemoryStream ms = new MemoryStream())
{
//将工作簿的内容放到内存流中
_workbook.Write(ms);
//将内存流转换成字节数组发送到客户端
System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
System.Web.HttpContext.Current.Response.End();
_workbook = null;
}
}
else if (2 == this._exportMode)
{
try
{
////设置新建文件路径及名称
string savePath = this._filePath;
//创建文件
FileStream fs = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
_workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
fs.Write(bytes, 0, bytes.Length);
fs.Flush();
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
fs.Close();
fs.Dispose();
_workbook.Close();
sheet = null;
_workbook = null;
flag = "ok";
}
catch (Exception ex)
{
flag = ex.Message;
}
//using (FileStream fs = File.Open(this._filePath, FileMode.Append))
//{
// _workbook.Write(fs);
// _workbook = null;
//}
}
#endregion
return(flag);
}
/// <summary>
/// 设置单元格的字体的颜色和大小和字体格式
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="color">颜色</param>
/// <param name="size">大小</param>
/// <param name="wb"></param>
public static void SetCellFont(int row, int col, CellFontColor color, CellFontSize size, CellFontName fontName, ref HSSFWorkbook wb)
{
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
ICellStyle Style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
switch (color)
{
case CellFontColor.black: font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
break;
case CellFontColor.blue: font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
break;
case CellFontColor.green: font.Color = NPOI.HSSF.Util.HSSFColor.Green.Index;
break;
case CellFontColor.red: font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
break;
case CellFontColor.white: font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
break;
case CellFontColor.yellow: font.Color = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
break;
}
switch (size)
{
case CellFontSize.s10: font.FontHeightInPoints = 10;
break;
case CellFontSize.s11: font.FontHeightInPoints = 11;
break;
case CellFontSize.s12: font.FontHeightInPoints = 12;
break;
case CellFontSize.s14: font.FontHeightInPoints = 14;
break;
case CellFontSize.s16: font.FontHeightInPoints = 16;
break;
case CellFontSize.s18: font.FontHeightInPoints = 18;
break;
case CellFontSize.s20: font.FontHeightInPoints = 20;
break;
case CellFontSize.s24: font.FontHeightInPoints = 24;
break;
}
switch (fontName)
{
case CellFontName.SongTi: font.FontName = "宋体"; break;
case CellFontName.TimesNewRoman: font.FontName = "Times New Roman"; break;
}
// font.Boldweight = 700; // 设置粗体
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
Style.SetFont(font);
t_cell.CellStyle = Style;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
Style.SetFont(font);
t_cell.CellStyle = Style;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
Style.SetFont(font);
t_cell.CellStyle = Style;
}
}
}
private void button2_Click(object sender, EventArgs e)
{
DateTime dt = DateTime.Now; //获取日期
/*****建立输入流的 workbookIn 对象 *****/
IWorkbook workbookIn = null; //新建Workbook对象
string filename = textBox1.Text;
FileStream fileStream = new FileStream(textBox1.Text, FileMode.Open, FileAccess.Read);
if (filename.IndexOf(".xlsx") > 0) //判断是否为2007版本
{
workbookIn = new XSSFWorkbook(fileStream); //xlsx数据写入workbookIn
}
else if (filename.IndexOf(".xls") > 0) //判断是否为2003版本
{
workbookIn = new HSSFWorkbook(fileStream); //xls数据写入workbookIn
}
/*****建立输出流的 workbookIn 对象 --- workbookOut*****/
HSSFWorkbook workbookOut = new HSSFWorkbook(); // 建立输出流的 workbookOut 用于输出文件
workbookOut.CreateSheet("Sheet1"); // 建立新的表单 名称： Sheet1
HSSFSheet sheetNew = (HSSFSheet)workbookOut.GetSheet("Sheet1"); // 获取 名称：Sheet1 的工作表
/* 修复 分页预览 无分页的情况*/
sheetNew.FitToPage = false;
try
{
ISheet sheet = workbookIn.GetSheetAt(0); //获取第一个工作表
IRow row; // = sheet.GetRow(0); //新建当前工作表行数据
int newRow = 0;
for (int i = 3; i <= sheet.LastRowNum; i++) //对工作表每一行
{
Console.WriteLine(i);
row = sheet.GetRow(i); //row读入第i行数据
if (row != null)
{
for (int j = 0; j < row.LastCellNum; j++) //对工作表每一列
{
string cellValue = row.GetCell(j).ToString(); //获取i行j列数据
if ((cellValue != "") && (cellValue != "TRUE")) //获取数据 获取条件 非空 TRUE 作为截止信号
{
sheetNew.CreateRow(newRow); //从店铺开始 每次创建一行
HSSFRow sheetRow = (HSSFRow)sheetNew.GetRow(newRow); // 获取新的行 作为对象
HSSFCell[] sheetCell = new HSSFCell[4]; // 每行建立四个列
sheetCell[0] = (HSSFCell)sheetRow.CreateCell(0); // 建立 列[0] 用于 填写店名
sheetCell[1] = (HSSFCell)sheetRow.CreateCell(1); // 建立 列[1] 用于 填写 货品名称
sheetCell[2] = (HSSFCell)sheetRow.CreateCell(2); // 建立 列[2] 用于 填写 货品单位
sheetCell[3] = (HSSFCell)sheetRow.CreateCell(3); // 建立 列[3] 用于 填写 货品数量
if (j == 0) // 输入流 从第四行开始 每列第一位为 店铺名称 故做一个判断
{
sheetCell[0].SetCellValue(cellValue); //填写 店名
sheetCell[1].SetCellValue("品名"); //填写 品名
sheetCell[2].SetCellValue("单位"); //填写 单位
sheetCell[3].SetCellValue("数量"); //填写 数量
Console.WriteLine($"{cellValue} 品名 单位 数量");
newRow++; // 行数增加 进入下一行
}
else
{
IRow row2 = sheet.GetRow(1); // 重新定义一个 行 迭代器
string cellName = row2.GetCell(j).ToString(); // 新的行迭代器 用于获取 商品名称 cellName 商品名称
sheetCell[1].SetCellValue(cellName); // 填写 货品名称
IRow row3 = sheet.GetRow(2); // 重新定义一个 行 迭代器
string cellUnit = row3.GetCell(j).ToString(); // 新的行迭代器 用于获取 商品单位 cellUnit 商品单位
sheetCell[2].SetCellValue(cellUnit); // 填写 货品单位
Double price = Convert.ToDouble(cellValue); // 字符串 转换为 32位浮点型数
sheetCell[3].SetCellValue(price); // 填写 货品数量
Console.WriteLine($"{cellName} {cellUnit} {cellValue}"); //控制台校验
newRow++;
}
}
}
}
sheetNew.CreateRow(newRow); // 新建一行 分开每家店铺
newRow++;
}
FileStream fileOut = new FileStream(textBox3.Text + @"\\" + string.Format("{0:M}", dt) + @".xls", FileMode.Create); // 将输出路径改为非固定项 地址值由 选择地址文本窗口传来
fileStream.Close();
workbookIn.Close();
MessageBox.Show("今日日期:" + string.Format("{0:M}", dt) + "\n文件地址:" + textBox3.Text + "\n您的订单已完成，表单正在打开中，请稍等..."); // 设计添加新弹框
//MessageBoxButtons.OK.ToString("OK");
textBox2.Text = "今日日期:" + string.Format("{0:M}", dt) + "\n您的订单已完成，表单正在打开中，请稍等...";
System.Diagnostics.Process.Start(textBox3.Text); // 将打开的 输出路径改为非固定项 地址值由 选择地址文本窗口传来
//Console.ReadKey();
workbookOut.Write(fileOut);
workbookOut.Close();
Application.Exit(); // 关闭程序 避免程序占用 输出文件
}
catch (IOException ex)
{
Console.WriteLine(ex.StackTrace);
}
}
/// <summary>
/// 将一个源文件的某个单元格的值复制到目标文件的指定单元格
/// 以富文本的形式，保留原格式
/// </summary>
/// <param name="dst_row">目标行</param>
/// <param name="dst_col">目标列</param>
/// <param name="src_row">源行</param>
/// <param name="src_col">源列</param>
/// <param name="dst">目标文件</param>
/// <param name="src">源文件</param>
/// <returns></returns>
public static bool CopyCell(int dst_row, int dst_col, int src_row, int src_col, ref HSSFSheet dst, ref HSSFSheet src)
{
if (src.GetRow(src_row - 1) == null)
{
return(false);
}
else
{
HSSFRow t_src_row = (HSSFRow)src.GetRow(src_row - 1);
if (t_src_row.GetCell(src_col - 1) == null)
{
return(false);
}
else
{
HSSFCell t_src_cell = (HSSFCell)t_src_row.GetCell(src_col - 1);
if (dst.GetRow(dst_row - 1) == null)
{
HSSFRow t_row = (HSSFRow)dst.CreateRow(dst_row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(dst_col - 1);
t_cell.CellStyle = t_src_cell.CellStyle;
IRichTextString t = t_src_cell.RichStringCellValue;
t_cell.SetCellValue(t);
}
else
{
HSSFRow t_row = (HSSFRow)dst.GetRow(dst_row - 1);
if (t_row.GetCell(dst_col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(dst_col - 1);
t_cell.CellStyle = t_src_cell.CellStyle;
IRichTextString t = t_src_cell.RichStringCellValue;
t_cell.SetCellValue(t);
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(dst_col - 1);
t_cell.CellStyle = t_src_cell.CellStyle;
IRichTextString t = t_src_cell.RichStringCellValue;
t_cell.SetCellValue(t);
}
}
}
}
return(true);
}
/// <summary>
/// 设置列宽 width = width*256
/// </summary>
/// <param name="col"></param>
/// <param name="width"></param>
/// <param name="sheet"></param>
public static void SetColWidth(int col, int width, ref HSSFSheet sheet)
{
sheet.SetColumnWidth(col - 1, width);
}
public static void ExportDT(DataTable dtSource, string filename)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.huobanplus.com";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "杭州火图科技有限公司"; //填加xls文件作者信息
si.ApplicationName = "火淘助手"; //填加xls文件创建程序信息
si.LastAuthor = "hot"; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (arrColWidth[column.Ordinal] > 255)
{
arrColWidth[column.Ordinal] = 254;
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
#endregion
//rowIndex = 2;
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "东青信息";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Allen"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//边框
dateStyle.BorderBottom = BorderStyle.Thin;
dateStyle.BorderLeft = BorderStyle.Thin;
dateStyle.BorderRight = BorderStyle.Thin;
dateStyle.BorderTop = BorderStyle.Thin;
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = HorizontalAlignment.Center;
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 20;
//font.Boldweight = 700;
//headStyle.SetFont(font);
//headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = HorizontalAlignment.Center;
//边框
//headStyle.BorderBottom = BorderStyle.Thin;
//headStyle.BorderLeft = BorderStyle.Thin;
//headStyle.BorderRight = BorderStyle.Thin;
//headStyle.BorderTop = BorderStyle.Thin;
HSSFFont font = (HSSFFont)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);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
#region 边框,样式
//HSSFCellStyle NewCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//NewCellStyle.Alignment = HorizontalAlignment.Center;
//边框
//NewCellStyle.BorderBottom = BorderStyle.Thin;
//NewCellStyle.BorderLeft = BorderStyle.Thin;
//NewCellStyle.BorderRight = BorderStyle.Thin;
//NewCellStyle.BorderTop = BorderStyle.Thin;
#endregion
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
//newCell.CellStyle = NewCellStyle;
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
//newCell.CellStyle = NewCellStyle;
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
// newCell.CellStyle = NewCellStyle;
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
// newCell.CellStyle = NewCellStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
// newCell.CellStyle = NewCellStyle;
break;
default:
newCell.SetCellValue("");
// newCell.CellStyle = NewCellStyle;
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// NPOI DataGridView 导出 EXCEL
/// </summary>
/// <param name="fileName"> 默认保存文件名</param>
/// <param name="dgv">DataGridView</param>
/// <param name="fontname">字体名称</param>
/// <param name="fontsize">字体大小</param>
public static void ExportExcel(DataGridView dgv, string fontname, short fontsize)
{
//检测是否有数据
if (dgv.Rows.Count == 0)
{
return;
}
//创建主要对象
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight");
//设置字体，大小，对齐方式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = fontname;
font.FontHeightInPoints = fontsize;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //居中对齐
//添加表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
if (dgv.Columns[i].Visible)
{
dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.GetCell(i).CellStyle = style;
}
}
//注释的这行是设置筛选的
//sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count));
//添加列及内容
for (int i = 0; i < dgv.Rows.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (dgv.Columns[j].Visible)
{
string ValueType = dgv.Rows[i].Cells[j].Value.GetType( ).ToString( );
string Value = dgv.Rows[i].Cells[j].Value.ToString( );
switch (ValueType)
{
case "System.String": //字符串类型
dataRow.CreateCell(j).SetCellValue(Value);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(Value, out dateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(Value, out boolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(Value, out intV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(Value, out doubV);
dataRow.CreateCell(j).SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
dataRow.CreateCell(j).SetCellValue("");
break;
default:
dataRow.CreateCell(j).SetCellValue("");
break;
}
dataRow.GetCell(j).CellStyle = style;
//设置宽度
sheet.SetColumnWidth(j, (Value.Length + 10) * 256);
}
else
{
sheet.SetColumnHidden(j, true);
}
}
}
//保存文件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
MemoryStream ms = new MemoryStream();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件被站用，请关闭文件 " + saveFileName);
workbook = null;
ms.Close();
ms.Dispose();
return;
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show(saveDialog.FileName + " 保存成功", "提示", MessageBoxButtons.OK);
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
}
protected internal HSSFIconMultiStateFormatting(CFRule12Record cfRule12Record, HSSFSheet sheet)
{
this.sheet = sheet;
this.cfRule12Record = cfRule12Record;
this.iconFormatting = this.cfRule12Record.MultiStateFormatting;
}
Dictionary <string, ConfigFile> ReadXML(string path)
{
//path = Application.dataPath + "/../Builder/builder_config.xls";
Dictionary <string, ConfigFile> ret = new Dictionary <string, ConfigFile>();
// !< 说明:客户端的表读取,就不走服务器那样的复杂处理啦..
HSSFWorkbook wk = new HSSFWorkbook(File.OpenRead(path));
HSSFSheet sheet = null;
string sheetname = "";
for (int a = 0; a < wk.NumberOfSheets; ++a)
{
sheet = wk.GetSheetAt(a) as HSSFSheet;
sheetname = wk.GetSheetName(a);
if (sheet == null)
{
Debug.LogError("异常!" + wk.GetFullName());
continue;
}
if (ret.ContainsKey(sheetname))
{
Debug.LogError("该excel表的sheet名字重复了：" + wk.GetSheetName(a));
}
else
{
ConfigFile result = new ConfigFile(wk.GetFullName() + "_" + sheetname);
List <String> header = new List <string>();
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
if (row == null)
{
continue;
}
ArrayList line = new ArrayList();
for (int j = row.FirstCellNum; j <= row.LastCellNum; ++j)
{
HSSFCell cell = row.GetCell(j) as HSSFCell;
if (cell == null)
{
continue;
}
if (i == sheet.FirstRowNum)
{
header.Add(cell.ToString());
}
else
{
line.Add(cell.ToString());
}
}
if (i == sheet.FirstRowNum)
{
result.SetTitles(header.ToArray());
}
else
{
if (line.Count > 0)
{
result.AddData(line[0] as String, line);
}
else
{
// !< 读完了?
break;
}
}
}
ret.Add(sheetname, result);
}
}
return(ret);
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <returns></returns>
static DataTable ImportDt(HSSFSheet sheet, int HeaderRowIndex, bool needHeader)
{
DataTable table = new DataTable();
HSSFRow headerRow;
int cellCount;
try
{
if (HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex) as HSSFRow;
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
HSSFRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i) as HSSFRow;
}
else
{
row = sheet.GetRow(i) as HSSFRow;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.STRING:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.FORMULA:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.STRING:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.NUMERIC:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.BOOLEAN:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.ERROR:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
return(table);
}
public void TestCreateCF()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
String formula = "7";
HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
HSSFBorderFormatting bordFmt = (HSSFBorderFormatting)rule1.CreateBorderFormatting();
bordFmt.BorderBottom = BorderFormatting.BORDER_THIN;
bordFmt.BorderTop = BorderFormatting.BORDER_THICK;
bordFmt.BorderLeft = BorderFormatting.BORDER_DASHED;
bordFmt.BorderRight = BorderFormatting.BORDER_DOTTED;
HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (HSSFColor.YELLOW.index);
HSSFConditionalFormattingRule rule2 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(ComparisonOperator.BETWEEN, "1", "2");
HSSFConditionalFormattingRule[] cfRules =
{
rule1, rule2
};
short col = 1;
CellRangeAddress[] regions =
{
new CellRangeAddress(0, 65535, col, col)
};
sheetCF.AddConditionalFormatting(regions, cfRules);
sheetCF.AddConditionalFormatting(regions, cfRules);
// Verification
Assert.AreEqual(2, sheetCF.NumConditionalFormattings);
sheetCF.RemoveConditionalFormatting(1);
Assert.AreEqual(1, sheetCF.NumConditionalFormattings);
HSSFConditionalFormatting cf = (HSSFConditionalFormatting)sheetCF.GetConditionalFormattingAt(0);
Assert.IsNotNull(cf);
regions = cf.GetFormattingRanges();
Assert.IsNotNull(regions);
Assert.AreEqual(1, regions.Length);
CellRangeAddress r = regions[0];
Assert.AreEqual(1, r.FirstColumn);
Assert.AreEqual(1, r.LastColumn);
Assert.AreEqual(0, r.FirstRow);
Assert.AreEqual(65535, r.LastRow);
Assert.AreEqual(2, cf.NumberOfRules);
rule1 = (HSSFConditionalFormattingRule)cf.GetRule(0);
Assert.AreEqual("7", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
HSSFFontFormatting r1fp = (HSSFFontFormatting)rule1.GetFontFormatting();
Assert.IsNotNull(r1fp);
Assert.IsTrue(r1fp.IsItalic);
Assert.IsFalse(r1fp.IsBold);
HSSFBorderFormatting r1bf = (HSSFBorderFormatting)rule1.GetBorderFormatting();
Assert.IsNotNull(r1bf);
Assert.AreEqual(BorderFormatting.BORDER_THIN, r1bf.BorderBottom);
Assert.AreEqual(BorderFormatting.BORDER_THICK, r1bf.BorderTop);
Assert.AreEqual(BorderFormatting.BORDER_DASHED, r1bf.BorderLeft);
Assert.AreEqual(BorderFormatting.BORDER_DOTTED, r1bf.BorderRight);
HSSFPatternFormatting r1pf = (HSSFPatternFormatting)rule1.GetPatternFormatting();
Assert.IsNotNull(r1pf);
Assert.AreEqual(HSSFColor.YELLOW.index, r1pf.FillBackgroundColor);
rule2 = (HSSFConditionalFormattingRule)cf.GetRule(1);
Assert.AreEqual("2", rule2.Formula2);
Assert.AreEqual("1", rule2.Formula1);
}
private HSSFChart(HSSFSheet sheet, ChartRecord chartRecord) {
this.chartRecord = chartRecord;
this.sheet = sheet;
}
/* package */
//public HSSFSheetConditionalFormatting(HSSFWorkbook workbook, InternalSheet sheet)
//{
// _workbook = workbook;
// _conditionalFormattingTable = sheet.ConditionalFormattingTable;
//}
public HSSFSheetConditionalFormatting(HSSFSheet sheet)
{
_sheet = sheet;
_conditionalFormattingTable = sheet.Sheet.ConditionalFormattingTable;
}
public void GenerateMaterials(ProjectInfo projectInfo, List <DXFData> dxfData)
{
hssfSheet = (HSSFSheet)workBook.GetSheet("Materials");
hssfSheet.GetRow(4).CreateCell(1).SetCellValue(projectInfo.projectName);
hssfSheet.GetRow(5).CreateCell(1).SetCellValue(projectInfo.projectNumber);
hssfSheet.GetRow(6).CreateCell(1).SetCellValue(projectInfo.generalContractor);
hssfSheet.GetRow(7).CreateCell(1).SetCellValue(projectInfo.workParcel);
List <int> numXF10 = new List <int>();
List <int> numXF20 = new List <int>();
List <int> numXF30 = new List <int>();
for (int i = 0; i < dxfData.Count; i++)
{
if (dxfData[i].NO_OF_STRANDS <= 3)
{
numXF10.Add(dxfData[i].NO_OF_TYPES * dxfData[i].NO_OF_LIVES);
numXF20.Add(0);
numXF30.Add(0);
}
else if (dxfData[i].NO_OF_STRANDS > 3 && dxfData[i].NO_OF_STRANDS != 6)
{
numXF10.Add(0);
numXF20.Add(dxfData[i].NO_OF_TYPES * dxfData[i].NO_OF_LIVES);
numXF30.Add(0);
}
else if (dxfData[i].NO_OF_STRANDS == 6)
{
numXF10.Add(0);
numXF20.Add(0);
numXF30.Add(dxfData[i].NO_OF_TYPES * dxfData[i].NO_OF_LIVES);
}
}
hssfSheet.GetRow(10).GetCell(0).SetCellValue(numXF10.Sum());
hssfSheet.GetRow(11).GetCell(0).SetCellFormula("A11");
hssfSheet.GetRow(12).GetCell(0).SetCellFormula("A11");
hssfSheet.GetRow(13).GetCell(0).SetCellFormula("A11");
hssfSheet.GetRow(14).GetCell(0).SetCellValue(numXF20.Sum());
hssfSheet.GetRow(15).GetCell(0).SetCellFormula("A15");
hssfSheet.GetRow(16).GetCell(0).SetCellFormula("A15");
hssfSheet.GetRow(17).GetCell(0).SetCellFormula("A15");
hssfSheet.GetRow(18).GetCell(0).SetCellValue(numXF30.Sum());
hssfSheet.GetRow(19).GetCell(0).SetCellFormula("A19");
hssfSheet.GetRow(20).GetCell(0).SetCellFormula("A19");
hssfSheet.GetRow(21).GetCell(0).SetCellFormula("A19");
List <double> length6mX20 = new List <double>();
List <double> length6mX30 = new List <double>();
List <double> length6mX10 = new List <double>();
//List<double> numXF20 = new List<double>();
//List<double> numXF30 = new List<double>();
for (int i = 0; i < dxfData.Count; i++)
{
if (dxfData[i].NO_OF_LIVES == 1)
{
length6mX20.Add(numXF20[i] * (dxfData[i].LENGTH - 1.3));
length6mX30.Add(numXF30[i] * (dxfData[i].LENGTH - 1.3));
length6mX10.Add(numXF10[i] * (dxfData[i].LENGTH - 1.3));
}
else
{
length6mX20.Add((numXF20[i] * dxfData[i].LENGTH) / 2.0);
length6mX30.Add((numXF30[i] * dxfData[i].LENGTH) / 2.0);
length6mX10.Add((numXF10[i] * dxfData[i].LENGTH) / 2.0);
}
}
hssfSheet.GetRow(22).GetCell(0).SetCellValue(Math.Ceiling(length6mX20.Sum() / 5.7));
hssfSheet.GetRow(23).GetCell(0).SetCellFormula("A23");
hssfSheet.GetRow(24).GetCell(0).SetCellValue(Math.Ceiling(length6mX30.Sum() / 5.7));
hssfSheet.GetRow(25).GetCell(0).SetCellFormula("A25");
hssfSheet.GetRow(26).GetCell(0).SetCellValue(Math.Ceiling(length6mX10.Sum() / 5.7));
hssfSheet.GetRow(27).GetCell(0).SetCellFormula("A27");
hssfSheet.GetRow(28).GetCell(0).SetCellFormula("ROUNDUP((A11+A15+A19)*2.5,0)");
hssfSheet.GetRow(29).GetCell(0).SetCellFormula("ROUNDUP((A11+A15+A19)/15,0)");
hssfSheet.GetRow(30).GetCell(0).SetCellFormula("ROUNDUP((A23+A25+A27)/35,0)*6");
hssfSheet.GetRow(31).GetCell(0).SetCellFormula("ROUNDUP((A11+A15+A19)/15,0)");
int sumOfStrands = 0;
double overallLength = 0;
double strandLength = 0;
double sumOfStrandLength = 0;
double sumOfStrandWeight = 0;
int sumOfAnchors = 0;
for (int i = 0; i < dxfData.Count; i++)
{
sumOfStrands += dxfData[i].NO_OF_TYPES * dxfData[i].NO_OF_STRANDS;
overallLength = dxfData[i].NO_OF_LIVES == 1 ? dxfData[i].LENGTH + 0.5 : dxfData[i].LENGTH + 1.1;
if (overallLength >= 25)
{
if (overallLength <= 29)
{
overallLength += 0.1;
}
else if (overallLength <= 39)
{
overallLength += 0.2;
}
else
{
overallLength += 0.3;
}
}
strandLength = Convert.ToDouble((Math.Ceiling(overallLength * 10) / 10)) * dxfData[i].NO_OF_TYPES * dxfData[i].NO_OF_STRANDS;
sumOfStrandLength += strandLength;
sumOfStrandWeight += strandLength * 0.785;
sumOfAnchors += dxfData[i].NO_OF_TYPES * dxfData[i].NO_OF_LIVES;
}
hssfSheet.GetRow(41).GetCell(2).SetCellValue(sumOfStrands);
hssfSheet.GetRow(42).GetCell(2).SetCellValue(sumOfStrandLength);
hssfSheet.GetRow(43).GetCell(2).SetCellValue(sumOfAnchors);
hssfSheet.GetRow(44).GetCell(2).SetCellValue(sumOfStrandWeight);
hssfSheet.GetRow(45).GetCell(2).SetCellValue(Math.Ceiling(sumOfStrandWeight));
}
public void Export2Excel(string fileName, DataSet data)
{
MemoryStream ms = new MemoryStream();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet();
#region 文件属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "xi";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "xi";
si.ApplicationName = "xi";
si.LastAuthor = "xi";
si.CreateDateTime = DateTime.Now;
hssfworkbook.SummaryInformation = si;
#endregion
#region Excel单元格格式
int rowIndex = 0;
HSSFRow headRow = null;
HSSFRow titleRow = null;
HSSFRow dataRow = null;
HSSFCell cell = null;
HSSFCellStyle headStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
HSSFFont headfont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(headfont);
HSSFCellStyle titleStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont titlefont = (HSSFFont)hssfworkbook.CreateFont();
headStyle.SetFont(titlefont);
HSSFCellStyle cellDateStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellDateFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
if ("Y" == "Y")
{
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd HH:mm");
}
else
{
cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd");
}
//数量小数格式化字符串
HSSFCellStyle cellNumStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat cellNumFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat;
string formatValue = "0";
string formatStr = string.Empty;
switch (formatValue)
{
#region 格式化
case "0":
formatStr = "0";
break;
case "1":
formatStr = "0.0";
break;
case "2":
formatStr = "0.00";
break;
case "3":
formatStr = "0.000";
break;
case "4":
formatStr = "0.0000";
break;
case "5":
formatStr = "0.00000";
break;
default:
formatStr = "{0:0}";
break;
#endregion
}
if (formatStr == "0" || formatStr == "0.00")
{
cellNumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(formatStr);
}
else
{
cellNumStyle.DataFormat = cellNumFormat.GetFormat(formatStr);
}
//日期格式字符串
string dateFormat = "yyyy-MM-dd";
if ("Y" == "Y")
{
dateFormat += " " + "HH:mm";
}
#endregion
foreach (DataRow row in data.Tables[0].Rows)
{
#region 新建sheet 填写表头 列头
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)hssfworkbook.CreateSheet();
}
#region 列头
titleRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
titleRow.CreateCell(i).SetCellValue(data.Tables[0].Columns[i].ColumnName);
titleRow.GetCell(i).CellStyle = titleStyle;
}
rowIndex++;
#endregion
}
#endregion
#region 明细
dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
for (int i = 0; i < data.Tables[0].Columns.Count; i++)
{
cell = (HSSFCell)dataRow.CreateCell(i);
string value = row[data.Tables[0].Columns[i].ColumnName].ToString();
switch (data.Tables[0].Columns[i].DataType.FullName)
{
case "System.String":
cell.SetCellValue(value);
break;
case "System.DateTime":
DateTime datevalue;
DateTime.TryParse(value, out datevalue);
if (datevalue != new DateTime())
{
cell.CellStyle = cellDateStyle;
cell.SetCellValue(DateTime.Parse(datevalue.ToString(dateFormat)));
}
else
{
cell.SetCellValue("");
}
break;
case "System.Boolean":
bool boolvalue = false;
bool.TryParse(value, out boolvalue);
cell.SetCellValue(boolvalue);
break;
case "System.Int16":
Int16 int16 = 0;
Int16.TryParse(value, out int16);
cell.SetCellValue(int16);
break;
case "System.Int32":
Int32 int32 = 0;
Int32.TryParse(value, out int32);
cell.SetCellValue(int32);
break;
case "System.Int64":
Int64 int64 = 0;
Int64.TryParse(value, out int64);
cell.SetCellValue(int64);
break;
case "System.Byte":
int intV = 0;
int.TryParse(value, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal":
double decV = 0;
double.TryParse(value, out decV);
cell.SetCellValue(decV);
cell.CellStyle = cellNumStyle;
break;
case "System.Single":
float floatV = 0;
float.TryParse(value, out floatV);
cell.SetCellValue(floatV);
cell.CellStyle = cellNumStyle;
break;
case "System.Double":
double doubV = 0;
double.TryParse(value, out doubV);
cell.SetCellValue(doubV);
cell.CellStyle = cellNumStyle;
break;
case "System.DBNull":
cell.SetCellValue("");
break;
default:
cell.SetCellValue(value);
break;
}
}
#endregion
rowIndex++;
}
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "GB2312";
HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Response.BinaryWrite(ms.ToArray());
HttpContext.Response.End();
ms.Close();
sheet = null;
hssfworkbook = null;
ms = null;
}
public int line_num = 0; //接收到多少行
private void serialPort1_DataReceived(object sender, SerialDataReceivedEventArgs e) //对于串口要接收大量数据不能用定时获取的方式
{
Thread.Sleep(serial_read_dealy); //等待串口数据全部到达，如果出现乱码就加长些
if (serialPort1.IsOpen) //serialPort1.IsOpen&&serial_temp!=""
{
line_num++;
serial = serialPort1.ReadExisting();
serialPort1.DiscardInBuffer();//记得清空串口，不然容易留到下次接收导致乱码
LM_print(serial);
if (draw_open)
{
DATA_SPLIT A = new DATA_SPLIT();
if (form2.serial_read_way_0 == "自由捕获")
{
A = GetData_Free(serial);
}
else
{
A = GetData_Keyword(serial);
}
line_var = A.DATA + 1;
for (int i = 0; i < line_var; i++)
{
if (line_var_first)
{
coor[i, coor_var - 1, 0] = 0; //ms
time_temp = Environment.TickCount; //系统启功后到当前的时间
}
else
{
coor[i, coor_var - 1, 0] = Environment.TickCount - time_temp;//开始画图后到现在的时间
}
coor[i, coor_var - 1, 1] = Convert.ToDouble(A.data_split[i]);
for (int i_0 = 0; i_0 < coor_var - 1; i_0++)
{
coor[i, i_0, 0] = coor[i, i_0 + 1, 0];
coor[i, i_0, 1] = coor[i, i_0 + 1, 1];
if (auto_limit)
{
if (coor[i, i_0 + 1, 1] < var_min && coor[i, i_0 + 1, 1] != 0)
{
var_min = coor[i, i_0 + 1, 1];
}
if (coor[i, i_0 + 1, 1] > var_max)
{
var_max = coor[i, i_0 + 1, 1] + 1;
var_min = var_max - 1;//防止等于0
}
}
}
line_var_first = false;
}
/*************************散点图**********************************/
DATA_SPLIT B = GetData_Keyword_SD(serial);
sd_var = B.DATA + 1;
for (int i = 0; i < sd_var; i++)
{
coor_sd[i, coor_var_sd - 1, 0] = Convert.ToDouble(B.data_split[i]);
coor_sd[i, coor_var_sd - 1, 1] = Convert.ToDouble(B.data_split_Y[i]);
for (int i_0 = 0; i_0 < coor_var_sd - 1; i_0++)
{
coor_sd[i, i_0, 0] = coor_sd[i, i_0 + 1, 0];
coor_sd[i, i_0, 1] = coor_sd[i, i_0 + 1, 1];
if (coor_sd[i, i_0 + 1, 1] < var_min_sdy && coor_sd[i, i_0 + 1, 1] != 0)
{
var_min_sdy = coor_sd[i, i_0 + 1, 1];
}
if (coor_sd[i, i_0 + 1, 1] > var_max_sdy)
{
var_max_sdy = coor_sd[i, i_0 + 1, 1] + 1;
var_min_sdy = var_max_sdy - 1;//防止等于0
}
if (coor_sd[i, i_0 + 1, 0] < var_min_sdx && coor_sd[i, i_0 + 1, 0] != 0)
{
var_min_sdx = coor_sd[i, i_0 + 1, 0];
}
if (coor_sd[i, i_0 + 1, 0] > var_max_sdx)
{
var_max_sdx = coor_sd[i, i_0 + 1, 0] + 1;
var_min_sdx = var_max_sdx - 1;//防止等于0
}
}
}
}
//***********************************数据记录与保存****************************************************/
if (excel_record)
{
label_excel_record_val.Text = "已记录\n折线" + row_var + "条\n点阵" + row_var_sd + "条";
HSSFSheet Sheet1 = (HSSFSheet)workbook2003.GetSheet("散点图"); //获取名称为Sheet1的工作表
HSSFSheet Sheet2 = (HSSFSheet)workbook2003.GetSheet("数据可视化"); //获取名称为Sheet1的工作表
row_var_sd++;
if (excel_first_sd)
{
for (int i = 0; i < sd_var; i++)//散点图
{
if (i > 0)
{
Sheet1.GetRow(0).CreateCell(0 + i * 3).SetCellValue("点" + i + " X坐标");
Sheet1.GetRow(0).CreateCell(1 + i * 3).SetCellValue("点" + i + "Y坐标");
}
else
{
Sheet1.CreateRow(0).CreateCell(0 + i * 3).SetCellValue("点" + i + " X坐标");
Sheet1.GetRow(0).CreateCell(1 + i * 3).SetCellValue("点" + i + "Y坐标");
}
}
excel_first_sd = !excel_first_sd;
}
for (int i = 0; i < sd_var; i++)
{
if (i > 0)
{
Sheet1.GetRow(row_var_sd).CreateCell(0 + i * 3).SetCellValue(coor_sd[i, coor_var_sd - 1, 0]);
Sheet1.GetRow(row_var_sd).CreateCell(1 + i * 3).SetCellValue(coor_sd[i, coor_var_sd - 1, 1]);
}
else
{
Sheet1.CreateRow(row_var_sd).CreateCell(0).SetCellValue(coor_sd[0, coor_var_sd - 1, 0]);
Sheet1.GetRow(row_var_sd).CreateCell(1).SetCellValue(coor_sd[0, coor_var_sd - 1, 1]);//已经创造过的不能再用create,会被覆盖
}
}
if (line_var > 0)
{
row_var++;
Sheet2.CreateRow(0).CreateCell(0).SetCellValue("时间（秒）");
for (int i = 0; i < line_var; i++)//数据可视化
{
Sheet2.GetRow(0).CreateCell(i + 1).SetCellValue("数据 " + i);
}
Sheet2.CreateRow(row_var).CreateCell(0).SetCellValue((coor[0, coor_var - 1, 0] * 0.001));
for (int i = 0; i < line_var; i++)
{
Sheet2.GetRow(row_var).CreateCell(i + 1).SetCellValue(coor[i, coor_var - 1, 1]);
}
}
}
if (ecxel_save)//导出文件
{
FileStream file2003 = new FileStream(@file_name, FileMode.Create);
workbook2003.Write(file2003);
file2003.Close();
workbook2003.Close();
ecxel_save = false;
}
}
}
private void ExportData()
{
int corpid = GetInt("corpid");
string[] ids = GetString("ids").Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
InitializeWorkbook();
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");
HSSFRow rowHeader = (HSSFRow)sheet1.CreateRow(0);
rowHeader.CreateCell(0).SetCellValue("线索状态");
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("追踪级别");
rowHeader.CreateCell(17).SetCellValue("追踪报警");
rowHeader.CreateCell(18).SetCellValue("追踪次数");
rowHeader.CreateCell(19).SetCellValue("追踪方式");
rowHeader.CreateCell(20).SetCellValue("最后追踪时间");
rowHeader.CreateCell(21).SetCellValue("最后追踪人");
rowHeader.CreateCell(22).SetCellValue("追踪情况");
rowHeader.CreateCell(23).SetCellValue("预约到店时间");
rowHeader.CreateCell(24).SetCellValue("客户来店时间");
rowHeader.CreateCell(25).SetCellValue("客户离店时间");
rowHeader.CreateCell(26).SetCellValue("接待时长");
rowHeader.CreateCell(27).SetCellValue("来店人数");
rowHeader.CreateCell(28).SetCellValue("是否到店");
rowHeader.CreateCell(29).SetCellValue("省份-城市-地区");
rowHeader.CreateCell(30).SetCellValue("备用电话");
rowHeader.CreateCell(31).SetCellValue("具体地址");
rowHeader.CreateCell(32).SetCellValue("微信号");
rowHeader.CreateCell(33).SetCellValue("最后操作人");
rowHeader.CreateCell(34).SetCellValue("选购品牌");
rowHeader.CreateCell(35).SetCellValue("选购车系");
rowHeader.CreateCell(36).SetCellValue("选购车型");
rowHeader.CreateCell(37).SetCellValue("订单号");
rowHeader.CreateCell(38).SetCellValue("成交价");
rowHeader.CreateCell(39).SetCellValue("战败原因");
rowHeader.CreateCell(40).SetCellValue("战败原因分析");
rowHeader.CreateCell(41).SetCellValue("建档时间");
rowHeader.CreateCell(42).SetCellValue("提交时间");
rowHeader.CreateCell(43).SetCellValue("市场专员");
rowHeader.CreateCell(44).SetCellValue("DCC专员");
rowHeader.CreateCell(45).SetCellValue("展厅专员");
rowHeader.CreateCell(46).SetCellValue("直销专员");
rowHeader.CreateCell(47).SetCellValue("自动编号");
rowHeader.CreateCell(48).SetCellValue("系统备注");
List <CustomerInfo> list = Customers.Instance.GetCustomerListByCorporation(corpid, true);
list = list.FindAll(l => ids.Contains(l.ID.ToString()));
for (int i = 0; i < list.Count; i++)
{
HSSFRow row = (HSSFRow)sheet1.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(list[i].CustomerStatus == (int)CustomerStatus.潜客_转出 ? (list[i].CustomerStatusSourceName + "(转出)") : list[i].CustomerStatusName);
row.CreateCell(1).SetCellValue(list[i].Name);
row.CreateCell(2).SetCellValue(list[i].Phone);
row.CreateCell(3).SetCellValue(list[i].IbuyCarSeries);
row.CreateCell(4).SetCellValue(list[i].InfoType);
row.CreateCell(5).SetCellValue(list[i].InfoSource);
row.CreateCell(6).SetCellValue(list[i].CustomerSex == 1 ? "男" : (list[i].CustomerSex == 2 ? "女" : "保密"));
row.CreateCell(7).SetCellValue(list[i].PhoneVest);
row.CreateCell(8).SetCellValue(list[i].Owner);
row.CreateCell(9).SetCellValue(list[i].Tracktag);
row.CreateCell(10).SetCellValue(list[i].IbuyCarBrand);
row.CreateCell(11).SetCellValue(list[i].IbuyCarModel);
row.CreateCell(12).SetCellValue(list[i].IbuyTime);
row.CreateCell(13).SetCellValue(list[i].QuotedpriceInfo);
row.CreateCell(14).SetCellValue(list[i].PromotionInfo);
row.CreateCell(15).SetCellValue(list[i].RemarkInfo);
row.CreateCell(16).SetCellValue(list[i].LastCustomerLevel);
row.CreateCell(17).SetCellValue(list[i].ConnectAlarm == "0" ? "正常" : (list[i].ConnectAlarm == "1" ? "正常(24小时内超时)" : (list[i].ConnectAlarm == "2" ? "追踪超时" : string.Empty)));
row.CreateCell(18).SetCellValue(list[i].ConnectTimes);
row.CreateCell(19).SetCellValue(list[i].LastConnectway);
row.CreateCell(20).SetCellValue(list[i].LastConnectTime);
row.CreateCell(21).SetCellValue(list[i].LastConnectUser);
row.CreateCell(22).SetCellValue(list[i].LastConnectDetail);
row.CreateCell(23).SetCellValue(list[i].ReservationTime);
row.CreateCell(24).SetCellValue(list[i].VisitTime);
row.CreateCell(25).SetCellValue(list[i].LeaveTime);
row.CreateCell(26).SetCellValue(list[i].IsVisit == 0 ? string.Empty : list[i].VisitDuration.ToString());
row.CreateCell(27).SetCellValue(list[i].IsVisit == 0 ? string.Empty : list[i].VisitNumber.ToString());
row.CreateCell(28).SetCellValue(list[i].IsVisit == 0 ? "否" : "是");
row.CreateCell(29).SetCellValue(string.Format("{0}-{1}-{2}", list[i].Province, list[i].City, list[i].District));
row.CreateCell(30).SetCellValue(list[i].BackupPhone);
row.CreateCell(31).SetCellValue(list[i].Address);
row.CreateCell(32).SetCellValue(list[i].WeixinAccount);
row.CreateCell(33).SetCellValue(list[i].LastUpdateUser);
row.CreateCell(34).SetCellValue(list[i].SbuyCarBrand);
row.CreateCell(35).SetCellValue(list[i].SbuyCarSeries);
row.CreateCell(36).SetCellValue(list[i].SbuyCarModel);
row.CreateCell(37).SetCellValue(list[i].OrderNumber);
row.CreateCell(38).SetCellValue(list[i].KnockdownPrice);
row.CreateCell(39).SetCellValue(list[i].GiveupCause);
row.CreateCell(40).SetCellValue(list[i].FailureCauseAnalyze);
row.CreateCell(41).SetCellValue(list[i].CreateTime);
row.CreateCell(42).SetCellValue(list[i].PostTime);
row.CreateCell(43).SetCellValue(list[i].MarketDirector);
row.CreateCell(44).SetCellValue(list[i].DCCDirector);
row.CreateCell(45).SetCellValue(list[i].ExhibitionDirector);
row.CreateCell(46).SetCellValue(list[i].Director);
row.CreateCell(47).SetCellValue(list[i].ShowNo);
row.CreateCell(48).SetCellValue(list[i].SystemRemark);
}
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
hssfworkbook.Write(ms);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond.ToString(), Encoding.UTF8).ToString() + ".xls");
Response.BinaryWrite(ms.ToArray());
Response.End();
hssfworkbook = null;
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="start_row">起始行</param>
/// <param name="end_row">结束行</param>
/// <param name="start_col">起始列</param>
/// <param name="end_col">结束列</param>
/// <param name="sheet">需要设置的表格</param>
public static void MergeCells(int start_row, int end_row, int start_col, int end_col, ref HSSFSheet sheet)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(start_row - 1, end_row - 1, start_col - 1, end_col - 1));
}
public static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "zzh"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "zzh"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:MM:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length + 1;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length + 1;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp + 1;
}
}
}
int rowIndex = 0;
//表头样式
HSSFCellStyle titleStyle = SetCellStyle(workbook, BorderStyle.Thin, 55, VerticalAlignment.Center, HorizontalAlignment.Center, HSSFColor.White.Index);
HSSFFont titleFont = SetFont(workbook, "宋体", 0, 700, 20);
titleStyle.SetFont(titleFont);
//列头样式
HSSFCellStyle headStyle = SetCellStyle(workbook, BorderStyle.Thin, 55, VerticalAlignment.Center, HorizontalAlignment.Center, HSSFColor.White.Index);
HSSFFont headfont = SetFont(workbook, "宋体", 0, 700, 10);
headStyle.SetFont(headfont);
//内容样式
HSSFCellStyle bodyStyle = SetCellStyle(workbook, BorderStyle.Thin, 55, VerticalAlignment.Center, HorizontalAlignment.Left, HSSFColor.White.Index);
HSSFFont bodyFont = SetFont(workbook, "宋体", 0, 400, 10);
bodyStyle.SetFont(bodyFont);
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
//HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
//headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//HSSFFont font = workbook.CreateFont() as HSSFFont;
//font.FontHeightInPoints = 20;
//font.Boldweight = 700;
//headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = titleStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
//HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
//headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//HSSFFont font = workbook.CreateFont() as HSSFFont;
//font.FontHeightInPoints = 10;
//font.Boldweight = 700;
//headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
newCell.CellStyle = bodyStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
if (!DateTime.TryParse(drValue, out dateV))
{
continue; //如果数据类型装换失败，直接不赋值 modified by lu 2017.7.21
}
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 设置单元格是否居中对齐，默认居左和底部
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="vertical">是否垂直居中</param>
/// <param name="horizontal">是否水平居中</param>
/// <param name="wb"></param>
public static void SetCellAlignmentCenter(int row, int col, bool vertical, bool horizontal, ref HSSFWorkbook wb)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
if (vertical) // 垂直
{
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
}
if (horizontal) // 水平
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
}
t_cell.CellStyle = cellStyle;
}
}
}
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
}
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 设置单元格的格式，如日期、小数
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="format"></param>
/// <param name="wb"></param>
public static void SetCellFormat(int row, int col, CellFormat format, ref HSSFWorkbook wb)
{
HSSFCellStyle Style = (HSSFCellStyle)wb.CreateCellStyle();
IDataFormat dataFormat = wb.CreateDataFormat();
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
Style.CloneStyleFrom(t_cell.CellStyle);
switch (format)
{
case CellFormat.Date: Style.DataFormat = dataFormat.GetFormat("yyyy年m月d日");
break;
case CellFormat.Point2: Style.DataFormat = dataFormat.GetFormat("0.00");
break;
}
t_cell.CellStyle = Style;
}
}
}
public HSSFEvaluationSheet(HSSFSheet hs)
{
_hs = hs;
}
/// <summary>
/// Creates new HSSFRow from scratch. Only HSSFSheet should do this.
/// </summary>
/// <param name="book">low-level Workbook object containing the sheet that Contains this row</param>
/// <param name="sheet">low-level Sheet object that Contains this Row</param>
/// <param name="rowNum">the row number of this row (0 based)</param>
///<see cref="NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/>
public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, int rowNum) : this(book, sheet, new RowRecord(rowNum))
{
}
/// <summary>
/// DataTable导出到Excel的MemoryStream(.xls)
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="headRow">列头行数</param>
private static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText = "", int headRow = 1)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Harry"; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = "Harry"; //填加xls文件最后保存者信息
si.Comments = "Harry"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int ExcelrowIndex = 0;
int DtRowIndex = 0;//
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (ExcelrowIndex == 65535 || ExcelrowIndex == 0)
{
if (ExcelrowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
#region 无用，准备删除
// HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
// headerRow.HeightInPoints = 25;
// HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// //headStyle.Alignment = HorizontalAlignment.Center;//水平居中
// //headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
// HSSFFont font = (HSSFFont)workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
// ExcelrowIndex++;
#endregion
HSSFCellStyle hssfcellstyle = (HSSFCellStyle)workbook.CreateCellStyle();
if (!string.IsNullOrEmpty(strHeaderText))
{
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref hssfcellstyle, ExcelrowIndex, false);
NewFoot(ref hssfcellstyle, workbook, 15);
headerRow.CreateCell(0).SetCellValue(strHeaderText);
headerRow.GetCell(0).CellStyle = hssfcellstyle;
ExcelrowIndex++;
}
}
#endregion
#region 列头及样式
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
#region 无用，准备删除
//HSSFRow headerRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//headerRow.HeightInPoints = 25;
//HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.WrapText = true;//自动换行
////headStyle.Alignment = HorizontalAlignment.Center;//水平居中
//headStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
//HSSFFont font = (HSSFFont)workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.Boldweight = 700;
//headStyle.SetFont(font);
#endregion
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
//headerRow.Dispose();
ExcelrowIndex++;
}
//添加更多列
{
for (int i = 1; i < headRow; i++)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow headerRow = NewHSSFRow(ref sheet, workbook, ref headStyle, ExcelrowIndex, true);
NewFoot(ref headStyle, workbook, 10);
foreach (DataColumn column in dtSource.Columns)
{
string drValue = row[column].ToString();
headerRow.CreateCell(column.Ordinal).SetCellValue(drValue);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, ((arrColWidth[column.Ordinal] + 1) * 256) > 10000 ? 10000 :
((arrColWidth[column.Ordinal] + 1) * 256)); //宽度10000可自定义
}
ExcelrowIndex++;
}
}
#endregion
}
#endregion
//跳过多行列头情况下已添加过的列
DtRowIndex++;
if (DtRowIndex < headRow)
{
continue;
}
#region 填充内容
#region 无用，准备删除
//HSSFRow dataRow = (HSSFRow)sheet.CreateRow(ExcelrowIndex);
//HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//rowStyle.WrapText = true;//自动换行
//rowStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
#endregion
HSSFCellStyle rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFRow dataRow = NewHSSFRow(ref sheet, workbook, ref rowStyle, ExcelrowIndex, true, 15);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
dataRow.GetCell(column.Ordinal).CellStyle = rowStyle;
}
#endregion
ExcelrowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// NPOI DataGridView 导出 EXCEL
/// </summary>
/// <param name="fileName"> 默认保存文件名</param>
/// <param name="dgv">DataGridView</param>
/// <param name="fontname">字体名称</param>
/// <param name="fontsize">字体大小</param>
public void ExportExcel(string fileName, DataGridView dgv, string fontname, short fontsize, string filename)
{
//检测是否有数据
//if (dgv.SelectedRows.Count == 0) return;
//创建主要对象
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(filename);
//设置字体，大小，对齐方式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = fontname;
font.FontHeightInPoints = fontsize;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
//添加表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
//dataRow = (HSSFRow)sheet.CreateRow(0);
dataRow.CreateCell(0).SetCellValue(filename);
dataRow = (HSSFRow)sheet.CreateRow(1);
for (int i = 0; i < dgv.Columns.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.GetCell(i).CellStyle = style;
}
//注释的这行是设置筛选的
//sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count));
//添加列及内容
for (int i = 0; i < dgv.Rows.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 2);
for (int j = 0; j < dgv.Columns.Count; j++)
{
string ValueType = dgv.Rows[i].Cells[j].Value is null ? "System.String" : dgv.Rows[i].Cells[j].Value.GetType().ToString();
string Value = dgv.Rows[i].Cells[j].Value is null ? "" : dgv.Rows[i].Cells[j].Value.ToString();
switch (ValueType)
{
case "System.String": //字符串类型
dataRow.CreateCell(j).SetCellValue(Value);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(Value, out dateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(Value, out boolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(Value, out intV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(Value, out doubV);
dataRow.CreateCell(j).SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
dataRow.CreateCell(j).SetCellValue("");
break;
default:
dataRow.CreateCell(j).SetCellValue("");
break;
}
dataRow.GetCell(j).CellStyle = style;
//设置宽度
var maxColumn = dgv.Columns.Count;
//列宽自适应，只对英文和数字有效
//for (int j = 0; j <= maxColumn; j++)
//{
// sheet.AutoSizeColumn(j);
//}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= maxColumn; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
// sheet.SetColumnWidth(j, (Value.Length) * 512);
}
}
//保存文件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
MemoryStream ms = new MemoryStream();
if (true || saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName + "日语生词" + ".xls";
if (saveFileName.IndexOf("/") > 0)
{
// saveFileName=saveFileName.Insert(saveFileName.IndexOf("/"), @"\");
saveFileName = saveFileName.Replace("/", "-");
}
//if (!CheckFiles(saveFileName))
//{
// MessageBox.Show("文件被站用，请关闭文件后重新进行导出操作 " + saveFileName);
// workbook = null;
// ms.Close();
// ms.Dispose();
// return;
//}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK);
//if (MessageBox.Show("导出成功,点击 [是] 后打开文件所在位置", "导出成功", MessageBoxButtons.YesNo) == DialogResult.Yes)
// dateTabletoCSV.ClickOpenLocation(saveFileName);
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
}
public static CFRuleRecord Create(HSSFSheet sheet, String formulaText)
{
Ptg[] formula1 = ParseFormula(formulaText, sheet);
return(new CFRuleRecord(CONDITION_TYPE_FORMULA, ComparisonOperator.NoComparison,
formula1, null));
}
/**
* Returns all the charts for the given sheet.
*
* NOTE: You won't be able to do very much with
* these charts yet, as this is very limited support
*/
public static HSSFChart[] GetSheetCharts(HSSFSheet sheet) {
List<HSSFChart> charts = new List<HSSFChart>();
HSSFChart lastChart = null;
HSSFSeries lastSeries = null;
// Find records of interest
List<RecordBase> records = sheet.GetSheet().GetRecords();
foreach(RecordBase r in records) {
if(r is ChartRecord) {
lastSeries = null;
lastChart = new HSSFChart(sheet,(ChartRecord)r);
charts.Add(lastChart);
} else if(r is LegendRecord) {
lastChart.legendRecord = (LegendRecord)r;
} else if(r is SeriesRecord) {
HSSFSeries series = lastChart.new HSSFSeries( (SeriesRecord)r );
lastChart.series.Add(series);
lastSeries = series;
} else if(r is ChartTitleFormatRecord) {
lastChart.chartTitleFormat =
(ChartTitleFormatRecord)r;
} else if(r is SeriesTextRecord) {
// Applies to a series, unless we've seen
// a legend already
SeriesTextRecord str = (SeriesTextRecord)r;
if(lastChart.legendRecord == null &&
lastChart.series.Size() > 0) {
HSSFSeries series = (HSSFSeries)
lastChart.series.Get(lastChart.series.Size()-1);
series.seriesTitleText = str;
} else {
lastChart.chartTitleText = str;
}
} else if (r is LinkedDataRecord) {
LinkedDataRecord linkedDataRecord = (LinkedDataRecord) r;
if (lastSeries != null) {
lastSeries.InsertData(linkedDataRecord);
}
} else if(r is ValueRangeRecord){
lastChart.valueRanges.Add((ValueRangeRecord)r);
} else if (r is Record) {
if (lastChart != null)
{
Record record = (Record) r;
for (HSSFChartType type : HSSFChartType.Values()) {
if (type == HSSFChartType.Unknown)
{
continue;
}
if (record.GetSid() == type.GetSid()) {
lastChart.type = type ;
break;
}
}
}
}
}
return (HSSFChart[])
charts.ToArray( new HSSFChart[charts.Size()] );
}
public HSSFDataValidationHelper(HSSFSheet sheet)
: base()
{
this.sheet = sheet;
}
/**
* Creates a bar chart. API needs some work. :)
* <p>
* NOTE: Does not yet work... checking it in just so others
* can take a look.
*/
public void CreateBarChart( HSSFWorkbook workbook, HSSFSheet sheet )
{
List<Record> records = new List<Record>();
records.Add( CreateMSDrawingObjectRecord() );
records.Add( CreateOBJRecord() );
records.Add( CreateBOFRecord() );
records.Add(new HeaderRecord(""));
records.Add(new FooterRecord(""));
records.Add( CreateHCenterRecord() );
records.Add( CreateVCenterRecord() );
records.Add( CreatePrintSetupRecord() );
// unknown 33
records.Add( CreateFontBasisRecord1() );
records.Add( CreateFontBasisRecord2() );
records.Add(new ProtectRecord(false));
records.Add( CreateUnitsRecord() );
records.Add( CreateChartRecord( 0, 0, 30434904, 19031616 ) );
records.Add( CreateBeginRecord() );
records.Add( CreateSCLRecord( (short) 1, (short) 1 ) );
records.Add( CreatePlotGrowthRecord( 65536, 65536 ) );
records.Add( CreateFrameRecord1() );
records.Add( CreateBeginRecord() );
records.Add( CreateLineFormatRecord(true) );
records.Add( CreateAreaFormatRecord1() );
records.Add( CreateEndRecord() );
records.Add( CreateSeriesRecord() );
records.Add( CreateBeginRecord() );
records.Add( CreateTitleLinkedDataRecord() );
records.Add( CreateValuesLinkedDataRecord() );
records.Add( CreateCategoriesLinkedDataRecord() );
records.Add( CreateDataFormatRecord() );
// records.add(createBeginRecord());
// unknown
// records.add(createEndRecord());
records.Add( CreateSeriesToChartGroupRecord() );
records.Add( CreateEndRecord() );
records.Add( CreateSheetPropsRecord() );
records.Add( CreateDefaultTextRecord( DefaultDataLabelTextPropertiesRecord.CATEGORY_DATA_TYPE_ALL_TEXT_CHARACTERISTIC ) );
records.Add( CreateAllTextRecord() );
records.Add( CreateBeginRecord() );
// unknown
records.Add( CreateFontIndexRecord( 5 ) );
records.Add( CreateDirectLinkRecord() );
records.Add( CreateEndRecord() );
records.Add( CreateDefaultTextRecord( (short) 3 ) ); // eek, undocumented text type
records.Add( CreateUnknownTextRecord() );
records.Add( CreateBeginRecord() );
records.Add( CreateFontIndexRecord( (short) 6 ) );
records.Add( CreateDirectLinkRecord() );
records.Add( CreateEndRecord() );
records.Add( CreateAxisUsedRecord( (short) 1 ) );
CreateAxisRecords( records );
records.Add( CreateEndRecord() );
records.Add( CreateDimensionsRecord() );
records.Add( CreateSeriesIndexRecord(2) );
records.Add( CreateSeriesIndexRecord(1) );
records.Add( CreateSeriesIndexRecord(3) );
records.Add(EOFRecord.instance);
sheet.InsertChartRecords( records );
workbook.InsertChartRecord();
}
/// <summary>
/// 将Excel里的数据分页显示
/// </summary>
/// <param name="strFileName">文件名</param>
/// <param name="pageSize">每页显示行数</param>
/// <param name="currentPage">当前页数</param>
/// <param name="total">总行数</param>
/// <returns></returns>
/// <remarks></remarks>
public static DataTable ImportByPage(string strFileName, int pageSize, int currentPage, ref int total)
{
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook = default(HSSFWorkbook);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
total = sheet.LastRowNum;
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j <= cellCount - 1; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
int firstRow = sheet.FirstRowNum + 1 + (currentPage - 1) * pageSize;
for (int i = firstRow; i <= firstRow + pageSize - 1; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
if (i <= sheet.LastRowNum)
{
for (int j = row.FirstCellNum; j <= cellCount - 1; j++)
{
if (row.GetCell(j) != null)
{
HSSFCell cell = (HSSFCell)row.GetCell(j);
if (cell.CellType == NPOI.SS.UserModel.CellType.Formula)
{
switch (cell.CachedFormulaResultType)
{
case NPOI.SS.UserModel.CellType.String:
dataRow[j] = row.GetCell(j).StringCellValue;
break;
case NPOI.SS.UserModel.CellType.Numeric:
dataRow[j] = row.GetCell(j).NumericCellValue;
break;
default:
dataRow[j] = row.GetCell(j).ToString();
break;
}
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
dt.Rows.Add(dataRow);
}
}
return(dt);
}