private void toExcel(System.Data.DataTable dt, int i = 1) {
Worksheet sheet = doc.Workbook.Worksheets.AddNamed(dt.TableName.IfNullOrEmpty("Sheet" + i.ToString()).Trim("$"));
cells = sheet.Cells;
int rows = dt.Rows.Count, cols = dt.Columns.Count;
for (int k = 1; k <= cols; k++) {
cells.AddValueCell(1, k, dt.Columns[k - 1].Caption).Font.Bold = true;
}
for (int j = 2; j <= rows + 1 ; j++) {
for (int k = 1; k <= cols; k++) {
cells.AddValueCell(j, k, dt.Rows[j - 2][k - 1]);
}
}
}
internal Worksheet(XlsDocument doc)
{
_doc = doc;
_visibility = WorksheetVisibilities.Default;
_sheettype = WorksheetTypes.Default;
_streamByteLength = 0;
_dbCellOffsets = new int[0];
_cells = new Cells(this);
_rows = new Rows();
_rowBlocks = new RowBlocks(this);
_cachedBlockRow = CachedBlockRow.Empty;
_columnInfos = new List<ColumnInfo>();
}
//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();
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss"));
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Bold = true;
titleXF1.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "NO#", titleXF1);
cells.Add(1, 2, "Claim Type", titleXF1);
cells.Add(1, 3, "Amount", titleXF1);
cells.Add(1, 4, "Owner", titleXF1);
cells.Add(1, 5, "Process", titleXF1);
cells.Add(1, 6, "Current Approver", titleXF1);
cells.Add(1, 7, "Submit Date", titleXF1);
cells.Add(1, 8, "Remark", titleXF1);
//添加数据
string json = GridData.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml.InnerXml);
for (int i = 0; i < doc.SelectNodes("records").Item(0).SelectNodes("record").Count; i++)
{
if (!string.IsNullOrEmpty(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml))
{
cells.Add(2 + i, 3, Convert.ToDouble(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml), titleXF);
}
else
{
cells.Add(2 + i, 3, "", titleXF);
}
cells.Add(2 + i, 1, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("No").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 2, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Type1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 4, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Person").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 5, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Status1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 6, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Approver").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 7, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("CreadedDate").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 8, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Remark").Item(0).InnerXml, titleXF);
}
xls.Send();
}
/// <summary>
/// 打开指定的工作薄
/// </summary>
/// <param name="workSheets">第N个工作薄</param>
public void OpenWorkSheets(int workSheets) {
if (workSheets <= 0) return; //工作薄编号必须从1开始
cells = doc.Workbook.Worksheets[workSheets].Cells;
}
