1 #region 从Excel导入
2 /// <summary>
3 /// 读取excel ,默认第一行为标头
4 /// </summary>
5 /// <param name="strFileName">excel文档路径</param>
6 /// <returns></returns>
7 public static DataTable ExcelImport(string strFileName)
8 {
9 DataTable dt = new DataTable();
10
11 ISheet sheet = null;
12 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
13 {
14 if (strFileName.IndexOf(".xlsx") == -1)//2003
15 {
16 HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
17 sheet = hssfworkbook.GetSheetAt(0);
18 }
19 else//2007
20 {
21 XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
22 sheet = xssfworkbook.GetSheetAt(0);
23 }
24 }
25
26 System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
27
28 IRow headerRow = sheet.GetRow(0);
29 int cellCount = headerRow.LastCellNum;
30
31 for (int j = 0; j < cellCount; j++)
32 {
33 ICell cell = headerRow.GetCell(j);
34 dt.Columns.Add(cell.ToString());
35 }
36
37 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
38 {
39 IRow row = sheet.GetRow(i);
40 DataRow dataRow = dt.NewRow();
41
42 for (int j = row.FirstCellNum; j < cellCount; j++)
43 {
44 if (row.GetCell(j) != null)
45 dataRow[j] = row.GetCell(j).ToString();
46 }
47
48 dt.Rows.Add(dataRow);
49 }
50 return dt;
51 }
52 #endregion
#region RGB颜色转NPOI颜色
private static short GetXLColour(HSSFWorkbook workbook, Color SystemColour)
{
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
if (XlColour == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
s = XlColour.Indexed;
}
}
else
s = XlColour.Indexed;
return s;
}
#endregion
#region 设置列的对齐方式
/// <summary>
/// 设置对齐方式
/// </summary>
/// <param name="style"></param>
/// <returns></returns>
private static HorizontalAlignment getAlignment(string style)
{
switch (style)
{
case "center":
return HorizontalAlignment.Center;
case "left":
return HorizontalAlignment.Left;
case "right":
return HorizontalAlignment.Right;
case "fill":
return HorizontalAlignment.Fill;
case "justify":
return HorizontalAlignment.Justify;
case "centerselection":
return HorizontalAlignment.CenterSelection;
case "distributed":
return HorizontalAlignment.Distributed;
}
return NPOI.SS.UserModel.HorizontalAlignment.General;
}
#endregion
