private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet)
{
SetCellRangeAddress(sheet, 0, 0, 0, 1);
SetCellRangeAddress(sheet, 0, 0, 3, 4);
SetCellRangeAddress(sheet, 0, 0, 6, 7);
SetCellRangeAddress(sheet, 0, 0, 9, 10);
SetCellRangeAddress(sheet, 0, 0, 12, 14);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("输入功率标定");
row.CreateCell(3).SetCellValue("输出功率标定");
row.CreateCell(6).SetCellValue("反射功率标定");
row.CreateCell(9).SetCellValue("ALC功率标定");
row.CreateCell(12).SetCellValue("衰减补偿");
row.GetCell(0).CellStyle = GetCellStyle(book);
row.GetCell(3).CellStyle = GetCellStyle(book);
row.GetCell(6).CellStyle = GetCellStyle(book);
row.GetCell(9).CellStyle = GetCellStyle(book);
row.GetCell(12).CellStyle = GetCellStyle(book);
row = sheet.CreateRow(1);
for (int i = 0; i < 4; i++)
{
row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压");
row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点");
row.GetCell(3 * i).CellStyle = GetCellStyle(book);
row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book);
}
row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值");
row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值");
row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值");
row.GetCell(12).CellStyle = GetCellStyle(book);
row.GetCell(13).CellStyle = GetCellStyle(book);
row.GetCell(14).CellStyle = GetCellStyle(book);
}
public void TestNotCreateEmptyCells()
{
IWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = new SheetBuilder(wb, testData).Build();
Assert.AreEqual(sheet.PhysicalNumberOfRows, 3);
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
NPOI.SS.UserModel.ICell firstCell = firstRow.GetCell(0);
Assert.AreEqual(firstCell.CellType, CellType.NUMERIC);
Assert.AreEqual(1.0, firstCell.NumericCellValue, 0.00001);
NPOI.SS.UserModel.IRow secondRow = sheet.GetRow(1);
Assert.IsNotNull(secondRow.GetCell(0));
Assert.IsNull(secondRow.GetCell(2));
NPOI.SS.UserModel.IRow thirdRow = sheet.GetRow(2);
Assert.AreEqual(CellType.STRING, thirdRow.GetCell(0).CellType);
String cellValue = thirdRow.GetCell(0).StringCellValue;
Assert.AreEqual(testData[2][0].ToString(), cellValue);
Assert.AreEqual(CellType.FORMULA, thirdRow.GetCell(2).CellType);
Assert.AreEqual("A1+B2", thirdRow.GetCell(2).CellFormula);
}
private static void SetJZDRowValue(NPOI.SS.UserModel.IRow row, JZD jzd)
{
IPoint point = jzd.Feature.Shape as IPoint;
row.GetCell(1).SetCellValue("J" + jzd.JZDH);
row.GetCell(2).SetCellValue(point.X);
row.GetCell(3).SetCellValue(point.Y);
}
/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
// HSSFWorkbook hssfworkbook;
IWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
string fileExt = Path.GetExtension(strFileName);
if (fileExt == ".xls")
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (fileExt == ".xlsx")
{
hssfworkbook = new XSSFWorkbook(file);
}
else
{
return(new DataTable());
}
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
return(dt);
}
/// <summary>
/// 导入Excel
/// </summary>
/// <param name="importPath">导入文件路径</param>
/// <param name="headerIndex">表头所在行索引 （兼容第一行为大标题的情况）</param>
public static DataSet ImportExcel(string importPath, int headerIndex = 0)
{
DataSet ds = new DataSet();
DataTable dt = null;
FileStream fs = new FileStream(importPath, FileMode.Open, FileAccess.Read);
IWorkbook book;
if (Path.GetExtension(importPath) == "xls")
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
}
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(headerIndex); //从第0行开始取 列头
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = headerIndex + 1; i <= sheet.LastRowNum; i++) //从第1行开始取数据
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
var cell = sheet.GetRow(i).GetCell(j);
cell.SetCellType(CellType.String);
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
ds.Tables.Add(dt);
}
return(ds);
}
public string ToString(int column)
{
var cell = XlsRow.GetCell(column);
if (cell != null)
{
// TODO: you can add more cell types capatibility, e. g. formula
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Numeric:
return(cell.NumericCellValue.ToString());
case NPOI.SS.UserModel.CellType.String:
return(cell.StringCellValue);
}
}
return(null);
}
private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = null;
OpenFileDialog sflg = new OpenFileDialog();
sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
}
}
ds.Tables.Add(dt);
}
}
/// <summary>
/// Get a specific cell from a row. If the cell doesn't exist,
/// </summary>
/// <param name="row">The row that the cell is part of</param>
/// <param name="column">The column index that the cell is in.</param>
/// <returns>The cell indicated by the column.</returns>
public static NPOI.SS.UserModel.ICell GetCell(NPOI.SS.UserModel.IRow row, int column)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(column);
if (cell == null)
{
cell = row.CreateCell(column);
}
return(cell);
}
/// <summary>
/// 设置指定行列的单元格的背景色
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <param name="color">颜色</param>
private void setAlarm(int rowIndex, int columnIndex)
{
rowIndex--;
columnIndex--;
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
//if (xlSheet.LastRowNum >= rowIndex)
//{
if (xlSheet.GetRow(rowIndex) != null)
{
row = xlSheet.GetRow(rowIndex);
}
else
{
row = xlSheet.CreateRow(rowIndex);
}
//if (xlSheet.LastRowNum == 0)
//{
// row = xlSheet.CreateRow(rowIndex);
//}
//else
//{
// row = xlSheet.GetRow(rowIndex);
//}
//}
//else
//{
// row = xlSheet.CreateRow(rowIndex);
//}
if (row.GetCell(columnIndex) != null)
{
cell = row.GetCell(columnIndex);
}
else
{
cell = row.CreateCell(columnIndex);
}
ICellStyle style = xlBook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
}
/// <summary>
/// 创建EXCEL文档
/// </summary>
/// <param name="path">保存路径</param>
/// <param name="dt">DataTable</param>
public static void CreatExcel(string fullPath, DataTable dt)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "主题";
hssfworkbook.SummaryInformation = si;
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet("Sheet");
hssfSheet.DefaultColumnWidth = 18;
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//表头
NPOI.SS.UserModel.IRow tagRow = hssfSheet.CreateRow(0);
tagRow.RowStyle = cellStyle;
for (int i = 0; i < dt.Columns.Count; i++)
{
tagRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
tagRow.GetCell(i).CellStyle = cellStyle;
}
int rowNum = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(rowNum);
if (rowNum == 50001)//超过五万条数据,新建一张工作表
{
hssfSheet = hssfworkbook.CreateSheet();
rowNum = 1;
NPOI.SS.UserModel.IRow newrow = hssfSheet.CreateRow(0);
row = hssfSheet.CreateRow(1);
for (int j = 0; j < dt.Columns.Count; j++)
{
newrow.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName.ToString());
}
}
//写入记录
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][dt.Columns[j].ColumnName].ToString());
}
rowNum++;
}
FileStream file = new FileStream(fullPath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 获取指定行列的数据
/// </summary>
/// <param name="rowIndex">行索引</param>
/// <param name="columnIndex">列索引</param>
/// <returns>该单元格的内容</returns>
private string getData(int rowIndex, int columnIndex)
{
rowIndex--;
columnIndex--;
if (xlSheet.LastRowNum >= rowIndex)
{
NPOI.SS.UserModel.IRow row = null;
if (xlSheet.LastRowNum == 0)
{
row = xlSheet.CreateRow(rowIndex);
}
else
{
row = xlSheet.GetRow(rowIndex);
}
if (row.LastCellNum >= columnIndex)
{
if (row.GetCell(columnIndex) != null)
{
return(row.GetCell(columnIndex).ToString());
}
else
{
return("");
}
}
else
{
return("");
}
}
else
{
return("");
}
}
/// <summary>
/// 將excel數據導入到DataTable,第一行為標題行,不導入
/// </summary>
/// <param name="path">excel路徑</param>
/// <param name="actiontype">獲取表結構，在存儲過程Web_GetImportSchema中</param>
/// <returns>不成功返回null</returns>
public DataTable ToDataTable(string path, string actiontype)
{
if (File.Exists(path))
{
DataTable dt = GetTableSchema(actiontype);
if (dt == null)
{
return(null);
}
Dictionary <int, DataType> dic = GetDataTableColType(dt);
int cols = dic.Count;
using (FileStream fs = new FileStream(path, FileMode.Open))
{
IWorkbook book = null;
ISheet sheet = null;
book = WorkbookFactory.Create(fs);
sheet = book.GetSheetAt(0);
if (sheet != null && sheet.LastRowNum > 0 && sheet.FirstRowNum >= 0 && sheet.LastRowNum != sheet.FirstRowNum)
{
int begin = sheet.FirstRowNum + 1, last = sheet.LastRowNum + 1;
while (begin < last)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(begin);
if (row == null)
{
break;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < cols; i++)
{
AddColValue(dr, row.GetCell(i), i, dic[i]);
}
dt.Rows.Add(dr);
begin += 1;
}
}
}
return(dt);
}
return(null);
}
private static void createCells(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet, DataTable dt, int start, int end)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
int colIndex = 0;
NPOI.SS.UserModel.IRow row = sheet.GetRow(i + 2);
if (row == null)
{
row = sheet.CreateRow(i + 2);
}
for (int j = start; j <= end; j++)
{
row.CreateCell(j).SetCellValue(Convert.ToInt32(dt.Rows[i][colIndex]));
row.GetCell(j).CellStyle = GetCellStyle(book);
colIndex++;
}
}
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromExcel(string excelFilePath)
{
HSSFWorkbook hssfworkbook;
//excelFilePath = excelFilePath.Replace("http://","").Replace("/","\\");
using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < 4; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
}
private static DataTable GetDataTableByXls(string excelFilePath, DataTable dt)
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int r = 0;
while (rows.MoveNext())
{
r++;
if (r == 1)
{
continue;
}
NPOI.SS.UserModel.IRow row = (HSSFRow)rows.Current;
DataRow rw = dt.NewRow();
for (int i = 1; i <= row.LastCellNum - 1; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
continue;
}
else
{
rw[i - 1] = cell.ToString();
}
}
dt.Rows.Add(rw);
}
return(dt);
}
}
/// <summary>
/// NPOI 导出会议的管理人员
/// </summary>
/// <param name="id"></param>
/// <param name="ids"></param>
public void ExportMeeting()
{
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计");
//excel格式化
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle();
numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000");
NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");
//设置单元格宽度
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//给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.Sheet.SetColumnWidth(0, 100 * 50);
row1.Sheet.SetColumnWidth(1, 100 * 50);
row1.Sheet.SetColumnWidth(2, 150 * 50);
row1.Sheet.SetColumnWidth(3, 100 * 50);
row1.Sheet.SetColumnWidth(4, 100 * 50);
MessasgeData mgdata = new MessasgeData();
mgdata = Datafun.MgfunctionData("select Bank,UserName,b.MeetingName,convert(nvarchar(11),Start_Date,120) ,convert(nvarchar(11),End_Date,120) from tb_login a left join tb_Meeting b on a.Meeting=b.id where Lvl=1 and a.isdel=1 ");
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "会议管理人员.xlsx")); //添加Excel名字
for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString());
rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString());
rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString());
rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString());
rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString());
rowtemp.GetCell(0).CellStyle = textStyle;
rowtemp.GetCell(1).CellStyle = textStyle;
rowtemp.GetCell(2).CellStyle = textStyle;
rowtemp.GetCell(3).CellStyle = numberStyle;
rowtemp.GetCell(4).CellStyle = textStyle;
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
/// <summary>
/// NPOI 宴会统计导出
/// </summary>
/// <param name="id"></param>
/// <param name="ids"></param>
public void Export(string id, string ids)
{
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("宴会统计");
//excel格式化
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle();
numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000");
NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle();
textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@");
//设置单元格宽度
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
//给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.Sheet.SetColumnWidth(0, 80 * 50);
row1.Sheet.SetColumnWidth(1, 80 * 50);
row1.Sheet.SetColumnWidth(2, 50 * 50);
row1.Sheet.SetColumnWidth(3, 80 * 50);
row1.Sheet.SetColumnWidth(4, 150 * 50);
row1.Sheet.SetColumnWidth(5, 80 * 50);
row1.Sheet.SetColumnWidth(6, 80 * 50);
MessasgeData mgdata = new MessasgeData();
SqlParameter[] pms =
{
new SqlParameter("@meeting", id),
new SqlParameter("@ms", ids)
};
mgdata = Datafun.MgfunctionData("select MeetingName from tb_Meeting where id=@meeting", pms);
if (mgdata.Mgdatacount > 0)
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "" + mgdata.Mgdata.Rows[0][0].ToString() + ".xlsx"));//添加Excel名字
}
else
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "宴会统计.xlsx"));//添加Excel名字
}
mgdata = Datafun.MgfunctionData("Tj_rstj_poc", pms, "poc");
for (int i = 0; i < mgdata.Mgdata.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(mgdata.Mgdata.Rows[i][0].ToString());
rowtemp.CreateCell(1).SetCellValue(mgdata.Mgdata.Rows[i][1].ToString());
rowtemp.CreateCell(2).SetCellValue(mgdata.Mgdata.Rows[i][2].ToString());
rowtemp.CreateCell(3).SetCellValue(mgdata.Mgdata.Rows[i][3].ToString());
rowtemp.CreateCell(4).SetCellValue(mgdata.Mgdata.Rows[i][4].ToString());
rowtemp.CreateCell(5).SetCellValue(mgdata.Mgdata.Rows[i][5].ToString());
rowtemp.CreateCell(6).SetCellValue(mgdata.Mgdata.Rows[i][6].ToString());
rowtemp.GetCell(0).CellStyle = textStyle;
rowtemp.GetCell(1).CellStyle = textStyle;
rowtemp.GetCell(2).CellStyle = textStyle;
rowtemp.GetCell(3).CellStyle = numberStyle;
rowtemp.GetCell(4).CellStyle = textStyle;
rowtemp.GetCell(5).CellStyle = textStyle;
rowtemp.GetCell(6).CellStyle = numberStyle;
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
Response.BinaryWrite(ms.ToArray());
Response.Flush();
Response.End();
}
/// <summary>
/// 读取excel到datatable中
/// </summary>
/// <param name="excelPath">excel地址</param>
/// <param name="sheetIndex">sheet索引</param>
/// <returns>成功返回datatable，失败返回null</returns>
public static DataTable ImportExcel(string excelPath, int sheetIndex)
{
IWorkbook workbook = null; //全局workbook
ISheet sheet; //sheet
DataTable table = null;
try
{
FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在
if (fileInfo.Exists)
{
FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流
switch (fileInfo.Extension)
{
//xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
case ".xls": workbook = new HSSFWorkbook(fileStream); break;
case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;
default: break;
}
fileStream.Close();//关闭文件流
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(sheetIndex); //读取到指定的sheet
table = new DataTable(); //初始化一个table
IRow headerRow = sheet.GetRow(0); //获取第一行，一般为表头
int cellCount = headerRow.LastCellNum; //得到列数
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列
table.Columns.Add(column);
}
//遍历读取cell
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //得到一行
DataRow dataRow = table.NewRow(); //新建一个行
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j); //得到cell
if (cell == null) //如果cell为null，则赋值为空
{
dataRow[j] = string.Empty;
}
else
{
dataRow[j] = row.GetCell(j).ToString();//否则赋值
}
}
table.Rows.Add(dataRow);//把行 加入到table中
}
}
return(table);
}
catch (Exception)
{
return(table);
}
finally
{
//释放资源
if (table != null)
{
table.Dispose();
}
workbook = null;
sheet = null;
}
}
/// <summary>
/// 数据导出到excel
/// </summary>
/// <param name="tempName">模板名称</param>
/// <param name="fileName">文件名称</param>
/// <param name="dsSheet">包含工作表数据的数据集</param>
private void WriteToExcel(string tempName, string fileName, DataSet dsSheet)
{
IWorkbook book = null;
string _filePath = Server.MapPath(_exportPath) + tempName;
using (FileStream fs = File.Open(_filePath, FileMode.Open, FileAccess.ReadWrite))
{
book = WorkbookFactory.Create(fs);
ISheet wSheet; //define worksheet
ICellStyle cStyle = book.CreateCellStyle(); //define style
////HSSFDataFormat.GetBuiltinFormat("###0.00");//(short)CellType.NUMERIC;
IDataFormat dataformat = book.CreateDataFormat();
cStyle.DataFormat = 194;//数字格式
ICellStyle cStyleDate = book.CreateCellStyle();
cStyleDate.DataFormat = dataformat.GetFormat("yyyy-MM-dd"); //
ICellStyle cStyleTime = book.CreateCellStyle();
cStyleTime.DataFormat = dataformat.GetFormat("hh:mm"); //
foreach (DataTable dt in dsSheet.Tables)
{
wSheet = book.GetSheet(dt.TableName);//打开指定的工作表
#region f填充数据
NPOI.SS.UserModel.IRow rowTitle = wSheet.GetRow(0);
NPOI.SS.UserModel.IRow rowtemp;
DataColumn dc;
for (int i = 0; i < dt.Rows.Count; i++)
{
rowtemp = wSheet.GetRow(i + 1);//先获取现有的行，如果为空，则创建
if (rowtemp == null)
{
rowtemp = wSheet.CreateRow(i + 1);
}
ICell rowCell;
string headTitle;
for (int j = 0; j < rowTitle.LastCellNum; j++)
{
if (rowTitle.GetCell(j) == null)
{
break;
}
headTitle = rowTitle.GetCell(j).ToString();
if (!dt.Columns.Contains(headTitle))
{
continue;
}
dc = dt.Columns[headTitle];
rowCell = rowtemp.GetCell(j);
if (rowCell == null)
{
rowCell = rowtemp.CreateCell(j);
}
if ((dc.DataType.Name == "Decimal" || dc.DataType.Name.Substring(0, 3) == "Int") && !dt.Rows[i].IsNull(dc.ColumnName))
{
double during = double.Parse(dt.Rows[i][dc.ColumnName].ToString());
rowCell.SetCellType(CellType.Numeric);
rowCell.SetCellValue(during);
rowCell.CellStyle = cStyle;
}
else if (dc.DataType.Name == "DateTime" && !dt.Rows[i].IsNull(dc.ColumnName))
{
rowCell.SetCellValue((DateTime)dt.Rows[i][dc.ColumnName]);
rowCell.CellStyle = cStyleDate;
}
else if (dc.DataType.Name == "TimeSpan" && !dt.Rows[i].IsNull(dc.ColumnName))
{
rowCell.SetCellValue(DateTime.Today.Date + (TimeSpan)dt.Rows[i][dc.ColumnName]);
rowCell.CellStyle = cStyleTime;
}
else if (!dt.Rows[i].IsNull(dc.ColumnName))
{
rowCell.SetCellValue(dt.Rows[i][dc.ColumnName].ToString());
}
}
}
//调整列宽
for (int j = 0; j < rowTitle.LastCellNum; j++)
{
wSheet.AutoSizeColumn(j);
}
#endregion
}
}
//保存到本地文件
_filePath = Server.MapPath(_exportPath) + fileName;
using (FileStream fs1 = File.Open(_filePath, FileMode.Create, FileAccess.ReadWrite))
{
book.Write(fs1);
}
book = null;
divSaveAs.InnerHtml = "<font color='black'>Excel文件已生成，请</font> <a href='" + _exportPath + fileName + "'>单击下载</a> ";
}
DataSet ReadExcel(string fullfileName)
{
DataSet ds = new DataSet();
DataTable dt = null;
FileStream fs = new FileStream(fullfileName, FileMode.Open, FileAccess.Read);
//NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
IWorkbook book = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
int sheetCount = book.NumberOfSheets;
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
if (sheet == null)
{
continue;
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(0);
if (row == null)
{
continue;
}
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum)
{
continue;
}
dt = new DataTable(sheet.SheetName);
for (int i = firstCellNum; i < lastCellNum; i++)
{
if (row.GetCell(i) == null)
{
continue;
}
var colHeader = row.GetCell(i).StringCellValue;
int index = 1;
while (dt.Columns.Contains(colHeader))
{
colHeader += index;
index++;
}
dt.Columns.Add(colHeader, typeof(string));
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
DataRow newRow = dt.Rows.Add();
for (int j = firstCellNum; j < lastCellNum; j++)
{
if (sheet.GetRow(i) == null)
{
continue;
}
if (sheet.GetRow(i).GetCell(j) == null)
{
continue;
}
ICell cell = sheet.GetRow(i).GetCell(j);
if (cell.CellType == CellType.String)
{
newRow[j] = cell.StringCellValue;
}
else if (cell.CellType == CellType.Numeric)
{
newRow[j] = cell.NumericCellValue;
}
else if (cell.CellType == CellType.Blank)
{
newRow[j] = DBNull.Value;
}
else
{
throw new Finance.Utils.FinanceException(FinanceResult.NOT_SUPPORT);
}
}
}
ds.Tables.Add(dt);
}
return(ds);
}
private string GenerateExcelReport(int report, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
NPOI.XSSF.UserModel.XSSFWorkbook workbook =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = workbook.CreateSheet();
workbook.SetActiveSheet(0);
// sets up basic styles for Excel
NPOI.SS.UserModel.ICellStyle HeaderCellStyle = workbook.CreateCellStyle();
HeaderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeaderCellStyle.VerticalAlignment = VerticalAlignment.Center;
HeaderCellStyle.Alignment = HorizontalAlignment.Center;
HeaderCellStyle.FillPattern = FillPattern.SolidForeground;
HeaderCellStyle.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index;
IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
HeaderCellStyle.SetFont(headerFont);
NPOI.SS.UserModel.ICellStyle DataCellStyle = workbook.CreateCellStyle();
DataCellStyle.VerticalAlignment = VerticalAlignment.Top;
DataCellStyle.WrapText = true;
ArrayList query_for_status = new ArrayList();
string query_columns = string.Empty,
ignore_columns = "id,cohort_id,status_timestamp,published",
column_headers = string.Empty;
switch (report)
{
case 0:
query_columns = "id,cohort_id,cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp";
query_for_status.Add("pending");
query_for_status.Add("rejected");
query_for_status.Add("inprogress");
ignore_columns = "id,cohort_id";
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Received,CEDCD Form Reviewed,Date CEDCD Form Published";
break;
case 1:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp";
query_for_status.Add("pending");
query_for_status.Add("rejected");
query_for_status.Add("inprogress");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Current Status,Current Status Date";
break;
case 2:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp";
query_for_status.Add("published");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Published";
break;
case 3:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp [Date CEDCD Form was Unpublished]";
query_for_status.Add("unpublished");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Unpublished";
break;
case 4:
query_columns = "*";
query_for_status.Add("published");
query_for_status.Add("pending");
break;
default:
query_columns = "*";
query_for_status.Add("published");
break;
}
DataTable dt_records = CECWebSrv.GetCohortsWithStatusesWithColumns(UserToken, (string[])query_for_status.ToArray(typeof(string)), query_columns);
/// write header to excel
///
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
//dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue("Export Date:");
//dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
//rowIndex += 2;
int colPos = 0;
///--------------------------------------------------------
/// column headers
///
NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++);
if (column_headers != string.Empty)
{
foreach (string s in column_headers.Split(','))
{
ICell c = headerRow.GetCell(colPos++, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(s);
c.CellStyle = HeaderCellStyle;
}
}
else
{
using (CECHarmPublicService ps = new CECHarmPublicService())
{
for (int _c = 0; _c < dt_records.Columns.Count; _c++)
{
if (ignore_columns.Contains(dt_records.Columns[_c].ColumnName))
{
continue;
}
ICell c = headerRow.GetCell(colPos);
if (c == null)
{
c = headerRow.CreateCell(colPos);
}
string column_name = ps.GetCohortWebFieldLabelByColumnName(UserToken, dt_records.Columns[_c].ColumnName);
if (String.IsNullOrWhiteSpace(column_name))
{
column_name = dt_records.Columns[_c].ColumnName;
}
c.SetCellValue(column_name);
c.CellStyle = HeaderCellStyle;
colPos++;
}
}
}
if (report != 0 && dt_records.Rows.Count >= 1)
{
/// data rows
for (int _i = 0; _i < dt_records.Rows.Count; _i++)
{
colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < dt_records.Columns.Count; _p++)
{
if (ignore_columns.Contains(dt_records.Columns[_p].ColumnName))
{
continue;
}
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = dt_records.Rows[_i][_p].ToString();
if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1")
{
cellVal = "N/P";
c.SetCellValue(cellVal);
}
else if (dt_records.Columns[_p].DataType == typeof(DateTime))
{
c.SetCellValue(DateTime.Parse(cellVal).ToString("MM/dd/yyyy"));
}
else if (dt_records.Columns[_p].ColumnName == "status")
{
switch (cellVal)
{
case "inprogress":
c.SetCellValue("Draft In Progress");
break;
case "pending":
c.SetCellValue("Under NCI Review");
break;
case "rejected":
c.SetCellValue("Returned to Cohort");
break;
default:
c.SetCellValue(cellVal);
break;
}
}
else
{
c.SetCellValue(cellVal);
}
c.CellStyle = DataCellStyle;
colPos++;
}
}
}
else if (report == 0 && dt_records.Rows.Count >= 1)
{
foreach (DataRow dr in dt_records.Rows)
{
dataRow = wkst.CreateRow(rowIndex++);
// cohort acronym
ICell c = dataRow.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["cohort_acronym"].ToString());
c.CellStyle = DataCellStyle;
// cohort name
c = dataRow.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["cohort_name"].ToString());
c.CellStyle = DataCellStyle;
// pi name
c = dataRow.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_name_1"].ToString());
c.CellStyle = DataCellStyle;
// pi institution
c = dataRow.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_institution_1"].ToString());
c.CellStyle = DataCellStyle;
// pi email
c = dataRow.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_email_1"].ToString());
c.CellStyle = DataCellStyle;
// the more complicated stuff...
if (dr["status"].ToString() == "pending")
{
c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue("No");
c.CellStyle = DataCellStyle;
using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false))
{
if (dt_temp.Rows.Count > 0)
{
c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
}
}
}
else if (dr["status"].ToString() == "rejected" || dr["status"].ToString() == "inprogress")
{
c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (dr["status"].ToString() == "rejected")
{
using (DataTable dt_temp = (new CECHarmPublicService()).AuditLog_GetActivities(UserToken, "submitted", (int)dr["id"]))
{
if (dt_temp.Rows.Count == 0)
{
c.SetCellValue(" ");
}
else
{
c.SetCellValue(((DateTime)dt_temp.Rows[0]["create_date"]).ToString("MM/dd/yyyy"));
}
}
}
else
{
c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy"));
}
c.CellStyle = DataCellStyle;
c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue("Pending Revisions");
c.CellStyle = DataCellStyle;
using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false))
{
if (dt_temp.Rows.Count > 0)
{
c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
}
}
}
}
}
else
{
ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(String.Format("Nothing to report"));
}
for (int _ic = 0; _ic <= headerRow.PhysicalNumberOfCells; _ic++)
{
wkst.AutoSizeColumn(_ic);
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
workbook.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 导入excel
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public ActionResult ImportExcel(HttpPostedFileBase file)
{
try
{
//思路：获取读取的文件，把文件转换为二进制数组，然后转成内存流，利用NPOI把内存流中的数据读取成Excel
Session.Remove("ImportExcel"); //把session中的ImportExcel移除避免残留以前数据
string fileExtension = System.IO.Path.GetExtension(file.FileName); //读取路径文件的扩展名
if (".xls".Equals(fileExtension) || ".XLS".Equals(fileExtension)) //判断读取的文件是.xls文件
{
byte[] fileBytes = new byte[file.ContentLength]; //指定数组的长度获取Excel数据的大小
file.InputStream.Read(fileBytes, 0, file.ContentLength); //读取文件内容
// 转为 内存流
System.IO.MemoryStream excelFileStream = new System.IO.MemoryStream(fileBytes);
//将内存流转为 工作簿
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelFileStream);
//判断工作簿中的工作表（Sheet）的个数
if (workbook.NumberOfSheets > 0)
{
//查询出 学院,专业,年级,班级 的信息：目的是用来查看导入的数据是否有重复
List <PatientVo> lsitStudentVos = new List <PatientVo>();
// 获取第一个工作表
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
//PhysicalNumberOfRows 获取的是物理行数，也就是不包括那些空行（隔行）的情况。
//判断 工作表（sheet)中有数据
if (sheet.PhysicalNumberOfRows > 0)
{
//将数据先装到datatable中
// 定义datatable
DataTable dtExcel = new DataTable();
//获取标题行 第一行
NPOI.SS.UserModel.IRow headerRow = sheet.GetRow(0);
//获取一行单元格个数 LastCellNum 获取列数，比最后一列列标大 1
int cellCount = headerRow.LastCellNum;
//获取数据总行数 LastRowNum 最后一行行标，比行数小 1
int rowCount = sheet.LastRowNum + 1;
//创建DataTable的列Columns
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dtExcel.Columns.Add(column);
}
//读取Excel中的数据
//(sheet.FirstRowNum) 第一行是标题
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //获取行
DataRow dataRow = dtExcel.NewRow(); //DataTable创建一行
if (row != null)
{
//遍历Excel一行的所有单元格
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
//添加行DataRow到DataTable
dtExcel.Rows.Add(dataRow);
}
//遍历datatable 获取数据
foreach (DataRow row in dtExcel.Rows)
{
//创建一个 StudentVo的对象
PatientVo student = new PatientVo();
try
{
//获取性别ID
var dd = row["性别"].ToString().Trim();
var SexID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == dd select tbSex.BaseDetailID).Single();
student.sexID = Convert.ToInt32(SexID);
student.Sex = row["性别"].ToString().Trim();
//病人所属ID
var PatientOwnership = row["病人所属"].ToString().Trim();
var cantonID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == PatientOwnership select tbSex.BaseDetailID).Single();
student.cantonID = Convert.ToInt32(cantonID);
student.PatientOwnership = row["病人所属"].ToString().Trim();
//获取人群分类id和名称
var CrowdClass = row["人群分类"].ToString().Trim();
var CrowdclassificationID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CrowdClass select tbSex.BaseDetailID).Single();
student.CrowdclassificationID = Convert.ToInt32(CrowdclassificationID);
student.CrowdClass = row["人群分类"].ToString().Trim();
//获取病例分类ID和名称
var CaseClass = row["病例分类"].ToString().Trim();
var CaseClassID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == CaseClass select tbSex.BaseDetailID).Single();
student.CaseClassID = Convert.ToInt32(CaseClassID);
student.CaseClass = row["病例分类"].ToString().Trim();
//获取病例名称ID和名称
var DiseaseType = row["疾病名称类型"].ToString().Trim();
var DiseaseTypeID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == DiseaseType select tbSex.BaseDetailID).Single();
student.DiseaseTypeID = Convert.ToInt32(DiseaseTypeID);
student.DiseaseType = row["疾病名称类型"].ToString().Trim();
//获取审核状态ID和名称
var AuditStatu = row["审核状态"].ToString().Trim();
var AuditStatusID = (from tbSex in MyModels.B_BaseDetailList where tbSex.BaseDetailName == AuditStatu select tbSex.BaseDetailID).Single();
student.AuditStatusID = Convert.ToInt32(AuditStatusID);
student.AuditStatus = row["审核状态"].ToString().Trim();
//给创建实体赋值
student.Cardnumber = row["卡片编号"].ToString().Trim();
student.name = row["患者姓名"].ToString().Trim();
student.IDnumber = row["身份证号码"].ToString().Trim();
student.BirthDateTime = row["出生日期"].ToString().Trim();
student.workunit = row["工作单位"].ToString().Trim();
student.Addressnationalstandard = row["现详细住址国标"].ToString().Trim();
student.Detailedaddress = row["现住详细地址"].ToString().Trim();
student.CensusAddressInter = row["户籍地址国标"].ToString().Trim();
student.CensusAddDetail = row["户籍地址详细"].ToString().Trim();
student.MorbidityDateTime = row["发病日期"].ToString().Trim();
student.DiagnoseDateTime = row["诊断日期"].ToString().Trim();
student.ReportDateTime = row["录入日期"].ToString().Trim();
student.RevisedDateTime = row["订正终审日期"].ToString().Trim();
student.Occupation = row["职业"].ToString().Trim();
student.Remark = row["备注"].ToString().Trim();
lsitStudentVos.Add(student);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//把数据存在session当中做临时保存，这里还没有保存到数据库
Session["ImportExcel"] = lsitStudentVos;
return(Json(true, JsonRequestBehavior.AllowGet));
}
else
{
//物理行数为0
}
}
else
{
//没有工作表
}
}
else
{
//上传的文件类型不正确
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(Json(false, JsonRequestBehavior.AllowGet));
}
public static DataTable ImportExcel(MemoryStream ms)
{
IWorkbook workbook = null; //全局workbook
ISheet sheet; //sheet
DataTable table = null;
try
{
workbook = new HSSFWorkbook(ms);
if (workbook != null)
{
sheet = workbook.GetSheetAt(0); //读取到指定的sheet
table = new DataTable(); //初始化一个table
IRow headerRow = sheet.GetRow(0); //获取第一行，一般为表头
int cellCount = headerRow.LastCellNum; //得到列数
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//初始化table的列
table.Columns.Add(column);
}
//遍历读取cell
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //得到一行
DataRow dataRow = table.NewRow(); //新建一个行
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j); //得到cell
if (cell == null) //如果cell为null，则赋值为空
{
dataRow[j] = "";
}
else
{
dataRow[j] = row.GetCell(j).ToString();//否则赋值
}
}
table.Rows.Add(dataRow);//把行 加入到table中
}
}
return(table);
}
catch (Exception e)
{
return(table);
}
finally
{
//释放资源
if (table != null)
{
table.Dispose();
}
workbook = null;
sheet = null;
}
}
private string ExportDataGridToExcel(DataTable toExport, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
CECHarmPublicService ps =
new CECHarmPublicService();
NPOI.XSSF.UserModel.XSSFWorkbook wkbk =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = wkbk.CreateSheet();
wkbk.SetActiveSheet(0);
/// write header to excel
///
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Table Name:");
dataRow.CreateCell(1).SetCellValue("Cancer Counts");
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Export Date:");
dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
// gender selections
string tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Selected Gender(s):");
foreach (string s in Genders)
{
tAss += String.Format("{0}, ", s);
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
// cancer category
tAss = string.Empty;
//dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue("Cancer Category:");
//dataRow.CreateCell(1).SetCellValue((new CultureInfo("en-US").TextInfo.ToTitleCase(CancerCategory)));
// cancer selections
tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Selected Cancer Type(s):");
foreach (string c in CancerTypes)
{
tAss += String.Format("{0}, ", select_cancer.GetCancerLabel(c));
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
// cohort selections
tAss = string.Empty;
dataRow = wkst.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue("Selected Cohort(s):");
foreach (string s in CohortIDsToCompare)
{
tAss += String.Format("{0}, ", select_cohort.GetCohortAcronym(int.Parse(s)));
}
tAss = (new CultureInfo("en-US").TextInfo.ToTitleCase(tAss.TrimEnd(new char[] { ' ', ',' })));
dataRow.CreateCell(1).SetCellValue(tAss);
rowIndex += 2;
///--------------------------------------------------------
/// column headers
///
NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++);
for (int _c = 0; _c < toExport.Columns.Count; _c++)
{
ICell c = headerRow.GetCell(_c);
if (c == null)
{
c = headerRow.CreateCell(_c);
}
c.SetCellValue(toExport.Columns[_c].ColumnName);
}
if (toExport.Rows.Count > 1)
{
/// data rows
for (int _i = 0; _i < toExport.Rows.Count; _i++)
{
int colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < toExport.Columns.Count; _p++)
{
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = toExport.Rows[_i][_p].ToString();
/// position 0 is cancer type, position 1 is male/female
if (_p == 0)
{
c.SetCellValue(select_cancer.GetCancerLabel(cellVal));
}
else if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1")
{
/// ---------------------------------------
/// was N/P, per Amy 23-April, 0 is preferred
/// per Amy, 4-May, 0 should appear if the cohort has incident cancers--even for prevelant;
/// show N/P if incident and prevelant is not provided
if (String.IsNullOrWhiteSpace(cellVal) || cellVal == " ")
{
//if (CECWebSrv.CohortHasCancerIndicator(UserToken, toExport.Columns[_p].ColumnName))
cellVal = "0";
//else
// cellVal = "N/P";
}
else
{
cellVal = "N/P";
}
c.SetCellValue(cellVal);
}
else
{
c.SetCellValue(cellVal);
}
colPos++;
}
}
}
else
{
ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(String.Format("None of the select cohorts reported any 'todo' cancers"));
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
wkbk.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 外观检查日报Excel
/// </summary>
/// <param name="ds"></param>
/// <param name="ReportHeader"></param>
/// <returns></returns>
public static MemoryStream ExportXlsByList(DataSet ds, string ReportHeader = "", string userName = "")
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
HSSFSheet sheet1 = (HSSFSheet)book.CreateSheet(ReportHeader);
sheet1.DefaultRowHeight = 150 * 4;//设置全局行高
//设置列宽度
for (int i = 0; i < ds.Tables[0].Columns.Count + 19; i++)
{
if (i == 1 || i == 0 || i == 28)//第一、二列
{
sheet1.SetColumnWidth(i, 19 * 250);
}
else if (i > 1 && i < 7)
{
sheet1.SetColumnWidth(i, 10 * 256);
}
else
{
sheet1.SetColumnWidth(i, 6 * 256);
}
}
//字体
IFont fontkh = book.CreateFont(); //创建一个字体样式
fontkh.FontHeightInPoints = 12; //字体大小
fontkh.FontName = "宋体"; //字体名
//显示数据字体
IFont fontdata = book.CreateFont(); //创建一个字体样式
fontdata.FontHeightInPoints = 11; //字体大小
fontdata.FontName = "宋体"; //字体名
//显示数据字体
IFont fontdata1 = book.CreateFont(); //创建一个字体样式
fontdata1.FontHeightInPoints = 11; //字体大小
fontdata1.Color = (short)ConsoleColor.Green;
fontdata1.FontName = "宋体"; //字体名
//字符样式
HSSFFont row2stylefont = (HSSFFont)book.CreateFont();
row2stylefont.FontName = "宋体";
row2stylefont.FontHeightInPoints = 25;
row2stylefont.IsBold = true;
//显示数据格式
ICellStyle styledata = book.CreateCellStyle();//创建显示数据格式
styledata.WrapText = true;
styledata.VerticalAlignment = VerticalAlignment.Center; //上下居中
styledata.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
styledata.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
styledata.SetFont(fontdata);
ICellStyle styledata1 = book.CreateCellStyle(); //创建显示数据格式
styledata1.Alignment = HorizontalAlignment.Center; //左对齐
styledata1.WrapText = true;
styledata1.VerticalAlignment = VerticalAlignment.Center; //上下居中
styledata1.SetFont(fontdata1);
//添加考核标题样式
//单元格样式
ICellStyle stylekh = book.CreateCellStyle();
stylekh.Alignment = HorizontalAlignment.Left; //左对齐
stylekh.WrapText = true; //自动换行
stylekh.FillForegroundColor = HSSFColor.PaleBlue.Index; //前景色
stylekh.FillPattern = FillPattern.SolidForeground; //填充样式
stylekh.VerticalAlignment = VerticalAlignment.Center; //上下居中
stylekh.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh.SetFont(fontkh);
ICellStyle stylekh1 = book.CreateCellStyle();
stylekh1.Alignment = HorizontalAlignment.Left; //左对齐
stylekh1.WrapText = true; //自动换行
stylekh1.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index; //前景色
stylekh1.FillPattern = FillPattern.SolidForeground; //填充样式
stylekh1.VerticalAlignment = VerticalAlignment.Center; //上下居中
stylekh1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //显示边框
stylekh1.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; //边框显示颜色
stylekh1.SetFont(fontkh);
//第一行单元格样式 外观检测日报
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 20;//字体大小
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontName = "宋体";
style.SetFont(font);
//给第一行填数据
HSSFRow row2 = (HSSFRow)sheet1.CreateRow(0);
row2.CreateCell(0).SetCellValue("外观检查日报");
row2.GetCell(0).CellStyle = style;
//显示考核信息
HSSFRow row3 = (HSSFRow)sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue("考核信息");
row3.GetCell(0).CellStyle = stylekh;
//第一行设置合并单元格
//参数分别为 自左到右 开始行索引,结束行索引,开始列索引,结束列索引
CellRangeAddress firstcellRange = new CellRangeAddress(0, 1, 0, ds.Tables[0].Columns.Count / 3);
CellRangeAddress nextcellRange = new CellRangeAddress(0, 1, ds.Tables[0].Columns.Count / 3, ds.Tables[0].Columns.Count + 10);
sheet1.AddMergedRegion(firstcellRange);
sheet1.AddMergedRegion(nextcellRange);
//空出一行
CellRangeAddress khInfo1 = new CellRangeAddress(2, 2, 1, ds.Tables[0].Columns.Count + 10);//显示"考核信息"
sheet1.AddMergedRegion(khInfo1);
CellRangeAddress kb = new CellRangeAddress(5, 5, 0, ds.Tables[0].Columns.Count + 10);//第5行空白
sheet1.AddMergedRegion(kb);
//备注合并单元格
CellRangeAddress Remark = new CellRangeAddress(3, 3, 7, 13); //合并第三行第8列到第9列
CellRangeAddress Remark1 = new CellRangeAddress(4, 4, 7, 13); //合并第四行第8列到第9列
sheet1.AddMergedRegion(Remark);
sheet1.AddMergedRegion(Remark1);
//给sheet1添加第一行的头部标题
HSSFRow row1 = (HSSFRow)sheet1.CreateRow(3);
HSSFRow tworow = (HSSFRow)sheet1.CreateRow(6); //第7行
int twonum = 0; //列标题的索引号
string mColumnName = ""; //列标题的名称
string[] strs = new string[]
{ "料伤", "夹伤", "划伤", "打伤", "磨伤", "轴承不过", "倒角不良",
"抛光变形", "扁错位", "刀痕", "孔径不良", "尾刺", "粗糙度",
"电镀不良", "生锈", "漏工程", "料头", "跳动不良", "长度不良", "加工错误", "其他" };//数组存储不良类型，用来构造列头
//列标题
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
{
//考核信息标题
mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();
if (k < 8)
{
row1.CreateCell(k).SetCellValue(mColumnName);
row1.GetCell(k).CellStyle = stylekh;
}
else
{
if (k > ds.Tables[0].Columns.Count - 4)
{
if (k == 15)
{
tworow.CreateCell(strs.Length + 7).SetCellValue(mColumnName);
tworow.GetCell(strs.Length + 7).CellStyle = stylekh1;
}
//此处是不良类型的标题，隐去了不良类型与不良数,利用生产的数组代替
if (k == (ds.Tables[0].Columns.Count - 1))
{
for (int i = 0; i < strs.Length; i++)
{
tworow.CreateCell(twonum).SetCellValue(strs[i]);
tworow.GetCell(twonum).CellStyle = stylekh1;
twonum++;
}
}
}
else
{
tworow.CreateCell(twonum).SetCellValue(k == 11 ? mColumnName + "/H" : mColumnName); //因为数据库里部能包好标准时/H 在此重构一下
tworow.GetCell(twonum).CellStyle = k == 15 ? stylekh1 : stylekh;
twonum++;
}
}
}
////显示数据 按行填充 行数从0开始
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(4);//第5行
ICellStyle makeStyle = book.CreateCellStyle();
makeStyle.Alignment = HorizontalAlignment.Left;
makeStyle.VerticalAlignment = VerticalAlignment.Center;
IFont makeFont = book.CreateFont();
makeFont.FontName = "宋体";
makeFont.FontHeightInPoints = 16;
makeStyle.SetFont(makeFont);
row1.CreateCell(15).SetCellValue("作成: " + userName + " 时间：" + DateTime.Now.ToString());
row1.GetCell(15).CellStyle = makeStyle;
rowtemp.CreateCell(15).SetCellValue("说明:达成率=(标准工时+其他工时)/出勤工时*100% 标准时间=∑(检查数量/标准时间)");
//考核信息部分 j为行索引 这里只遍历前八个
for (int j = 0; j < 8; j++)
{
if (j == 0)
{ //第一个日期转化格式
rowtemp.CreateCell(j).SetCellValue(DateTime.Parse(ds.Tables[0].Rows[0][j].ToString()).ToString("yyyy年MM月dd日"));
}
else if (j > 1 && j < 7)
{
if (j == 6)
{
//达成率加百分比
rowtemp.CreateCell(j).SetCellValue(ds.Tables[0].Rows[0][j].ToString() + "%");
}
else
{
//工号、出勤时间等已双精度输出
rowtemp.CreateCell(j).SetCellValue(double.Parse(ds.Tables[0].Rows[0][j].ToString()));
}
}
else
{
//备注姓名等
rowtemp.CreateCell(j).SetCellValue(ds.Tables[0].Rows[0][j].ToString());
}
//样式填充
if (j != 7)
{
rowtemp.GetCell(j).CellStyle = styledata;
}
}
int SumCheck = 0;
int okCheck = 0;
int NgCheck = 0;
int asd;
//第7行开始遍历导出数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
HSSFRow rowtemp1 = (HSSFRow)sheet1.CreateRow(i + 7);
for (int j = 7; j < ds.Tables[0].Columns.Count - 1; j++)
{
if (j > ds.Tables[0].Columns.Count - 4 && ((ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1]) == null ? false : true))
{
string[] types = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 2].ToString().Split(',');
string[] cts = ds.Tables[0].Rows[i][ds.Tables[0].Columns.Count - 1].ToString().Split(',');
if (types.Length > 1 && cts.Length > 1)
{
for (int cs = 0; cs < cts.Length; cs++)
{
rowtemp1.CreateCell(j - 8 + int.Parse(types[cs])).SetCellValue(int.Parse(cts[cs]));
rowtemp1.GetCell(j - 8 + int.Parse(types[cs])).CellStyle = styledata1;
}
}
}
else
{
int.TryParse(ds.Tables[0].Rows[i][j].ToString(), out asd);
if (asd > 0 || j == 14)
{
if (j == 12)
{
SumCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
if (j == 13)
{
okCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
if (j == 14)
{
NgCheck += int.Parse(ds.Tables[0].Rows[i][j].ToString());
}
rowtemp1.CreateCell(j - 8).SetCellValue(int.Parse(ds.Tables[0].Rows[i][j].ToString()));
rowtemp1.GetCell(j - 8).CellStyle = styledata;
}
else if (j == 7)
{
rowtemp1.CreateCell(strs.Length + j).SetCellValue(ds.Tables[0].Rows[i][j].ToString());
}
else
{
rowtemp1.CreateCell(j - 8).SetCellValue(ds.Tables[0].Rows[i][j].ToString());
rowtemp1.GetCell(j - 8).CellStyle = styledata;
}
}
}
}
HSSFRow rowtemp2 = (HSSFRow)sheet1.CreateRow(ds.Tables[0].Rows.Count + 7);
rowtemp2.CreateCell(3).SetCellValue("total:");
rowtemp2.GetCell(3).CellStyle = styledata;
rowtemp2.CreateCell(4).SetCellValue(SumCheck);
rowtemp2.GetCell(4).CellStyle = styledata;
rowtemp2.CreateCell(5).SetCellValue(okCheck);
rowtemp2.GetCell(5).CellStyle = styledata;
rowtemp2.CreateCell(6).SetCellValue(NgCheck);
rowtemp2.GetCell(6).CellStyle = styledata;
//写入流
System.IO.MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
/// <summary>
/// 导入excel文件
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private static string ImportExcel(string filePath)
{
string result = "";
NPOI.SS.UserModel.ISheet sheet = null;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
sheet = hssfworkbook.GetSheetAt(0);
}
}
catch (Exception)
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
NPOI.XSSF.UserModel.XSSFWorkbook hssfworkbook2 = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
sheet = hssfworkbook2.GetSheetAt(0);
}
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
NPOI.SS.UserModel.IRow row = (NPOI.SS.UserModel.IRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
if (row.LastCellNum != -1)
{
if ((dr[0] == null | dr[0].ToString() == "") && (dr[1] == null | dr[1].ToString() == "") && (dr[2] == null | dr[2].ToString() == ""))
{
}
else
{
dt.Rows.Add(dr);
}
}
}
dt = Distinct(dt, new string[] { "A", "B", "C" });
DataView myDataView = new DataView(dt);
string[] strComuns = { "C" };
if (myDataView.ToTable(true, strComuns).Rows.Count < dt.Rows.Count)
{
result = "用户表存在相同的账号数据";
return(result);
}
DbHelper dp = new DbHelper();
foreach (DataRow dr in dt.Rows)
{
string strsql = "insert into hs_user1 values ('" + dr["B"] + "','" + dr["C"] + "')";
dp.RunTxt(strsql);
}
result = "导入成功";
return(result);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
}
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont 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);
}
//headerRow.Dispose();
rowIndex += 1;
}
#endregion
}
#endregion
#region 填充内容
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 13;
hssfSheet.SetColumnWidth(0, 25 * 256);
hssfSheet.SetColumnWidth(3, 20 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 20 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue("计划外领料单");
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
// 标题样式
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#endregion
#region 表数据
// 表数据
for (int k = 0; k < 2; k++)
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i += 2)
{
row.CreateCell(i).SetCellValue(dr[i / 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle;
row.CreateCell(5).SetCellValue("");
row.GetCell(5).CellStyle = cellStyle;
row.CreateCell(7).SetCellValue("");
row.GetCell(7).CellStyle = cellStyle;
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7));
}
// 表数据
for (int k = 2; k < dt.Rows.Count; k++)
{
if (k == dt.Rows.Count - 1)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[i].ToString());
rowlast.GetCell(i).CellStyle = cellStyle;
}
//hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7));
}
else
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < 4; i += 2)
{
row.CreateCell(i).SetCellValue(dr[i / 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 4; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dr[i - 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle;
#region 合并单元格
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
#endregion
}
}
//NPOI.SS.UserModel.IRow tagRow5 = hssfSheet.CreateRow(dt.Rows.Count + 5);
//tagRow5.Height = 20 * 20;
//ICell cell12 = tagRow5.CreateCell(0);
////设置单元格内容报告
//cell12.SetCellValue("Rev: 1.0");
//hssfSheet.AddMergedRegion(new CellRangeAddress(dt.Rows.Count + 5, dt.Rows.Count + 5, 0, 7));
//cell12.CellStyle = TelNoStyle;
#endregion
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream xlsx
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
public static MemoryStream ExportXlsx(IEnumerable <DataTable> dataTables, string strHeaderText, string passaord = null)
{
XSSFWorkbook workbook = new XSSFWorkbook();
int i = 0;
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 右击文件 属性信息
{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "NPOI";
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "文件作者信息"; //填加xlsx文件作者信息
// si.ApplicationName = "创建程序信息"; //填加xlsx文件创建程序信息
// si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
// si.Comments = "作者信息"; //填加xls文件作者信息
// si.Title = "标题信息"; //填加xls文件标题信息
// si.Subject = "主题信息";//填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.GetProperties().CustomProperties.AddProperty("Company", "NPOI");
// if (!workbook.GetProperties().CustomProperties.Contains("Company"))
// workbook.GetProperties().CustomProperties.AddProperty("Company", dsi.Company);
}
#endregion
foreach (DataTable dt in dataTables)
{
string sheetName = string.IsNullOrEmpty(dt.TableName)
? "Sheet " + (++i).ToString()
: dt.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
int rowIndex = 0;
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int k = 0; k < dt.Rows.Count; k++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[k][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
rowIndex += 1;
}
#endregion
//#region 表头
//for (int j = 0; j < dt.Columns.Count; j++)
//{
// string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
// ? "Column " + j.ToString()
// : dt.Columns[j].ColumnName;
// headerRow.CreateCell(j).SetCellValue(columnName);
//}
//#endregion
#region 内容
for (int a = 0; a < dt.Rows.Count; a++)
{
DataRow dr = dt.Rows[a];
IRow row = sheet.CreateRow(a + rowIndex);
for (int b = 0; b < dt.Columns.Count; b++)
{
row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty);
DataColumn dc = dt.Columns[b];
NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal);
string drValue = dr[b].ToString();
switch (dc.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
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
// ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// 读取excel到datatable中
/// </summary>
/// <param name="excelPath">excel地址</param>
/// <param name="sheetIndex">sheet索引</param>
/// <returns>成功返回datatable，失败返回null</returns>
public static DataTable ImportExcel(string excelPath, int sheetIndex)
{
List <string> lstRows = new List <string>();
IWorkbook workbook = null; //全局workbook
ISheet sheet; //sheet
DataTable table = null;
bool isFirst = true;
try
{
FileInfo fileInfo = new FileInfo(excelPath);//判断文件是否存在
if (fileInfo.Exists)
{
FileStream fileStream = fileInfo.OpenRead();//打开文件，得到文件流
switch (fileInfo.Extension)
{
//xls是03，用HSSFWorkbook打开，.xlsx是07或者10用XSSFWorkbook打开
case ".xls": workbook = new HSSFWorkbook(fileStream); break;
case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;
default: break;
}
fileStream.Close();//关闭文件流
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(sheetIndex); //读取到指定的sheet
table = new DataTable(); //初始化一个table
IRow headerRow = sheet.GetRow(0); //获取第一行，一般为表头
int cellCount = headerRow.Cells.Count; //得到列数
if (cellCount != CommonUtils.heads.Length)
{
//Program.logger.LogInfo(string.Format("导入的已分配额度表格式不一致,应为“{0}”{1}列", string.Join(",", CommonUtils.heads), CommonUtils.heads.Length));
return(table);
}
else
{
for (int i = 0; i < CommonUtils.heads.Length; i++)
{
DataColumn dc = new DataColumn(CommonUtils.heads[i]);
table.Columns.Add(dc);
}
}
try
{
int code = int.Parse(headerRow.GetCell(1).ToString());
}
catch (Exception)
{
isFirst = false;
}
int startIdx = sheet.FirstRowNum;
if (isFirst == false)
{
startIdx = sheet.FirstRowNum + 1;
}
//遍历读取cell
for (int i = startIdx; i <= sheet.LastRowNum; i++)
{
StringBuilder sb = new StringBuilder();
NPOI.SS.UserModel.IRow row = sheet.GetRow(i); //得到一行
DataRow dataRow = table.NewRow(); //新建一个行
bool flag = true;
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j); //得到cell
if (cell == null) //如果cell为null，则赋值为空
{
dataRow[j] = "";
}
else
{
if (j == 1)//股票列表
{
if (string.IsNullOrEmpty(row.GetCell(j).ToString().Trim()))
{
flag = false; break;
}
dataRow[j] = row.GetCell(j).ToString().Trim().PadLeft(6, '0');
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim();//否则赋值
}
}
sb.Append(dataRow[j]);
}
if (flag)
{
if (lstRows.Contains(sb.ToString()))
{
continue;
}
lstRows.Add(sb.ToString());
table.Rows.Add(dataRow);//把行 加入到table中
}
}
}
return(table);
}
catch (Exception e)
{
//Program.logger.LogInfo("读取excel文件异常,{0}", e.Message);
return(table);
}
finally
{
//释放资源
if (table != null)
{
table.Dispose();
}
workbook = null;
sheet = null;
}
}