public object Parse(Stream s)
{
XlsDocument doc = new XlsDocument(s);
org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets[0];
List <org.in2bits.MyXls.Row> list = new List <org.in2bits.MyXls.Row>();
if (sheet != null)
{
foreach (var row in sheet.Rows)
{
list.Add(row);
}
}
return(list);
}
/// <summary>
/// 构造函数
/// 初始化_XlsDoc对象
/// </summary>
public ExportExcel(MyDocument doc,string[] needColumns)
{
MyDoc = doc;
XlsDoc = new XlsDocument();
string sheetName = string.Format("{0}", MyDoc.DocName);
sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName;
CurSheet = XlsDoc.Workbook.Worksheets.Add(sheetName);
if (needColumns == null || needColumns.Length == 0)
{
Content = ContentFactory.CreateInstance(MyDoc.Content);
}
else
{
Content = ContentFactory.CreateInstance(MyDoc.Content, needColumns);
}
}
/// <summary>
/// DataTable导出数据到Excel
/// </summary>
public static void DataTableToExcel(System.Data.DataTable dt, string[] sHeader)
{
XlsDocument xls = new XlsDocument();
xls.FileName = "List" + DateTime.Now.ToString("yyyyMMddhhmmss");
org.in2bits.MyXls.Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");//状态栏标题名称
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性
cinfo.Collapsed = true;
cinfo.ColumnIndexStart = 0; //列开始
cinfo.ColumnIndexEnd = 6; //列结束
cinfo.Collapsed = true;
cinfo.Width = 15 * 256; //列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
Cells cells = sheet.Cells;//Cells实例是sheet页中单元格（cell）集合
//单元格1-base
Cell[] cellHeader = new Cell[sHeader.Length];
#region 对Excel进行赋值
for (int j = 0; j < dt.Rows.Count; j++) //行数
{
for (int i = 0; i < sHeader.Length; i++) //列数
{
cellHeader[i] = cells.Add(1, i + 1, sHeader[i]);
cellHeader[i].Font.Height = 20 * 10;
cellHeader[i].BottomLineColor = Colors.Black;
cellHeader[i].BottomLineStyle = 2;
cellHeader[i].Pattern = 1;
cellHeader[i].PatternBackgroundColor = Colors.Black;
cellHeader[i].PatternColor = Colors.White; // "#ECE9D8";
if (j + 2 < 65536) //excel2003的最大行
{
cellHeader[i] = cells.Add(j + 2, i + 1, dt.Rows[j][i].ToString());
}
}
}
#endregion
xls.Send();
}
/// <summary>
/// excel转DataTable
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="endRow">行</param>
/// <param name="endCol">列</param>
/// <param name="startRow">开始行</param>
/// <param name="startCol">开始列</param>
/// <returns>DataTable</returns>
public System.Data.DataTable toDataTable(Worksheet sheet, int endRow, int endCol, int startRow = 1, int startCol = 1) {
System.Data.DataTable dt = new System.Data.DataTable();
dt.TableName = sheet.Name;
//endRow = endRow + startRow;
//Range excelRange = null;
//int cols = 1;
//for (int col = startCol; col <= endCol; col++) {
// excelRange = sheet.Cells[startRow, col] as Range;
// string val = excelRange.Text.ToString();
// if (string.IsNullOrEmpty(val)) break;
// dt.Columns.Add(val);
// cols++;
//}
//int nulls = 1, rownulls = 1;
//for (int row = startRow + 1; row <= endRow; row++) {
// DataRow dataRow = dt.NewRow();
// nulls = 1;
// for (int col = startCol; col < cols; col++) {
// excelRange = sheet.Cells[row, col] as Range;
// string val = excelRange.Text.ToString();
// if (string.IsNullOrEmpty(val)) nulls++;
// dataRow[col - 1] = val;
// }
// dt.Rows.Add(dataRow);
// if (nulls == cols) rownulls++;
// if (rownulls > 10) break;
//}
//int count = dt.Rows.Count;
//if (count > 10 && rownulls > 10) {
// int start = count - 10;
// for (int i = 0; i < 10; i++) dt.Rows[start].Delete();
//}
//if (excelRange != null) {
// Marshal.ReleaseComObject(excelRange);
// excelRange = null;
//}
return dt;
}
//static public void ExportDataGrid(DataTable dt, string FileType, string FileName) //从DataGrid导出
//{
// DataGrid dg = new DataGrid();
// //DataSet dg = new DataSet();
// dg.DataSource = dt;
// dg.DataBind();
// //定义文档类型、字符编码
// HttpContext.Current.Response.Clear();
// HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
// HttpContext.Current.Response.Charset = "UTF-8";
// HttpContext.Current.Response.ContentEncoding = Encoding.Default;
// HttpContext.Current.Response.ContentType = FileType;
// dg.EnableViewState = false;
// //定义一个输入流
// StringWriter tw = new StringWriter();
// HtmlTextWriter hw = new HtmlTextWriter(tw);
// //目标数据绑定到输入流输出
// dg.RenderControl(hw);
// HttpContext.Current.Response.Write(tw.ToString());
// HttpContext.Current.Response.End();
//}
static public void Output(DataTable dt, string type, string name)
{
org.in2bits.MyXls.XlsDocument doc = new org.in2bits.MyXls.XlsDocument();
doc.FileName = DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();//excel文件名称
//org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets.AddNamed("sheet1");//Excel工作表名称
org.in2bits.MyXls.Worksheet sheet = doc.Workbook.Worksheets.AddNamed("sheet1");
org.in2bits.MyXls.Cells cells = sheet.Cells;
int colnum = dt.Columns.Count;//获取DataTable列数
for (int i = 0; i < colnum; i++)
{
cells.Add(1, (i + 1), dt.Columns[i].Caption.ToString());//导出DataTable列名
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < colnum; j++)
{
cells.Add((i + 2), (j + 1), dt.Rows[i][j].ToString());
}
}
doc.Save(HttpContext.Current.Server.MapPath("file/"));
string strFilePath = HttpContext.Current.Server.MapPath("file/") + doc.FileName;
FileInfo fi = new FileInfo(strFilePath);//excelFile为文件在服务器上的地址
HttpResponse contextResponse = HttpContext.Current.Response;
contextResponse.Clear();
contextResponse.Buffer = true;
contextResponse.Charset = "utf8"; //设置了类型为中文防止乱码的出现
contextResponse.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", name)); //定义输出文件和文件名
contextResponse.AppendHeader("Content-Length", fi.Length.ToString());
contextResponse.ContentEncoding = Encoding.Default;
contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
contextResponse.WriteFile(fi.FullName);
contextResponse.Flush();
contextResponse.End();
}
private ushort _width = 2560; //Set default to 10-character column width
#endregion Fields
#region Constructors
/// <summary>
/// Initializes a new instance of the ColumnInfo class for the given Doc
/// and Worksheet.
/// </summary>
/// <param name="doc">The parent MyXls.Doc object for the new ColumnInfo object.</param>
/// <param name="worksheet">The parent MyXls.Worksheet object for the new ColumnInfo object.</param>
public ColumnInfo(XlsDocument doc, Worksheet worksheet)
{
_doc = doc;
_worksheet = worksheet;
}
/// <summary>
/// Initializes a new instance of the Cells collection class for the given Worksheet.
/// </summary>
/// <param name="worksheet">The parent Worksheet object for this new Cells collection.</param>
public Cells(Worksheet worksheet)
{
// _doc = doc;
_worksheet = worksheet;
}
internal RowBlocks(Worksheet worksheet)
{
_worksheet = worksheet;
}
internal Cell(Worksheet worksheet)
{
_worksheet = worksheet;
}
private static Bytes BOUNDSHEET(Worksheet sheet, int basePosition)
{
Bytes bytes = new Bytes();
Bytes sheetName = XlsDocument.GetUnicodeString(sheet.Name, 8);
bytes.Append(WorksheetVisibility.GetBytes(sheet.Visibility));
bytes.Append(WorksheetType.GetBytes(sheet.SheetType));
bytes.Append(sheetName);
bytes.Prepend(BitConverter.GetBytes((int) basePosition)); //TODO: this should probably be unsigned 32 instead
bytes.Prepend(BitConverter.GetBytes((ushort) bytes.Length));
bytes.Prepend(RID.BOUNDSHEET);
return bytes;
}
/// <summary>
/// excel转DataTable
/// </summary>
/// <param name="sheet">工作表</param>
/// <returns>DataTable</returns>
public System.Data.DataTable toDataTable(Worksheet sheet) {
return toDataTable(sheet, 1000, 50);
}
/// <summary>
/// excel转DataTable
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="endRow">行</param>
/// <returns>DataTable</returns>
public System.Data.DataTable toDataTable(Worksheet sheet, int endRow) {
return toDataTable(sheet, endRow, 50);
}
