无格式Excel NPOI导入code：
DownLoadExcel(filePath)代码展示：
protected void btnImport_Click(object sender, EventArgs e)
{
string filePath = Request.MapPath("/") + "FileUpload/";
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
if (FileUpload1.FileName != null && FileUpload1.FileName != "")
{
string newFilePath = filePath + FileUpload1.FileName;
FileUpload1.SaveAs(newFilePath);
try
{
using (FileStream fs = new FileStream(newFilePath, FileMode.Open))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0);
int rowCount = 1;
int cellCount = 0;
List<List<OracleParameter>> sqlText = new List<List<OracleParameter>>();
string sql = string.Empty;
while (rowCount <= sheet.LastRowNum)
{
IRow row = sheet.GetRow(rowCount);
ArrayList arr = new ArrayList();
while (cellCount < row.Cells.Count)
{
ICell cell = row.GetCell(cellCount);
arr.Add(cell.ToString());
cellCount++;
}
cellCount = 0;
rowCount++;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into EXPORT(");
strSql.Append("STUDENT_NAME,STUDY_NO)");
strSql.Append(" values (");
strSql.Append(":STUDENT_NAME,:STUDY_NO)");
sql = strSql.ToString();
List<OracleParameter> listPara = new List<OracleParameter>();
listPara.Add(new OracleParameter(":STUDENT_NAME", arr[0].ToString()));
listPara.Add(new OracleParameter(":STUDY_NO", arr[1].ToString()));
sqlText.Add(listPara);
}
new DataAccess.SqlRunner().ExecuteSqlNonQuery(sql, sqlText);
BindData(true);
Show("导入成功!");
}
}
catch (Exception ex)
{
Show(ex.Message);
}
}
else
{
Show("请选择要上传的文件!");
}
}
NPOI导出Excel：
导出按钮事件：
protected void btnExport_Click(object sender, EventArgs e)
{
List<OracleParameter> paraList = new List<OracleParameter>();
string sql = GetSqlString(out paraList);
DataTable dt = new DataAccess.SqlRunner().ExecuteSQLFillTable(sql, paraList);
string fileName = "货物信息_" + System.DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
string sheetName = "货物信息";
string filePath = Request.MapPath("/TempFiles/") + fileName;
string[] excelHeader = { "成品", "货号" };
//自然格式导出方法1
CreateExcelPath(dt, excelHeader, filePath, sheetName);
DownLoadExcel(filePath);
}
GetSqlString(out paraList)方法展示：
public string GetSqlString(out List<OracleParameter> paraList)
{
string strWhere = string.Empty;
paraList = new List<OracleParameter>();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT STUDENT_NAME,STUDY_NO,ROWID ");
strSql.Append(" FROM EXPORT t WHERE 1=1 ");
if (!string.IsNullOrEmpty(this.txtName.Text.Trim()))
{
paraList.Add(new OracleParameter(":name", this.txtName.Text.Trim()));
strSql.Append("AND t.student_name=:name ");
}
if (!string.IsNullOrEmpty(this.txtNo.Text.Trim()))
{
paraList.Add(new OracleParameter(":no", this.txtNo.Text.Trim()));
strSql.Append("AND t.study_no=:no ");
}
strSql.Append(" ORDER BY t.study_no");
return strSql.ToString();
}
ExecuteSQLFillTable(sql, paraList)代码展示：
public DataTable ExecuteSQLFillTable(string strSQL, List<OracleParameter> paraList)
{
DataTable dt = new DataTable();
OracleConnection conn = new OracleConnection(strconnect);
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSQL;
if (paraList != null)
{
foreach (OracleParameter temp in paraList)
{
cmd.Parameters.Add(temp);
}
}
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
CreateExcelPath(dt, excelHeader, filePath, sheetName)代码展示：
public void CreateExcelPath(DataTable dt, string[] excelHeader, string filePath, string sheetName)
{
HSSFWorkbook workBook = new HSSFWorkbook();
ISheet sheet = workBook.CreateSheet(sheetName);
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
ICellStyle style = workBook.CreateCellStyle();
//设置单元格字体居中
style.Alignment = HorizontalAlignment.CENTER;
//设置单元格背景色
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
//设置字体格式
IFont font = workBook.CreateFont();
font.FontHeight = 15 * 15;
font.Color = NPOI.HSSF.Util.HSSFColor.BLUE.index;
//把设置写入样式
style.SetFont(font);
//把定义的样式加入新增的单元格
cell.CellStyle = style;
cell.SetCellValue("货物信息");
//合并单元格的方法
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));
//SetCellRangeAddress(sheet, 0, 0, 0, 1);
row.Height = 600;
//添加超链接的方法
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.URL);
link.Address="http://mail.epass.net.cn/";
cell.Hyperlink = link;
row = sheet.CreateRow(1);
//设置ExcelHeader
for (int i = 0; i < excelHeader.Length; i++)
{
row.CreateCell(i).SetCellValue(excelHeader[i].ToString());
//row.Cells[i].CellStyle = 18;
//ICellStyle style = cell.CellStyle;
}
//设置ExcelBody
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count - 1; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
if (File.Exists(filePath))
{
File.Delete(filePath);
}
using (FileStream fs = new FileStream(filePath, FileMode.Create))
{
workBook.Write(fs);
fs.Close();
}
}
DownLoadExcel(filePath)代码展示：
public void DownLoadExcel(string filePath)
{
Stream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
string fileName = Path.GetFileName(filePath);
byte[] buffer = new byte[10000];
int length;
long data = stream.Length;
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(fileName, UTF8Encoding.UTF8));
try
{
while (data > 0)
{
if (Response.IsClientConnected)
{
length = stream.Read(buffer, 0, 10000);
Response.OutputStream.Write(buffer, 0, length);
Response.Flush();
buffer = new byte[10000];
data = data - length;
}
else
{
data = -1;
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (stream != null)
stream.Close();
}
}
各种操作：
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">操作sheet</param>
/// <param name="rowstart">开始行索引</param>
/// <param name="rowend">结束行索引</param>
/// <param name="colstart">开始列索引</param>
/// <param name="colend">结束列索引</param>
public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
public void SaveSheetData(DataTable dt, string filePath)
{
//HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//string hasHead = "true";
//ISheet sheet = hssfworkbook.GetSheetAt(0);
//int rowIndex = 0;
//if (hasHead == "true")
//{
// rowIndex++;
//}
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// IRow dataRow = sheet.CreateRow(i + rowIndex);
// dataRow.CreateCell(0).SetCellValue(dt.Rows[i]["ReferenceNo"].ToString());
// dataRow.CreateCell(1).SetCellValue(dt.Rows[i]["G_Name"].ToString());
// dataRow.CreateCell(2).SetCellValue(dt.Rows[i]["G_Ename"] == null ? "" : dt.Rows[i]["G_Ename"].ToString());
// dataRow.CreateCell(3).SetCellValue(dt.Rows[i]["G_Description"].ToString());
//}
//using (FileStream file = new FileStream(filePath, FileMode.Create))
//{
// hssfworkbook.Write(file);
// file.Close();
//}
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//创建下载后的Excel的右键属性
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
//新怎工作表1，2
hssfworkbook.CreateSheet("Sheet1");
hssfworkbook.CreateSheet("Sheet2");
//hssfworkbook.CreateSheet("Sheet3");
//hssfworkbook.CreateSheet("Sheet4");
//merger start
//HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet("Sheet2");
//IRow row = sheet.CreateRow(0);
//ICell cell = row.CreateCell(0);
//cell.SetCellValue("我是中国人");
//创建字体样式
// ICellStyle style = hssfworkbook.CreateCellStyle();
// style.Alignment = HorizontalAlignment.CENTER;
// //create a font style
// IFont font = hssfworkbook.CreateFont();
// font.FontHeight = 20 * 20;
// font.Color = NPOI.HSSF.Util.HSSFColor.RED.index;
// style.SetFont(font);
// cell.CellStyle = style;
// //merged cells on single row
// //ATTENTION: don't use Region class, which is obsolete
// sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
// //merged cells on mutiple rows
// CellRangeAddress region = new CellRangeAddress(2, 4, 2, 4);
// sheet.AddMergedRegion(region);
//merger end
//htyperLink start
//ICellStyle hlink_style = hssfworkbook.CreateCellStyle();
//IFont hlink_font = hssfworkbook.CreateFont();
//hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
//hlink_font.Color = HSSFColor.BLUE.index;
//hlink_style.SetFont(hlink_font);
//ICell cell;
// ISheet sheet = hssfworkbook.CreateSheet("Hyperlinks");
// cell = sheet.CreateRow(0).CreateCell(0);
// cell.SetCellValue("URL Link");
//创建超链接
// HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.URL);
// link.Address = ("http://poi.apache.org/");
// cell.Hyperlink = (link);
// cell.CellStyle = (hlink_style);
// //link to a file in the current directory
// cell = sheet.CreateRow(1).CreateCell(0);
// cell.SetCellValue("File Link");
// link = new HSSFHyperlink(HyperlinkType.FILE);
// link.Address = ("link1.xls");
// cell.Hyperlink = (link);
// cell.CellStyle = (hlink_style);
// //e-mail link
// cell = sheet.CreateRow(2).CreateCell(0);
// cell.SetCellValue("Email Link");
// link = new HSSFHyperlink(HyperlinkType.EMAIL);
// //note, if subject contains white spaces, make sure they are url-encoded
// link.Address = ("mailto:poi@apache.org?subject=Hyperlinks");
// cell.Hyperlink = (link);
// cell.CellStyle = (hlink_style);
//HyperLink end
// head start
//ISheet s1 = hssfworkbook.CreateSheet("baoge test");
//s1.CreateRow(0).CreateCell(1).SetCellValue(123);
////set header text
//s1.Header.Left = HSSFHeader.Page; //Page is a static property of HSSFHeader and HSSFFooter
//s1.Header.Center = "This is a test sheet";
////set footer text
//s1.Footer.Left = "Copyright NPOI Team";
//s1.Footer.Right = "created by Tony Qu（瞿杰）";
// head end
ISheet sheet3 = hssfworkbook.CreateSheet("third sheet");
ISheet sheet4 = hssfworkbook.CreateSheet("fourth sheet");
ISheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(@"C:\Users\Administrator\Desktop\Menu\Menu\images\demo.jpg", hssfworkbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
//set enclosed border for the merged region
// ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.DOTTED, NPOI.HSSF.Util.HSSFColor.RED.index);
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = true;
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = true;
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(filePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}总结很简略，有待继续完成，向大神们学习!!!
扫一扫
917
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
